Last active
February 16, 2024 00:37
-
-
Save itsalljustdata/b22067acb0df4fe1c1a366e66d1d97a2 to your computer and use it in GitHub Desktop.
SSRS Get Datasets from report definition
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' | |
| ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition | |
| ) | |
| SELECT CATDATA.Name AS ReportName | |
| ,CATDATA.Path AS ReportPathLocation | |
| ,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName | |
| ,xmlcolumn.value('(Query/DataSourceName)[1]', 'VARCHAR(250)') AS DataSoureName | |
| ,xmlcolumn.value('(Query/CommandText)[1]', 'VARCHAR(2500)') AS CommandText | |
| FROM (SELECT ItemID -- Unique Identifier | |
| ,[Path] --Path including object name | |
| ,[Name] --Just the objectd name | |
| ,ParentID --The ItemID of the folder in which it resides | |
| ,CASE | |
| [Type] --Type, an int which can be converted using this case statement. | |
| WHEN 1 THEN 'Folder' | |
| WHEN 2 THEN 'Report' | |
| WHEN 3 THEN 'File' | |
| WHEN 4 THEN 'Linked Report' | |
| WHEN 5 THEN 'Data Source' | |
| WHEN 6 THEN 'Report Model - Rare' | |
| WHEN 7 THEN 'Report Part - Rare' | |
| WHEN 8 THEN 'Shared Data Set - Rare' | |
| WHEN 9 THEN 'Image' | |
| ELSE CAST(Type as varchar(100)) | |
| END AS TypeName | |
| ,CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) AS reportXML | |
| ,LinkSourceID --If a linked report then this is the ItemID of the actual report. | |
| ,[Description] --This is the same information as can be found in the GUI | |
| ,[Hidden] --Is the object hidden on the screen or not | |
| ,CreatedBy.UserName CreatedBy | |
| ,CreationDate | |
| ,ModifiedBy.UserName ModifiedBy | |
| ,ModifiedDate | |
| FROM ReportServer.dbo.[Catalog] CTG | |
| INNER JOIN ReportServer.dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID | |
| INNER JOIN ReportServer.dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID | |
| WHERE [Type] = 2 -- Report | |
| AND [Path] like '/IRIS/XRC3313%' | |
| ) CATDATA | |
| CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable (xmlcolumn) | |
| --WHERE xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%ProductCategoryName%' | |
| ORDER BY CATDATA.Name |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment