Querying SSRS Report MetaData for data sources , datasets associated query and connection string information

SSRS  metada    contains wealth    of information which   can be usefull   under varieous circumstances , but extracting    that information can be a timefull process , the basic information  is   stored    under the ReportServer  database  tables, but to retrieve the embedded data  from the report xml  ,the data needs to be   extracted into readable format from an xml format.

The below query  queries the report catalog table of ReportServer  Database   and flattens the xml content   from the Catalog table.

In this below dataset i have  mainly flattened the  Report xml   data   from the Content column of the Catalog table  ,  which was initially converted  to a varbinary type and than to xml,After the conversion to xml and  flattening  the xml , we can associate report name with its related datastes and datasources and connection string     as below



Declare @ReportName varchar(200)
Set @ReportName='SomeReport'
;With Cte
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] AS varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] WHERE type in (2))t 
where t.Name=@ReportName
)
/*** Get the Report Name and DataSource Name ***/,
ReportDataSources
As
(

Select 
dss.dtt.value('(@Name)[1]','varchar(100)') as dsDataSourceName,
Ltrim(Replace(dst.dts.value('(text())[1]','varchar(max)'),'/Data Sources/','')) AS DataSourceName,
Cnts.Cn.value('(text())[1]','varchar(100)') as Cnstring
FROM Cte
cross Apply cte.Content.nodes('//*:DataSource') dss(dtt)
Outer Apply dss.dtt.nodes('.//*:DataSourceReference') dst(dts)
outer Apply Cte.Content.nodes('.//*:ConnectString') Cnts(Cn)
) 
,ReportsAndDS
As
(
SELECT Distinct 
cte.name AS ReportName,

dss.dtt.value('(@Name)[1]','varchar(100)') AS DatasetName,

ds.dstt.value('(text())[1]','varchar(max)') AS DataSourceName,
Ltrim(Replace(shared.Sha.value('(text())[1]','varchar(100)'),'/Datasets/','')) AS SharedDataSet,
COALESCE(ct.cts.value('(text())[1]','varchar(max)'),'ReportQuery') as QueryType,
dst.dts.value('(text())[1]','varchar(max)') AS Query
FROM Cte
cross Apply cte.Content.nodes('//*:DataSet') dss(dtt)
cross Apply dss.dtt.nodes('.//*:CommandText') dst(dts)
Outer Apply dss.dtt.nodes('.//*:CommandType') ct(cts)
Outer Apply dss.dtt.nodes('.//*:DataSourceName') ds(dstt)
Outer Apply cte.content.nodes('.//*:SharedDataSetReference') shared(Sha)

)

,FtReportsAndDs
As
(

Select rs.ReportName,rs.DatasetName,rds.DataSourceName,rs.SharedDataSet ,rs.QueryType ,rs.Query, Substring(rds.Cnstring,CharIndex('=',rds.Cnstring,1)+1, CharIndex(';',rds.Cnstring,1)-(CharIndex('=',rds.Cnstring,1)+1)) as ServerName
from ReportsAndDS rs
Left Join ReportDataSources rds 
on rs.DataSourceName=rds.dsDataSourceName
)
/*** Get the DatSource Name and DatSource XML ***/
,DataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (5))t )
,SharedDataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (8))t )

,SharedDataSourceConnectionDetails
As
(
Select sdc.Name,dsr.ds.value('(text())[1]','varchar(100)') as DataSourceName ,
ddr.dr.value('(text())[1]','varchar(max)') as Query

from SharedDataSources sdc

Cross Apply sdc.Content.nodes('//*:DataSourceReference') dsr(ds)
Outer Apply sdc.Content.nodes('//*:CommandText') ddr(dr)

)
/*** Prepare the Final Query to Get Report Name and its asscoiated datasources datasets ,QueryType,Query and Connection String ***/

