mssqltips logo

Script to determine SQL Server Reporting Services parameters, path and default values

By:   |   Updated: 2009-09-17   |   Comments (27)   |   Related: > Reporting Services Parameters

Problem
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?

Solution
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.

Here is the script to generate the data with all the report names, parameters and its default values:
 
--Find all the reports, and thier parameters and thier default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd --ReportDefinition
)
SELECT 
   NAME
   PATH
   x.value ('@Name''VARCHAR(100)'AS ReportParameterName
   x.value ('DataType[1]''VARCHAR(100)'AS DataType
   x.value ('AllowBlank[1]''VARCHAR(50)'AS AllowBlank
   x.value ('Prompt[1]''VARCHAR(100)'AS Prompt
   x.value ('Hidden[1]''VARCHAR(100)'AS Hidden
   x.value ('data(DefaultValue/Values/Value)[1]''VARCHAR(100)'AS Value
FROM (
   SELECT  PATH
           NAME
           CAST(CAST(content AS VARBINARY(MAX)) AS XMLAS ReportXML 
   FROM ReportServer.dbo.Catalog 
   WHERE CONTENT IS NOT NULL AND TYPE 2
   A
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter'R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report
ORDER BY NAME

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 Steps

  • 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


get scripts

next tip button



About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, August 07, 2014 - 7:13:57 AM - Erlon Rodrigues Cruz Back To Top

Hi Sankar,

 

Nice tip. My problem is that I don't have access to the database SQL console. Is it possible to retrive that information using the execution or the management endpoint??

e.g:

mngm_endpoint = "http://<server_ip>/ReportServer/ReportService2010.asmx?wsdl"

exec_endpoint = "http://<server_ip>/ReportServer/ReportExecution2005.asmx?wsdl"


Thursday, September 19, 2013 - 1:18:59 PM - Steve Back To Top

Hmmm... noticed that some of my reports have the 2010 schema path in the rdl (developed in VS 2012), so I ran the query with that changed and got back 43 reports.  Looks like I have a mix of schema paths...I vaguely recall reading something somewhere about the schema updating only if you use features unique to the later schema... so I'll amend my previous comment form "this needs work" to "be careful"!


Thursday, September 19, 2013 - 1:11:07 PM - Steve Back To Top

This needs work... ran it on a 2008 R2 instance, with the schema URL updated, and it only listed 14 of my reports.  There are 50+ reports, all of which have parameters. 


Wednesday, June 19, 2013 - 9:54:22 AM - dondadda Back To Top

I'm using SSRS again, and this was brilliant. I have tried to find RSSCRIPTER but unable to locate a valid source, this has helped a lot.


Thursday, June 06, 2013 - 6:46:54 AM - Wayne Back To Top

Hi

I am looking for something similar but to be able to access and change the parameters of a linked report, is this possible?


Wednesday, June 05, 2013 - 3:26:20 PM - xtrout Back To Top

I am looking for something similar for listing the paramter used in a subscription. We have a report that has 60+ subscritptions based on the change of one parameter (application name). Unfortunately the default screen displays the same info with each subsctiption so there is no way to differentiate the application in the list. Do you have any code that may help me there? I cannot seem to wrap my brain around parsing out the XML parameter values.


Tuesday, March 12, 2013 - 7:58:14 AM - suchana Back To Top

pLZ HELP ME TO DO THAT


Tuesday, March 12, 2013 - 7:56:33 AM - suchana Back To Top

HI

 

 New to SSRS i need to generate a report using XML.

Example:

Table:Employee

Columns :EmpID

              Emp Name,Salary,Address,

Information is Stored In DB Under EMPloyee table with EMPXML column aginast EMPID(<

FIELDS><VALUES><EmpName>XYZ</Empname><Salary>10000</Salary><Address>New Jersy</Address></VALUES></FIELDS>)

 

Need To generate Report based on Emp Name(parametr)

Report should be

Emp Name                          Salary                      Address

Xyz                                       20k                            NJ,USA

 

 

  

 

 


Tuesday, February 05, 2013 - 5:52:13 PM - Gayle Back To Top

Great Post!

 

Thank you


Thursday, April 05, 2012 - 5:36:18 AM - Mayur Back To Top

hi.

Good post.

I have one problem regarding getting value of multi-value parameter of SSRS report.

In ReportServer database, how i fetch the value of multi-value parameter dataset, do you know any query or sp to fetch multi-value parameter dataset name used in SSRS.

 

Actually in my .net application i have to bind parameter which i get from ReportServer. if parameter type is multi-value then i have to show dropdownlist for that parameter, but what are the items in that dropdown. i want to bind that dropdownlist. so please send me query or sp which is used to fetch multi-value parameter value

Thanks in advance

 


Wednesday, April 04, 2012 - 1:31:02 PM - Kris Back To Top

This is awesome! I was just working on writing something like this.  You rock, Sankar.  

Good catch Chadwicksmith with the 2008 stuff.     =D


Tuesday, July 27, 2010 - 1:28:57 AM - SankarReddy Back To Top

Just replace the definition like below and you should be fine. --SQL Server 2005 DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', --SQL Server 2008 DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

 


Tuesday, July 27, 2010 - 12:53:24 AM - [email protected] Back To Top

for me also same problem. when I executed the sql in my local server(sql 2008) did not find a single record. Could you suggest.


Wednesday, March 31, 2010 - 9:53:28 AM - ChadwickSmith Back To Top

I figured it out.  If you're using SQL 2008, you have to replace this line:

      DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',

with this:

      DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

 

Once you change that it works fine!


Wednesday, March 31, 2010 - 9:24:42 AM - ChadwickSmith Back To Top
I have the same problem, except It doesn't return any rows for me.  There are 224 reports in dbo.Catalog with TYPE = 2, but it doesn't return any rows when I run your query.  We are running SQL 2008 with SharePoint Integrated Mode.

Friday, February 12, 2010 - 2:29:36 PM - rduclos Back To Top
I have done something similar in the past. I use it to display a section on the first page of all my reports to show what parameters and values were used or changed from defaults on the report.

http://rduclos.wordpress.com/2008/12/18/reporting-services-2005-dynamic-parameter-list/

Thursday, November 12, 2009 - 1:19:22 PM - SankarReddy Back To Top

Kirthika,

I heard back from MSFT and thier response is same as mine earlier, which is "don't do this" via T-SQL. You can do this via Reporting Services API for which I gave the link above. Even if you try with T-SQL and note that if something goes wrong (which might happen, if you apply a hotfix, service pack, version etc...) your instance will NOT be supported by MSFT. Thats a hefty price tag for someting so trivial.

 


Saturday, October 31, 2009 - 2:13:44 PM - kirthikajanani.p Back To Top

 Thanks a lot for looking into this. Really appreciate the help. 


Saturday, October 31, 2009 - 1:39:10 PM - SankarReddy Back To Top

Kirthika,

MSFT recommends NOT to change the ReportServer database schema. Upgrading ReportServer Database

After your question I searched a little bit more and found a link from MSFT that you can do updates to ReportDefinition using the .Net framework using VB/C#. I am NOT sure if it is recommended doing this via TSQL. Let me find that out with some experts and will get back to you.

Tutorial: Updating Reports Using Classes Generated from the RDL Schema

 

 


Saturday, October 31, 2009 - 1:07:21 PM - kirthikajanani.p Back To Top

 Hello Sankar,

 

Thank you for the reponse. I totally agree with you on this aspect but updating huge number of reports manually is near impossible.

Have you faced problems in such an update or does MSFT advice against this ?

 

Thanks

Kirthika


Friday, October 30, 2009 - 6:09:43 PM - SankarReddy Back To Top
[quote user="kirthikajanani.p"] I am also trying to update one parameter  in all these xmls  at one go using this script. [/quote]

Kirthika,

I am NOT sure if it is a wise decision to do. If you ask me, I wouldn't advice to do that way. MSFT recommends NOT to touch these system tables or change the structure of the tables of the ReportServer database. I understand its a pain to change all reports and deploy them again but that is the correct and *safest* way to update the reports. Updating the XML to make changes to the report is asking for trouble.

 


Friday, October 30, 2009 - 5:54:24 PM - kirthikajanani.p Back To Top

 Hello,

 This script was very useful. I am also trying to update one parameter  in all these xmls  at one go using this script.

 

Though am able to select the xml by convering image to xml and varbinary, I am having trouble contructing an update statement because it has to be stored not as xml but as image in the database.

 

Can someone please help by posting any update they have done to the  Catalog table using update statements ?

 

This will come in handy especially when you dont want to go change things in the deployed report but change parameters for these directly at the database level.

 

Thanks

Kirthika


Saturday, October 17, 2009 - 3:12:08 PM - SankarReddy Back To Top
Jerry, That's really weird. I don't have the configuration you have handy with me, is it possible for you to run the below steps if you don't mind. 1. Grab the XML from the dbo.Catalog and use it as an xml variable. 2. Modify the tip query to shred from the above XML variable in SQL Server 2005 instance. Or if you can send me a sample xml where you had the issue, I will be glad to take a look for you. Send me the xml to replace * with period

Friday, October 16, 2009 - 3:36:05 PM - jerryhung Back To Top

Weird, I ran the query and it only returned like 2 reports (5 parameters in total), out of the 174 reports (and many of them have default parameters)

I did look at a few of report's XML in .dbo.Catalog to see the <ReportParameters> filled with correct data as well, so it is quite odd

This is on SQL 2008 DB and SSRS, x64


Thursday, September 17, 2009 - 1:23:34 PM - JohnMarsing Back To Top

Thanks for the tip, this will be great for documenation purposes.  I don't have SSRS running on my laptop but I am using a win forms application that uses the report viewier controller.  Do you have any ideas how you could run your scrpit against my .rdlc files that I have created?

 Thanks John


Thursday, September 17, 2009 - 3:22:50 AM - SankarReddy Back To Top
Glad its useful to you.

Thursday, September 17, 2009 - 2:41:46 AM - NTHustler Back To Top
Nice tip, thanx. Actuallly need this today to do some pre deployment checking of big number of reports.


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools