Recently, I was asked which stored procedures power which Dynamics SSRS reports. If you want to go report by report, you can download any report from your SSRS website and open it in ReportBuilder.
But, as I was on a roll with my recent SSRS catalog query, I decided there had to be a way to get this information through a query. And I figured it out.
(I have to say, after working through this problem, I have a burning desire to get away from T-SQL for a while (even though it was fun because I learned a bunch of stuff). My brain hurts. It’s time to get back to some strategic IT consulting.)
Below is the query that generates the information. Thanks to Sankar Reddy on MSSQLTips.com whose article was instrumental in helping me figure this out.
Note: You can download the query from the link provided at the end of this blog post.
Here’s some sample output:
You can adapt this query to run over your root report folder. You can also opt to see all queries, not just stored procedures, by commenting this out:
Keep in mind that query strings can be much longer than calls to stored procedures—and the data won’t dump to Excel very well.
If this query makes your brain hurt too, I’ve dumped the results from the Dynamics GP sample company into an Excel spreadsheet. Download the query and spreadsheet here.
If you’d like similar content delivered to your inbox, sign up for our newsletter. Simply enter your email into the sign up box located on the top right of this page.