Interroger le catalogue SSRS

Requête pour obtenir la liste des rapports, dossiers, sources de données, etc… du catalogue SSRS. A lancer dans la base ReportServer.

 /*-------------------------------------------------------------------
[SCRIPT] Query SSRS Catalog
[DATABASE] ReportServer
[DATEMAJ] 20200228 
-------------------------------------------------------------------*/
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'
           WHEN 11 THEN
               'KPI'
           ELSE
               CAST(Type AS VARCHAR(100))
       END AS TypeName,
                      --, content
       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,
       CTG.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;

/*-------------------------------------------------------------------

[SCRIPT] Query SSRS Catalog

[DATABASE] ReportServer

[DATEMAJ] 20200228

-------------------------------------------------------------------*/

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'

WHEN 11 THEN

'KPI'

ELSE

CAST(Type AS VARCHAR(100))

END AS TypeName,

--, content

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,

CTG.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;