Link to home
Start Free TrialLog in
Avatar of Jeff Edmunds
Jeff EdmundsFlag for United States of America

asked on

Identify drill-through reports and subreports using TSQL

Here's the situation:
I'm reviewing the relatively large number of SSRS reports our company has created over the years with the intention of eventually archiving or deleting reports which are no longer being used. I currently have a stored procedure in place which is combining information from the ExecutionLog3 view with information from the Catalog table in the ReportServer database. As new records are selected, they are inserted into a log table I created. As reports which have already been logged are executed, the existing records in the log table are updated with the most recent execution date/time. Records which haven't been executed in the time since I've started logging the data have a default date of 1/1/1900. This way, I have a table which contains 1 record for each report in the Catalog, updated regularly to show the most recent date/time that report was executed. This all works well...

However, when the time comes to actually start archiving or deleting reports based on this information, I'm concerned that I might remove reports which aren't showing as having been recently executed, only to learn they were being used as drill-though or subreports and that I was only grabbing execution dates/times from the execution log for main reports.

This is all running on SQL Server 2008 R2.

Here's the question:
How can I create a SQL script that will identify subreports and drill-through reports being used by other main reports?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Good for you to create your own homegrown SSRS logging.  I've pulled this off at multiple employers with SSRS pre-2012.

>How can I create a SQL script that will identify subreports and drill-through reports being used by other main reports?
Afaik you'd have to also connect the subreport/drill-through reports' data sets to your logging calls as well, otherwise there's no way of knowing.   When I built it I coded in the SP that serves as data sources for reports to INSERT into my log table, and that can also be done for subreport/drill-through's.
Avatar of Jeff Edmunds

ASKER

I'm intrigued but not sure how to go about doing what you're suggesting. Could you give an example?
>>How can I create a SQL script that will identify subreports and drill-through reports being used by other main reports?

I dont think we have any T-SQL which will give you this data.

but you can get list of SSRS reports using catalog table like below.

SELECT
     *
FROM [dbo].[Catalog]
WHERE [Type] = 2

Open in new window


You can also get list of scheduled reports but this is not.
Thanks for your reply, Pawan. I've already written a stored procedure to log the execution of reports in the database. It is already pulling from the Catalog table, as well as the ExecutionLog3 view, as stated in my original post. I'm using a MERGE statement to update existing records as they are being executed and insert new records as reports are added to the catalog. I have a separate stored procedure which deletes records from the log when a report no longer exists in the catalog, based on the report path (meaning someone has deleted or moved the report).

The specific problem is identifying the reports which are used as sub-reports or drill-through reports by other "main" reports. I don't know how the information is stored which links the main reports to any child reports it calls or how it is recorded when a sub-report or drill-through report is executed.

Jim's idea sounds valid but I'm hoping he might be able to provide a code example to illustrate exactly what he's suggesting.
>>The specific problem is identifying the reports which are used as sub-reports or drill-through reports by other "main" reports. I don't know how the information is stored which links the main reports to any child reports it calls or how it is recorded when a sub-report or drill-through report is executed.

Well in this case you need to have a column in the database say reporttype which can have values like SubReport,MainReport,DrillDown,Drillthough etc... then only we can achieve this other it is not possible.
Adding such a column in the log table I created would be certainly be easy. However, I'd need to know how to populate that column for it to be of value. In order to do that, I need to know how Microsoft differentiates between the execution of a sub-report or drill-through report and a main (or standalone) report. This brings us back to the original question of how to identify those sub-reports or drill-through reports.
>>This brings us back to the original question of how to identify those sub-reports or drill-through reports.
We have to execute the report and then check ...and prepare excel for each report and then insert the data in the table.
I'm afraid that isn't a viable option. There are currently approximately 1400 reports in the catalog. Executing each one individually would not only be time consuming but it defeats the purpose of the log, which is to see which of these reports are actually in use. Ultimately I'd like to know how MS connects the reports in the database, so I can directly query the component tables or views and build the information.
That's a good link - thanks. However, I believe that's actually one of the links I used when originally building the logging process, using the ExecutionLog3 data. I agree, those columns likely don't help. It must store that information elsewhere... it's that location and how the data is related which I'm trying to determine.
glad to help as always :)
Still open to suggestions for this. As I re-read Jim's post it sounds like he's actually adding something to the stored procedure being used as the data sources for each report when the reports are created - those stored procedures then write to the log. I'm working on this from the opposite direction... I have a system which contains over 1000 reports which have already been built and I'm trying to identify those few which actually make use of sub-reports or drill-through reports. I'm not writing to the log from the data sources, I'm querying the database to extract the execution data. Microsoft must store the information that describes the report relationships somewhere.
ASKER CERTIFIED SOLUTION
Avatar of Jeff Edmunds
Jeff Edmunds
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Answer found elsewhere. The previously proposed suggestions, although appreciated, were either not applicable to the current situation or only provided information I already had.