?
Solved

Is there a way to track which reports are ran the most?

Posted on 2012-09-10
10
Medium Priority
?
410 Views
Last Modified: 2012-09-11
Good Day all,

Question in regards to Crystal 8.5 compiled reports.  I have 505 reports that I am trying to distinguish which out of those reports are the most utilized reports.  I have done a massive such on here and on other locations but no clear answer was derived.  I went to the server where the reports are stored.  

Application Server that runs a third party software that is able to direct the users to a repository of these 505 reports.  I am unaware of how I can monitor on any terminal server how many times a report has run.  I was thinking about adding something in the report that would maybe insert and update the user that ran and how many times it has been run.

Any suggestions pls.
0
Comment
Question by:Sqlspider
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38383736
At least one of the 3rd-party Crystal User Function Libraries listed at http://kenhamady.com/bookmarks.html allows you to add a formula in Crystal Reports that writes to a text file the report name, user, machine, and start time.

At least one of the 3rd-party Crystal Report viewers listed at http://kenhamady.com/bookmarks.html allows you to log report use at even greater detail to either a text or directly to a database table via ODBC.
0
 

Author Comment

by:Sqlspider
ID: 38384310
IdoMillet, this has put me onto the correct path but now I am lost as to how to use the info you have give me.  I went to the links and most of what I saw, I had to pay for.  I did do  search on a keyword CurrentCEUserName.  I found that this will give me what I needed but I am having troubles getting the code in the Crystal report.  I am unaware of where I need to go from here.

Thanks

sqlspider
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38385428
I don't believe that will work in CR 8.5.
If it does you may have to have Crystal Enterprise installed and be logged into it when you run the report.

I don't believe a compiled report will be able to use that.

Ido - does that work with CR8.5?

mlmcc
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Ido Millet
ID: 38386214
I don't know what technique he is referring to. CEUserName doesn't give you a way to write it out. The UFL I have in mind already gives you a function to get the Windows User ID as well as to write to a text file or to a database.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38387125
Agree your UFL will work.  The CEUserName is a special field but he would have to write the code to save it and I believe it only works if you are logged into Crystal Enterprise.

mlmcc
0
 

Author Comment

by:Sqlspider
ID: 38387528
Ok,
IdoMillet how do I utilize the UFL that you are referring to please.  Thank you.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38387582
You crate a formula in the report.  The act of viewing the report causes the formula to evaluate.  That causes a function within the formula to write the information you wish to log to a text file or to a database.
0
 
LVL 18

Expert Comment

by:vasto
ID: 38387740
You might need more than a UFL to achieve your goal. UFL will return the current user name, but you will need to preserve this information somewhere (in an external log file or database). You can probably write your own UFL, which will do exactly what you want, but this will take some development time and it will take 3 weeks full time work to update all reports (505 report X 15 min per report = 126 hours)

You can find a tutorial how to write UFL online. Check these links:
http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=103
http://www.crystalbook.com/crtcr/extras/xi/Creating_UFLs_In_VB.pdf

If you want to use an existing UFL which will return the current user there are some free UFLs. For example this one: http://www.viksoe.dk/code/u2lwin32.htm
It is coming also with the source code so you can extend it and add functions if you want.

However, as I mentioned  you might have the tools , but you will need to implement them , which will take 3 weeks.
0
 
LVL 18

Accepted Solution

by:
vasto earned 2000 total points
ID: 38387879
BTW the UFL from the link :http://www.viksoe.dk/code/u2lwin32.htm
provides functions LoginName, ComputerName and FileAppendLine ,which will be enough to collect the information you need and save it in a file. You might start with some reports , which are most interesting for you.


Another possible solution is to use an existing system. I am actually surprised that your third party system is not able to handle this.  The provided link http://kenhamady.com/bookmarks.html  ( check also this PDF: http://kenhamady.com/viewerfeaturematrix.pdf)  contains at least one network enabled system which will allow you to run and schedule reports and analyze the statistics. All you need to do is to add the reports to the system. The system can log user and workstation activity which means it will collect information about who ran the report, at what time, how much time it took for the report to finish, what were the parameters, which computer was used to run the report ... In addition you can add custom C# code to your reports because the system provides a developer SDK . What you can do with the collected information?

Some of the possible uses are:
        *** You can calculate the "weight" of the reports. You might have 1 report which takes 30 min to run but is used 20 times per year and another report which takes 30 second and used 100,000 times per year - the system will allow to plan your optimizations and to find which report will save you the most time. For the given example if you improve the speed with 10% then you will save 1 hour per year in the first case and 84 hours in the second one. Obviously the second report will have higher priority when you decide which one to optimize first.
       *** You can compare the performance of the reports for different periods. For example compare the average time it takes to run a specific report before and after a change.
       *** You can find the users who are using a specific report and run with them changes and bug fixes
       *** In case there is a bug in a report you can go back and check who used the report during the period when the bug was active and what were the parameter’s values for each run . Then you can rerun the report and contact the users to resolve the issue.

These are just part of the possible uses. You can also use the statistics to predict the use of the reports based on the previous usage patterns and to find points when multiple heavy reports might be started simultaneously and interfere the system performance. You may also detect if a report was not started as usual (for example a monthly report was not started on time) and to investigate the reasons based on Islands and Gaps method even without knowing that the report is monthly report.

The system is free for nonprofits so if you have 501C3 registration you can use it for free. Otherwise you can use it during the 30 days trial period without limitations. The trial period might be extended up to 6 months, which I believe will be enough for you to finish your project without buying it.  I am linked to the company, but if you decide this might work for you  it will be a good idea to contact the company directly before to start planning.
0
 

Author Closing Comment

by:Sqlspider
ID: 38388794
Thanks much on the breakdown.  Very useful information.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question