Saturday, July 9, 2011

SSRS - Reportserver Database Tables Explored - Part 1

Greetings!!! This is the first of the series of blogs about the metadata tables available in SSRS Reportserver Database. Reportserver database has all the details about reports deployed in the Report manager by storing them internally in tables.Let us take closer look on the tables and what it stores in it.In this blog , i will just give a small brief about tables and what kind of data it stores. And then provide sample queries as much as possible for better understanding. As a first installement , Let us take a look at below 3 tables.

                                              1. Catalog
                                              2. DataSource
                                              3. DataSets

Catalog

Stores the master data about all the objects related to SSRS listed below
 
1 = Folder
2 = Report (.rdl)
3 = .XML
4 = Link Report
5 = Data Source (.rds)
6 = Model
8 = Shared Dataset
9 = Report Part

This is the base master table where all the objects related to the SSRS is stored.

Some important fields
Parentid   - Under which object this object is present.
Type         - Type of the object as metioned above.
Content    - Has the report xml stored in varbinary format.
Properties - Has the XML with the properties of the object.
Parameter - Has the XML with parameters used in the reports.

  
DataSource
Stores the list of shared and embedded data sources available.

Some important fields
ItemId - Reference to the catalog table
Link     - Reference to the Report to which this datasource is linked.

  
DataSets
Stores the list of the shared datasets available.

Some important fields
ItemId - Reference to the catalog table.
LinkId  - Reference to the Report to which this datasource is linked.
Name   - DataSet Name in the report.

Sample usage queries

1. To View the Report code in XML format from content field in Catalog Table

SELECT  Name,Convert(XML,(Convert(VARBINARY(MAX),Content))) AS ReportXML
  FROM  ReportServer.dbo.Catalog
 WHERE  Content IS NOT NULL
   AND  [Type] = 2 -- For Report objects alone


2. Get the Report's Created/Modified User and Created/Modified Dates.

SELECT Name
      ,CreatedBy = U.UserName
      ,CreationDate = C.CreationDate
      ,ModifiedBy = UM.UserName
      ,ModifiedDate
  FROM Reportserver.dbo.Catalog C
  JOIN Reportserver.dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN Reportserver.dbo.Users UM
    ON c.ModifiedByID = UM.UserID
 WHERE Name = 'ReportName'

3. Get the List of Report Parameters for the given Report

SELECT  Name = Paravalue.value('Name[1]', 'VARCHAR(250)')
       ,Type = Paravalue.value('Type[1]', 'VARCHAR(250)')
       ,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
       ,AllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
       ,MultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
       ,UsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
       ,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
       ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
       ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
       ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
 FROM (
     SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
       FROM  ReportServer.dbo.Catalog C
      WHERE  C.Content is not null
        AND  C.Type  = 2
        AND  C.Name  =  'ReportName'
    ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )

4. Get the Data Sources used in the Report
Note : The XML Schema used here is for SQL 2011. You need to change it to make it work for other versions. To see the schema use the first query in the samples list.

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  ReportName     = name
       ,DataSourceName   = x.value('(@Name)[1]', 'VARCHAR(250)')
       ,DataProvider   = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
       ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type  = 2
      AND  C.Name  = 'ReportName'
        ) a
  CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
 ORDER BY name ;

5. Get the Data Sets used in the Report.
Note : The XML Schema used here is for SQL 2011. You need to change it to make it work for other versions. To see the schema use the first query in the samples list.

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  ReportName    = name
       ,DataSetName    = x.value('(@Name)[1]', 'VARCHAR(250)')
       ,DataSourceName  = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
       ,CommandText    = x.value('(Query/CommandText)[1]','VARCHAR(250)')
       ,Fields      = df.value('(@Name)[1]','VARCHAR(250)')
       ,DataField    = df.value('(DataField)[1]','VARCHAR(250)')
       ,DataType    = df.value('(rd:TypeName)[1]','VARCHAR(250)')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type = 2
         AND  C.Name = 'ReportName'
     ) a
  CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
  CROSS APPLY x.nodes('Fields/Field') f(df)
ORDER BY name

6. Get the list of Data Sources used by Reports using DataSources Table.

SELECT D.Name
       ,'Using Report '
      = CASE
        WHEN D.Name IS NOT NULL THEN  C.Name
        ELSE 'Shared Data Source'
        END
      ,'IsSharedDataSource' = CLink.Name
     FROM DataSource D
     JOIN Catalog C
     ON D.ItemID = C.ItemID
LEFT JOIN Catalog CLink
     ON Clink.ItemID = D.Link
  WHERE C.Name = 'ReportName'
I will continue with next set of tables in Part 2.  Bye for now !!!

3 comments:

  1. HI a thanks for this fine page.

    For datasource, XML queries (alls, not only yours) are returning wrong information.
    In fact I am not sure that datasource's updates made by the web interface or web service or SSMS or rs.exe are reflected in the XML code embedded in SSRS database.

    What do you think about that point?

    ReplyDelete
  2. Hi !

    I need one help, i am updating report footer by script and it actually changing XML of rdlc file. Now my problem is that report is now updated on my report site, but when i open it in report builder i can see that change, so can you tell me what can be the reason for it. Its really emergency for me.

    ReplyDelete
  3. Pretty good page!!!

    I have a question: When a report is created, let's say in Development, it points to an specific Data Source or Custom Data Source. When the report is loaded into PRODUCTION Report Server, it gets converted to Binary and loaded into the catalog table as you mentioned. If now I change the Data Source of the loaded report, looks like the binary doesn't change. What it changes is the Data Source at the Data Source table, correct???

    ReplyDelete