Table Explaination for dbo.catalog table in ReportServer database

Question

Hello,

 

I am trying to look for the explaination for the columns in dbo.catalog table. Is there any place i have explainations for these tables?

 

For instance there is a column 'Type' in dbo.catalog table. But not sure what information it has - it has values like 1,2,3,4 .. Wanted to know what these values are.. Any idea on this?

 

Thanks, Uma

Answer 1

Hi Uma,

 

We do not document or support querying any Report Catalog tables.  If you need to find out what items are in the report catalog, use the ListChildren SOAP API to list them.  We do support the ExecutionLog (Table/View; version dependent) or the ExecutionLog2 View.    Otherwise, writing queries against the SSRS DB is not supported and the schema, content, interpretation of the content is subject to change without notice.

 

Probably not the answer you wanted to hear...

-Lukasz

Answer 2

Hi Lukasz,

 

Do you know if it is possible to copy a modelID to another model in the catalog table? Or is the ModelID stored in another table(s) within the ReportServer database?

 

I had to generate a new report model and cannot overwrite the existing due to it having a different ModelID. I need to overwrite, as there are numerous reports that have been created against the original model?

 

Thanks!

-Carla

 

Answer 3

Hi Uma,

 

It is referring to the type of Path column.

 

if the value was 1 then the path is a folder.

Value 2 refers to Report

Value 3 refers to Resource

Value 4 refers to Linked Report

Value 5 refers to Data Source

 

i hope it makes you clear your doubt

 

 

Answer 4

Hi Carla - You can export the previous model and then upload it again to the server.  Unfortunately, model item ids are not guaranteed to be identical across multiple Generate Model calls. 

 

Hope that helps,

-Lukasz

Answer 5

Thank you so much b_rusly

---

Uma

Answer 6

Hi All,

I saw value 6 in type column of Catalog table, am wondering what does '6' represent?

thanks,

Ling

Answer 7

6 = report model

Answer 8

In case someone end up here using SQL Server 2008 R2, newly-added values since R2 are:

8 = Shared Dataset
9 = Report Part

---

MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

Answer 9

Hi Valentino,

can we get the detail of tables and views in ReportServer database?

---

Sushant

Answer 10

Hi Sushant,

As Lukasz said, Microsoft does not document the internal report server tables/views.  However, that of course doesn't mean that you can't query them.  As long as you don't rely on the structure of the tables for any production systems, I don't see any harm in running select statements on those tables.

Have a look at the script here for some interesting usage of the reportserver tables: http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

---

MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

Answer 11

Hi valentine,

Thanks for your answer. But can you help in getting the list of reports deployed in project web access site?

in catalog table i can find out those names but those contain the deleted report names also. how can i separate reports from deleted reports to existing reports?

Thanks

---

Sushant

Answer 12

in catalog table i can find out those names but those contain the deleted report names also. how can i separate reports from deleted reports to existing reports?

The Catalog table does not contain deleted reports. Once they're deleted, they're gone from the table as well.  Are you sure you're not confusing "deleted" with "hidden"?  To filter out hidden reports, have a look at the Hidden column in the Catalog table:

WHERE Hidden = 0

---

MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

Answer 13

I am experiencing the same issue with deleted reports showing in the Catalog table. Any ideas why reports would still be showing in the table?

Answer 14

deleted reports should come off the catalogue table as soon as deleted.

also for the Type use a case:

CASE
WHEN C.type = 1 THEN '1-Folder'
WHEN C.type = 2 THEN '2-Report'
WHEN C.type = 3 THEN '3-File'
WHEN C.type = 4 THEN '4-Linked Report'
WHEN C.type = 5 THEN '5-Datasource'
WHEN C.type = 6 THEN '6-Model'
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS ItemType,

by

Raj Sharma - Lucky Dice

Answer 15

They should be deleted from the catalogue as soon as you physically remove them.

As a test - do a select all on the Catalog table and then create a report, you will notice an extra rows created.

As soon as you delete this new report, your rows sould also go down by one.

For the types, use the CASE statment below:

CASE
WHEN C.type = 1 THEN '1-Folder'
WHEN C.type = 2 THEN '2-Report'
WHEN C.type = 3 THEN '3-File'
WHEN C.type = 4 THEN '4-Linked Report'
WHEN C.type = 5 THEN '5-Datasource'
WHEN C.type = 6 THEN '6-Model'
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS [ItemType]

Lucky Dice - Decisive Technology

Answer 16

Adding SQL SSRS 2016+ items...

CASE
WHEN C.type = 1 THEN '1-Folder'
WHEN C.type = 2 THEN '2-Report'
WHEN C.type = 3 THEN '3-File'
WHEN C.type = 4 THEN '4-Linked Report'
WHEN C.type = 5 THEN '5-Datasource'
WHEN C.type = 6 THEN '6-Model'
WHEN C.type = 8 THEN '8-Shared Dataset'
WHEN C.type = 9 THEN '9-Report Part'

WHEN C.type = 11 THEN 'KPI'
WHEN C.type = 12 THEN 'Mobile Report (folder)'
WHEN C.type = 13 THEN 'Power BI Desktop Document'

ELSE 'Unknown' END AS [ItemType]

Answer 17

Thanks for this

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 18

This query will give you Folder and Subfolders up-to 5 level:

