BusinessObjects Board

XIR2 - Retrieve Report Statistics Macro

Author: Jim McGregor,

Author notes:

Platform: Microsoft Excel 2003 / XIR2 SP4

Version: 1.0 (See version 1.1 here)


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 (26.0 KB)

alpha1145 :us: (BOB member since 2006-01-04)

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

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

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 :uk: (BOB member since 2007-10-05)

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 :us: (BOB member since 2006-01-04)

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 :uk: (BOB member since 2007-10-05)


That’s an awesome tool you created!

phonque :netherlands: (BOB member since 2005-10-17)

Thank you very much for this tool Alpha.

BNReddy :us: (BOB member since 2006-06-27)


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.


writetoprav :us: (BOB member since 2007-04-09)

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 :us: (BOB member since 2006-01-04)


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

Thanks :+1:

writetoprav :us: (BOB member since 2007-04-09)

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?


bstn82 (BOB member since 2008-06-27)

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 (16.0 KB)

alpha1145 :us: (BOB member since 2006-01-04)

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 :us: (BOB member since 2006-01-04)

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 :us: (BOB member since 2006-01-04)

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 :us: (BOB member since 2006-01-04)

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


Vills :india: (BOB member since 2007-10-24)


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


gauthamvarma (BOB member since 2007-06-10)


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)

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)

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 :us: (BOB member since 2006-01-04)