Hello Tessitura collective Mind!
Has anyone written scripts or found strait forward code based ways to find out each of their reports -- and what procedures they use? We've got a multitude of versions, and updates and new and old reports. These reports are a combination of SSRS and Infomaker formats. What would be amazing to do is have some query to filter on to be able to find if a given procedure is linked to a certain report/SSRS or Infomaker file? Any and all suggestions are appreciated. Many thanks, -Lisa
We have a frequency report that uses a date range of usage and one or more report categories. The report shows the report, last run, and frequency. We also try to tail the end of the report name with SSRS to distinguish the type. Custom and standard reports are included. PM me and I can go into more detail.
The information about what stored procedure backs a report is stored outside of the impresario database, so it is not immediately accessible by a typical SQL query. InfoMaker reports are stored in .pbl files (good luck trying to access those programmatically) and SSRS reports are stored in the XML-based RDL format, which ultimately do live in your SQL server. If you SELECT name, CONVERT(xml, CONVERT(varbinary(max), content)) AS rdl FROM ReportServer$INSTANCENAME.dbo.Catalog WHERE type = 2, you'll find the RDL XML for all of your reports... and in theory, you could query the XML file to find your <DataSet><CommandText> elements; this is all supported in T-SQL, as a matter of fact. I don't know how to use XQuery, but I found some code from a blog post in a google cached page that actually does the trick. I'm reposting it as a snippet here: https://bitbucket.org/snippets/TN_WebShare/Ee97Bn
My recommended approach is to maintain internal documentation for all custom reports that lists the dependent database objects, just like Tessitura's documentation does for all canned reports.
Hi Lisa, I have written a report that returns the Report Name, and the command text (whether it be T-SQL or a Stored Procedure call) for all SSRS reports. I can share that with you if you are interested.
Debbie
Thank you. I will check this out. Also if there is ever a way to find the stored procedures from infomaker reports that would be super helpful too. Do you think anyone at the Tessitura Network know this?
I'm not Lisa but I'm interested!
Ditto what Nancy said.
3rded for me. That sounds awesome
I would also be interested!