This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, November 4, 2012

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

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