Script to determine SQL Server Reporting Services parameters, path and default values
By: Sankar Reddy | Updated: 2009-09-17 | Comments (27) | Related: > Reporting Services Parameters
Our company has hundreds of reports with daily, weekly, monthly, quarterly, yearly and ad-hoc schedules. Is there a way to identify all the reports with their path, parameters and default values as well as if the parameters are hidden or visible when executing the reports? How do I check this information programmatically without manually reviewing each report? Is this information stored in system tables or DMV's that I can query?
SQL Server Reporting Services stores a lot of metadata about the reports in a rich set of tables in the ReportServer database. For this solution, we will take advantage of the dbo.Catalog table where the report characteristics are stored in a column named Content. The data is in XML format, but it is stored as image data type. Luckily, SQL Server 2005 offers a rich set of XML functions and we will be using those to generate the data rather than having to review the reports manually.
The result from the script above will look something like this:
Here are few more screenshots on how the parameters might look on the DailyBlogReport:
The Report Definition Language (RDL) for these parameters is outlined below:
- Next time you need to determine report parameters, consider the script from this tip as an option to drill into that information.
- This tip also sheds some light into where the report metadata is stored and how to work with the data. As you face similar challenges, consider this script as a baseline to begin to address your needs.
- Take a look at more Reporting Services and XML tips on MSSQLTips.
Last Updated: 2009-09-17
About the author
View all my tips