USE [ReportServer]
GO
SELECT DISTINCT 
       T3.[Path], 
       T3.[FolderLevel],
       CASE
           WHEN T3.[FolderLevel] = 0
           THEN 'Home'
           ELSE T3.[Folder_1]
       END [Folder_1], 
       T3.[Folder_2], 
       T3.[Folder_3], 
       T3.[Folder_4], 
       REPLACE(LEFT(REPLACE(T3.[Path], '/' + T3.[Folder_1] + '/' + T3.[Folder_2] + '/' + T3.[Folder_3] + '/' + T3.[Folder_4], ''), CHARINDEX('/', REPLACE(T3.[Path], '/' + T3.[Folder_1] + '/' + T3.[Folder_2] + '/' + T3.[Folder_3] + '/' + T3.[Folder_4], ''), 2)), '/', '') [Folder_5],
       CASE
           WHEN T3.[Type] = 2
           THEN T3.[ReportName] + '.rdl'
           WHEN T3.[Type] = 12
           THEN T3.[ReportName] + '.rsmobile'
           WHEN T3.[Type] = 13
           THEN T3.[ReportName] + '.pbix'
           ELSE T3.[ReportName]
       END [ReportNameWithExtension], 
       T3.[ReportName], 
       REPLACE(T3.[Path], '/' + ISNULL(T3.[Folder_1],'') + '/' + ISNULL(T3.[Folder_2],'') + '/' + ISNULL(T3.[Folder_3],'') + '/' + ISNULL(T3.[Folder_4],''), '') AS FolderPath, 
       T3.[ItemType], 
       T3.[Type]
FROM
(
    SELECT DISTINCT 
           T2.[Folder_1], 
           T2.[Folder_2], 
           T2.[Folder_3],
           CASE
               WHEN REPLACE(LEFT(REPLACE(T2.[Path], '/' + T2.[Folder_1] + '/' + T2.[Folder_2] + '/' + T2.[Folder_3], ''), CHARINDEX('/', REPLACE(T2.[Path], '/' + T2.[Folder_1] + '/' + T2.[Folder_2] + '/' + T2.[Folder_3], ''), 2)), '/', '') = ''
               THEN NULL
               ELSE REPLACE(LEFT(REPLACE(T2.[Path], '/' + T2.[Folder_1] + '/' + T2.[Folder_2] + '/' + T2.[Folder_3], ''), CHARINDEX('/', REPLACE(T2.[Path], '/' + T2.[Folder_1] + '/' + T2.[Folder_2] + '/' + T2.[Folder_3], ''), 2)), '/', '')
           END [Folder_4], 
           T2.ItemType, 
           T2.[Path], 
           T2.ReportName, 
           T2.[FolderLevel], 
           T2.Type
    FROM
    (
        SELECT T1.[Folder_1], 
               T1.[Folder_2],
               CASE
                   WHEN REPLACE(LEFT(REPLACE(T1.[Path], '/' + T1.[Folder_1] + '/' + T1.[Folder_2], ''), CHARINDEX('/', REPLACE(T1.[Path], '/' + T1.[Folder_1] + '/' + T1.[Folder_2], ''), 2)), '/', '') = ''
                   THEN NULL
                   ELSE REPLACE(LEFT(REPLACE(T1.[Path], '/' + T1.[Folder_1] + '/' + T1.[Folder_2], ''), CHARINDEX('/', REPLACE(T1.[Path], '/' + T1.[Folder_1] + '/' + T1.[Folder_2], ''), 2)), '/', '')
               END [Folder_3], 
               T1.ItemType, 
               T1.[FolderLevel], 
               T1.[Path], 
               T1.ReportName, 
               T1.Type
        FROM
        (
            SELECT CT.[Name] AS ReportName, 
                   REPLACE(LEFT(CT.[Path], CHARINDEX('/', CT.[Path], 2)), '/', '') [Folder_1],
                   CASE
                       WHEN REPLACE(LEFT(REPLACE(CT.[Path], LEFT(CT.[Path], CHARINDEX('/', CT.[Path], 2)), ''), CHARINDEX('/', REPLACE(CT.[Path], LEFT(CT.[Path], CHARINDEX('/', CT.[Path], 2)), ''), 2)), '/', '') = ''
                       THEN NULL
                       ELSE REPLACE(LEFT(REPLACE(CT.[Path], LEFT(CT.[Path], CHARINDEX('/', CT.[Path], 2)), ''), CHARINDEX('/', REPLACE(CT.[Path], LEFT(CT.[Path], CHARINDEX('/', CT.[Path], 2)), ''), 2)), '/', '')
                   END [Folder_2], 
                   (LEN(CT.[Path]) - LEN(REPLACE(CT.[Path], '/', ''))) - 1 AS FolderLevel, 
                   CT.[Path], 
                   CT.[Type],
                   CASE
                       WHEN CT.type = 1
                       THEN '1 - Folder'
                       WHEN CT.type = 2
                       THEN '2 - SSRS Paginated Report'
                       WHEN CT.type = 3
                       THEN '3 - File'
                       WHEN CT.type = 4
                       THEN '4 - Linked Report'
                       WHEN CT.type = 5
                       THEN '5 - Datasource'
                       WHEN CT.type = 6
                       THEN '6 - Model'
                       WHEN CT.type = 8
                       THEN '8 - Shared Dataset'
                       WHEN CT.type = 9
                       THEN '9 - Report Part'
                       WHEN CT.type = 11
                       THEN '11 - KPI'
                       WHEN CT.type = 12
                       THEN '12 - SSRS Mobile Report'
                       WHEN CT.type = 13
                       THEN '13 - Power BI Report'
                       ELSE 'Unknown'
                   END AS [ItemType]
            FROM dbo.[Catalog] AS CT
            WHERE CT.[Type] IN(2, 12, 13)
        ) T1
    ) T2
) T3
ORDER BY [FolderLevel], 
         [Folder_1], 
         [Type]

---

Thanks Shiven:) If Answer is Helpful, Please Vote