How to find list of top N reports run(or used by users) on Business objects Crystal Report SAP

[former_member1002499]

Hello,

I am trying to find the list of to N reports which are frequently accessed and used by the users in SAP Business Objects (BO) Crystal Report BI platform.

I did try utilizing some information from the below link but no luck yet. Please advise-

[https://blogs.sap.com/2012/10/11/businessobjects-query-builder-queries/]

Thanks!

[DellSC]

This information is in the Audit database, not in the CMS database, so you can't get to it through QueryBuilder. If you have auditing turned on, you can run a query like this (use it as a command in Crystal, if you want) to get the information. This query is written in SQL Server syntax, so you may have to tweak it if your auditing is in a different database, but the tables will all be the same.

Select
  [Object_Name],
  Object_Folder_Path,
  ots.Object_Type_Name,
  count(Distinct Session_ID) as event_count
from ADS_EVENT e
  inner join ADS_OBJECT_TYPE_STR ots
    on e.Object_Type_ID = ots.Object_Type_ID
	  and ots.Language = 'EN'
	  and ots.Object_Type_Name != 'LCMJob'
where e.Event_Type_ID in (1002, 1003, 1011, 1013)
group by Object_Folder_Path, [Object_Name], ots.Object_Type_Name
order by [Object_Name]

-Dell

[nkovach]

In our audit database the object_folder_path field is empty. I know that data is in the CMS database but how do I combine that with the audit database to get a full list of all folders / reports / # of runs?