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 +''', ''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 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.