Select t.ReportName,t.DatasetName,t.DataSourceName,t.QueryType,t.Query, t.DatSourceConnectionDetails
,
t.DataSourceCredentials
, Isnull(Substring(DatSourceConnectionDetails,CharIndex('=',DatSourceConnectionDetails,1)+1, CharIndex(';',DatSourceConnectionDetails,1)-(CharIndex('=',DatSourceConnectionDetails,1)+1)),t.DataSourceName) as ServerName
from (
SELECT Distinct 
ds.ReportName,ds.DatasetName,IsNull(ds.DataSourceName,ds.ServerName) as DataSourceName,ds.Query,ds.QueryType,

dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials

FROM FtReportsAndDs ds Left join DataSources ctee ON
ds.DataSourceName=ctee.Name
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt)
UNION ALL
SELECT Distinct 
ds.ReportName,shd.Name as datasetName,shd.DataSourceName,shd.Query,ds.QueryType,
dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials
FROM ReportsAndDS ds Inner Join SharedDataSourceConnectionDetails shd
on ds.SharedDataSet=shd.Name
Left Join 
DataSources ctee ON
ctee.Name=shd.DataSourceName
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt))t

This is the snapshot of the above query without any filter applied for report  for this demo.

As you can see i haveflattened the xml content so that the we can see each report its associated dataset datasource name and its connection string, this can be very handy when we have very large number of reports   and wanted to migrate those reports to see the depended data sources   .

Splitting Strings in TSql using CTE

There are number of occasions where we need to split a string to individual values,one such common use  case is for multivalue parameters  in ssrs, when a stored procedure  is used  to  feed the dataset   we need to split the    string parameter  values  .


This can be implemented in a number of ways, i chooose to use a cte  and than embedd that cte in a table valued function,   and this use case  only  works  in sql server 2012 , for other versions, replace the  conditional IIF function with a case


Consider  u   got string values of countries as shown below

Declare @sp VarChar(max)='UK,USA,FRANCE,CANADA,MEXICO,BRAZIL,CHINA, MALAYSIA,TURKEY,INDIA,AUSTRALIA'


and we want to split it into individual countries ,to split the string, whatever logic we use, we need to know the  postion of  individual string  i.e the starting position and ending position of the sorrounded delimiters .

This is where recursive cte  can be very usefull,the way it is done is we use Charecter index   string function in the anchor query to get the first  end position of a delimiter   of first individual string   and 0 as the starting position, as there will be no delimeters at the start of string.

Than we use these values recursivly to get the  next delimeter position ,and to do that we  increment the previous  delimeter postion in the charecter index function start value,  in this case ,after the anchor query gets 3 as the first delimeter value  than it is incremented by 1 i.e   4 and this 4 will become the string starting  position for the second individual string i.e USA  and the charecter index gets the  next individual ending  delimeter position as 7 ,and this logic is repeated  recursively to build the starting and ending delimeter position for each string as shown in the second figure.


;With Cte
As
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

The above cte gets the delimited index values  recursivly    for each individual   string    as shown below

Once we  have these values ,the next statement
Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

simply selects the values between those delimeters
as shown below

 we can embedd this logic into a table valued function as shown below and subsequently use this function in the stored procedure  as shown in the query following this function


 Create   Function apn_split(@Sp varchar(max))

 Returns Table

 As
 Return
WIth Cte
as
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,iif(t=0,1,t+1),iif(tt=0,len(@sp)-t,tt-t-1) )  as Country  from Cte



Consider to build to report to analyse sales data (in this example  i'm using AdwentureWorksDW2008R2) which will  allow the end user to  select multiple countries    as show below

Than you can use the above function in the stored procedure to pass  multiple values from ssrs to stored procedure  as shown below

Alter Procedure ap_ProductSalesByCountry

@Country varchar(max)
As
SELECT     DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName,
                      DimSalesTerritory.SalesTerritoryCountry, DimDate.EnglishMonthName, DimDate.CalendarYear, FactResellerSales.SalesAmount
FROM         FactResellerSales INNER JOIN
                      DimSalesTerritory ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN
                      DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
                      DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE     DimSalesTerritory.SalesTerritoryCountry  in ( Select distinct country    from apn_split(@Country))