XIR2 - Retrieve Report Statistics Macro

system · December 30, 2008, 5:33pm

Author: Jim McGregor, james.mcgregor@zionsbancorp.com

Author notes:

Platform: Microsoft Excel 2003 / XIR2 SP4

Version: 1.0 (See version 1.1 here)

Code:

Rather than post the code verbatim, here is an overview of the logic:

1. Connect to the CMS (Thanks Julien!)
2. First Loop - Loop through the three types of reports: Crystal, Webi, Deski
2. Second Loop - Extract the list of reports.
3. Third Loop - Count up the number of instances and calculate the avg runtime.

Retrieve Report Statistics Macro.zip (26.0 KB)

alpha1145 (BOB member since 2006-01-04)

system · December 30, 2008, 7:47pm

Added to BOB’s Downloads. Updated alpha listing. Thanks for sharing, Jim.

MichaelWelter (BOB member since 2002-08-08)

system · January 13, 2009, 1:44pm

Suppose I don’t have BO client or server installed on my machine.

I have a link of infoview, so will this work?

Note: I cannot test as I don’t have access to CMS. But just wondering is it possible…?

aniketp (BOB member since 2007-10-05)

system · January 14, 2009, 3:32pm

This macro requires that the BO client software be installed on your PC. VBA macros rely on the Business Objects .dll library files that are installed when the client software is installed.

alpha1145 (BOB member since 2006-01-04)

system · January 15, 2009, 7:21am

Thank you alpha1145.

That means If I have infoview link and CMC login, even then I won’t be able to use this tool.

And I need to have BO installed on my machine. Thanks for response.

aniketp (BOB member since 2007-10-05)

system · February 3, 2009, 9:45am

THANK YOU ALPHA 1145!

That’s an awesome tool you created!

phonque (BOB member since 2005-10-17)

system · February 26, 2009, 10:37pm

Thank you very much for this tool Alpha.

BNReddy (BOB member since 2006-06-27)

system · March 11, 2009, 8:51pm

Hi,

This is really a nice app. Thanks a lot.

Is there any limit set on the number of reports? When ever i try to run this report it only give me 2000 records (Report 1000 and WEBI 1000). If so can you please let me know how to change it. I have definitely more than 2000 in total.

Thanks

writetoprav (BOB member since 2007-04-09)

system · March 12, 2009, 2:11am

Thanks for the heads up on the “bug”. Most likely this is because queries run against the repository have natural limits on them (1000 records seems to be the most common limit). I’ll most likely have to change the select statements in the code to read something like:

SELECT TOP 10000 <something, something> from <tablename> where <conditions>

By adding the “TOP 10000” to queries against the BO SDK, it overrides the limits. I’ll try and get this fixed as soon as I can.

alpha1145 (BOB member since 2006-01-04)

system · March 12, 2009, 6:42pm

Hello,

Thanks a lot for quick response. Adding “TOP 10000” worked like a charm. Changed it myself and tested it as well.

Thanks

writetoprav (BOB member since 2007-04-09)

system · March 20, 2009, 7:07pm

Hi. This is a great tool. I have a question why I got ‘0’ values in the Number of Instances, Number of Recurring Instances, and Avg Runtime as ‘-’.

Do I need to enabling anything on my end to capture these statistics?

Thanks.

bstn82 (BOB member since 2008-06-27)

system · March 24, 2009, 3:59pm

This is an update to the Retrieve Report Statistics Macro. I added the “TOP 10000” to the select statements in the code to prevent the records from being limited to a 1000 records. I’ll update the original post after the file has been updated.
Retrieve Report Statistics Macro.zip (16.0 KB)

alpha1145 (BOB member since 2006-01-04)

system · March 25, 2009, 1:33pm

Make sure you add the “TOP 10000” to the select statements that are pulling the count of instances as well, otherwise it will cap that to 1000 records as well. I have a corrected version of the macro and I’m waiting for it to clear “BOB’s Uploads”.

alpha1145 (BOB member since 2006-01-04)

system · March 25, 2009, 1:34pm

Does the report in question have any scheduled instances? If not, then I would expect you to get a “0” value for the number of instances, number of recurring instances and Avg Runtime.

alpha1145 (BOB member since 2006-01-04)

system · March 25, 2009, 1:49pm

Just as an FYI, it takes me 67 minutes to run this macro and collect statistics on 8001 reports. So if you have a lot of reports, be patient.

alpha1145 (BOB member since 2006-01-04)

system · April 7, 2009, 11:42am

I am getting compile error: Can’t find project or library.

Vills (BOB member since 2007-10-24)

system · April 10, 2009, 7:47pm

Hi,

Is there a way to modify the code to have this tool work for BO XIR2 SP2.

Right now it error out as

CrystalEnterprise12.SessionMgr.1 - -2147024891: Access Denied. You cannot log on to an older version of the CMS. 1000440

Thanks,
Goutham.

gauthamvarma (BOB member since 2007-06-10)

system · April 13, 2009, 6:24am

Hi,

I want only WEBI list of reports from the public foler.
Is there any customised macro availble to get WEBI list of reports?

Many thanks in advance.

radhu (BOB member since 2009-02-12)

system · April 13, 2009, 7:04am

Hi all,

Can anyone help me, how to extract list of reports which is availble in WEBI public folders only?

I ran the script but its giving me lots of reports that couldn’t be understood.

I want to have list of reports in WEBI Public folder - any chance to get?

radhu (BOB member since 2009-02-12)

system · April 17, 2009, 9:47pm

Make sure that you are running it on a machine that has the BO client tools installed (Designer, Deski, etc). That will install the proper .dll libraries that the macro needs to run.

alpha1145 (BOB member since 2006-01-04)