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?
LVL 2
Jeff EdmundsApplication Developer/SQL DBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
I'm intrigued but not sure how to go about doing what you're suggesting. Could you give an example?
Pawan KumarDatabase ExpertCommented:
>>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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Pawan KumarDatabase ExpertCommented:
>>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.
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Pawan KumarDatabase ExpertCommented:
>>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.
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Pawan KumarDatabase ExpertCommented:
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Pawan KumarDatabase ExpertCommented:
glad to help as always :)
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
I believe I've found an answer to this elsewhere.

It appears that the ExecutionLog3 table only records the execution of the primary report in the case of sub-reports. To determine the sub-reports associated with a main report you need to step through the xml in the content field of the catalog table, which represents the report definition itself. To do this, you need to check it against the proper XML schema.
For example:
From there you can cross apply the Subreport node and get a list.

The ExecutionLog3 table does record drill-though report execution with value in the ItemAction field labeled "DrillThrough". Unfortunately, that only tells me which reports are drill-through reports when they're actually used. I tried looking in the report definition XML for a drillthrough report note (corresponding to the subreport node) and didn't find one. Edit: I did just find this... guessing it's case-sensitive. There is in fact a "Drillthrough" element in there. This means you can pull a full list of referenced reports from the report definitions stored in the catalog, they're just not all listed in the ExecutionLog3 view.

My ultimate goal is to identify all reports which are no longer in use (main reports, sub-reports, and drill-through reports), so I can clean them out. I don't want to remove a referenced report, only to find out later that there is a main report which relies on it. I was hoping someone new of an easier method of pulling this information from the SQL tables, but it looks like the two referenced report types are handled totally differently.

Thanks to those who attempted to help locate this information.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
Answer found elsewhere. The previously proposed suggestions, although appreciated, were either not applicable to the current situation or only provided information I already had.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.