Type column description of ReportServer Database

Here is the Type column description of ReportServer.Dbo.Catalog Table :

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)

Thanks,
Randhir

Usefull queries of ReportServer Database

http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

Thanks,
Randhir

Date Source's Dependent Report

Use ReportServer

GO

 

SELECT
    C2.Name AS DataSourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.DataSource AS DS
        INNER JOIN
    ReportServer.dbo.Catalog AS C
        ON
            DS.ItemID = C.ItemID
                AND
            DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
                        WHERE Type = 5) -- to  identifies data sources
        FULL OUTER JOIN
    ReportServer.dbo.Catalog C2
        ON
            DS.Link = C2.ItemID
WHERE
    C2.Type = 5
ORDER BY
    C2.Name ASC,
    C.Name ASC;

 

 

Thakns,

Randhir

Linked Report's Data Set ReportServer Reporting Service

Use ReportServer
GO

;WITH XMLNAMESPACES (

DEFAULT

'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rs

)

SELECT

LinkedReport,

x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,

x.value('CommandText[1]','VARCHAR(50)') AS CommandText

FROM (

SELECT

    c1.Name AS LinkedReport, CAST(CAST(C2.content AS VARBINARY(MAX)) AS XML) AS reportXML

FROM dbo.Catalog c1

INNER JOIN dbo.Catalog AS c2 ON c1.LinkSourceID=c2.ItemID

WHERE

c1.Type = 4

 

) a

CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

Thanks,
Randhir

 

 

 

Get only Linked Report from ReportServer database Reporting Services

Using Type 4 we can get all linked report from catalog table.

SELECT ItemID, Path, Name, ParentID, LinkSourceID
FROM Catalog
WHERE Type = 4

Thanks,
Randhir

Compare Date Range columns with Date Range Parameters

Comparing Date Range columns with Date Range Parameters always confuse us.
Here is the simple solution  using below query:


Declare @DateFrom DateTime, @DateTo DateTime
Set @DateFrom = '2012/10/01'
Set @DateTo = '2012/10/30'

Select *
From Schedule
Where
ScheduleFrom < = @DateTo And
ScheduleTo >= @DateFrom


 This is also described as Allen's Interval Algebra. 
http://en.wikipedia.org/wiki/Allen%27s_Interval_Algebra


Randhir