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

Get Agent Job Name for SSRS Report Subscription

Every time when we create Reporting Service Report subscription , a new Agent Job creates.
Here is the useful script to get Agent Job Name for each subscription :

Use ReportServer
GO
SELECT    
Schedule.ScheduleID AS SQLAgent_Job_Name,
Subscriptions.Description AS SubscriptionName,
Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath
FROM         ReportSchedule
INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID






Or More Advance ...


  SELECT    
 Schedule.ScheduleID AS AgentJobName,
 Subscriptions.Description AS SubscriptionName,
  Subscriptions.DeliveryExtension AS DeliveryExt,
  [Catalog].Name AS ReportName,
  [Catalog].Path AS ReportPath,
 SUBSTRING(ExtensionSettings, LEN('TO') + CHARINDEX('TO', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('TO', ExtensionSettings) + 1) - (LEN('TO') + CHARINDEX('TO', ExtensionSettings))) AS 'To Email recipient List',
CASE CHARINDEX('CC', ExtensionSettings) WHEN 0 THEN
  ''
 ELSE
   SUBSTRING(ExtensionSettings, LEN('CC') + CHARINDEX('CC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('CC', ExtensionSettings) + 1) - (LEN('CC') + CHARINDEX('CC', ExtensionSettings)))
END AS 'CC Email recipient List',
CASE CHARINDEX('BCC', ExtensionSettings) WHEN 0 THEN
 ''
 ELSE
 SUBSTRING(ExtensionSettings, LEN('BCC') + CHARINDEX('BCC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('BCC', ExtensionSettings) + 1) - (LEN('BCC') + CHARINDEX('BCC', ExtensionSettings)))

END AS 'BCC Email recipient List'

FROM        
  ReportSchedule
INNER JOIN Schedule  ON   ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions  ON   ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]  ON   ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE
  Subscriptions.DeliveryExtension = 'Report Server Email'

Remove Milisecond from DateTime value.

I like to use following two ways to get rid of Milisecond from DateTime value:

Select GetDate()


1. Select CONVERT(Varchar,GetDate(), 120)

2. Select DateAdd(MS, -DatePart(MS,GetDate()), GetDate())

How to get Computed column definition ?

It is easy..using sp_Helptext system stored procedure we can get computed column definition .

Create Table ComputedColumn
(
AmountA INT,
AmountB INT,
AmountC AS (ISNULL(AmountA,0) + IsNull(AmountB,0))
)

INSERT INTO ComputedColumn
SELECT 1, 2

SELECT * FROM ComputedColumn

sp_Helptext ComputedColumn , AmountC 
 
OUTPUT
----------
(isnull([AmountA],(0))+isnull([AmountB],(0)))