Retrieve SSRS report server database information

아티클
04/04/2015

Introduction

Microsoft provided SSRS for report development. we develop report for business analysis and decision making purpose. SSRS report uses RDL file for report implementation. these RDL file contains report details like its Datasource,Parameter,Dataset,Design layout etc. Business person access these reports from report server URL. we deploy these reports on report server to make it access from business user. Report server uses ReportServer database to store details of all report. when report server is configured, we pass this report server database information. Report server database is a predefined template based database. which contain mutiple table to collect report information. this article will explain all such table of report server database which holds report information.

Background

Report server database contains details of all SSRS report.database administrator manages this database periodically. it helps user to get information of any report. it also allows developer to recover any report, if its source code is missing. it means developer can not only gets all report description rather they can recover all report RDL file easily.

Using the code

Connect to Report server Database

Get list of tables available in report server database

 Select Name,Create_Date,Modify_Date from sys.tables

above query will display all table details available in report server database. it has total 31 tables available.

Get list of Reports available in Reportserver Database

 Select Name,Path,CreationDate,ModifiedDate from Catalog

Catalog table contains all report details.

Get Details of Specific report

 Select Name,Path,CreationDate,ModifiedDate from Catalog Where Name ='Simple Test Report.rdl'

above example will get details of sample report Simple Test Report.rdl

Get all available Datasource information in Report server database

 Select distinct Name from DataSource Where Name is NOT NULL

Get Datasource Information of specific report

 Declare @Namespace NVARCHAR(500)
Declare @SQL   VARCHAR(max)
Declare  @ReportName NVARCHAR(850)
SET @ReportName='Simple Test Report.rdl'

SELECT @Namespace= SUBSTRING(
                 x.CatContent  
                 ,x.CIndex
               ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
             )
     FROM
     (
         SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
              ,CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
         FROM Reportserver.dbo.Catalog C
        WHERE C.Content is not null
           AND C.Type  = 2
  ) X

SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace +''', ''https://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 top 1 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 ;'

EXEC(@SQL)

above example will return complete datasource information of given report Simple Test Report.rdl. it will display report name, datasource name,connection string and data provider of datasource.

Get Available Parameter with details in specific Report

  SELECT Name as ReportName
        ,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)') 
       ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)') 
    ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)') 
       ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)') 
       ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)') 
       ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)') 
     ,ParameterPrompt = 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 top 1 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  =  'Simple Test Report.rdl'
    ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )

above example query will return all parameter details of given SSRS report. it will return ReportName,ParameterName,ParameterType,ISNullable,ISAllowBlank,ISMultiValue,ISUsedInQuery,ParameterPrompt, DynamicPrompt,PromptUser,State related information.

Show owner details of specific report

 Select C.Name,C.Path,U.UserName,C.CreationDate,C.ModifiedDate from Catalog C
INNER Join Users U ON C.CreatedByID=U.UserID
Where C.Name ='Simple Test Report.rdl'

above example will display owner name of given report who has develop this report. it gets user details from Users table.

Search in report server database for specific object

 With Reports
AS
(
Select Name as ReportName,CONVERT(Varchar(Max),CONVERT(VARBINARY(MAX),Content)) AS ReportContent from  
Catalog Where Name is NOT NULL
)
Select ReportName from Reports Where ReportContent like '%tablename%'

above example is very useful , when it require to identify dependency of any table, procedure or funcrion in any report. it extract XML content of each report , convert them in varchar and then search for given object.Catalog table contains XML contents of all RDL file.

Recover report RDL file from report server database

 Select Name as ReportName,CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS ReportContent from  
Catalog Where Name ='Simple Test Report.rdl'

above example will get the XML contents of given report. user just need to copy this text and save into any file with extension RDL. once new RDL file is created, one can easily add this RDL into SSRS report project with Add existing Report option. report will work correctly.

Get configuration information of Report Server database

 Select Name,Value from ConfigurationInfo

Get available roles in Report Server

 Select RoleName,Description from Roles

Get Report Server Machine Name where Report server database is configured

 Select MachineName,InstallationID,InstanceName,Client,PublicKey,SymmetricKey from Keys
Where MachineName IS NOT NULL

Points of Interest

This article is very informative and interesting.it will help developer to know the backend part of SSRS report. developer can track usage of any SSRS report with this database easily.

History

No updates available

다음을 통해 공유