Category Archives: Business Intellegence (SSIS,SSRS,SSAS)

How to get details of data sources and associated command with reports on Report Server

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’,
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ AS REP
)
SELECT c.Path ,c.Name ,DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM ReportServer_SQL2008_Prod.[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) ) LIKE ‘%quicktrend%’

How to get the list of parameters used in Reports

SELECT a.name as ReportName,
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_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
–AND C.Name = ‘%/Dashboard/KnowledgeBase%’
) a
CROSS APPLY ParameterXML.nodes(‘//Parameters/Parameter’) p ( Paravalue )

How to get report level permission on Report Server

SELECT C.Name,U.UserName,R.RoleName,R.Description,U.AuthType FROM ReportServer_SQL2008_Prod.dbo.Users U
JOIN ReportServer_SQL2008_Prod.dbo.PolicyUserRole PUR
ON U.UserID = PUR.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Policies P
ON P.PolicyID = PUR.PolicyID
JOIN ReportServer_SQL2008_Prod.dbo.Roles R
ON R.RoleID = PUR.RoleID
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON C.PolicyID = P.PolicyID

Note- For a specific report use below code
SELECT C.Name,U.UserName,R.RoleName,R.Description,U.AuthType
FROM ReportServer_SQL2008_Prod.dbo.Users U
JOIN ReportServer_SQL2008_Prod.dbo.PolicyUserRole PUR
ON U.UserID = PUR.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Policies P
ON P.PolicyID = PUR.PolicyID
JOIN ReportServer_SQL2008_Prod.dbo.Roles R
ON R.RoleID = PUR.RoleID
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON C.PolicyID = P.PolicyID
WHERE c.Name = @name of your report
ORDER BY U.UserName

How to get all folder level permission on Report Server

SELECT CASE RSCatalog.TYPE
WHEN 1 THEN ‘Folder’
ELSE ‘Report’
END AS [Type] ,
RSCatalog.Path ,
RSCatalog.Name AS Report ,
Users.UserName ,
Roles.RoleName
FROM [dbo].[Catalog] RSCatalog
INNER JOIN [dbo].[PolicyUserRole] PolicyUserRole ON RSCatalog.PolicyID = PolicyUserRole.PolicyID
INNER JOIN [dbo].[Roles] Roles ON PolicyUserRole.RoleID = Roles.RoleID
INNER JOIN [dbo].[Users] Users ON PolicyUserRole.UserID = Users.UserID
WHERE RSCatalog.TYPE IN ( 1, 2 )
ORDER BY RSCatalog.Path ,
RSCatalog.Name ,
Users.UserName

How to get list of all hosted reports and their path on report server

Declare @Namespace NVARCHAR(500)
Declare @SQL VARCHAR(max)
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_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) X
SELECT @Namespace = REPLACE(@Namespace,’xmlns=”‘,”) + ”
SELECT COALESCE(C.Path, ‘Unknown’) as ReportPath,Name,CreatedBy = U.UserName
,CreationDate = C.CreationDate
,ModifiedBy = UM.UserName
,ModifiedDate
FROM ReportServer_SQL2008_Prod.dbo.Catalog C
JOIN ReportServer_SQL2008_Prod.dbo.Users U
ON C.CreatedByID = U.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Users UM
ON c.ModifiedByID = UM.UserID