SSRS – Report – Job Link

SELECT 

      c.Name AS ReportName

      , rs.ScheduleID AS JOB_NAME

      , s.[Description]

      , s.LastStatus

      , s.LastRunTime

FROM 

      ReportServer..[Catalog] c 

      JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID 

      JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID

      AND rs.SubscriptionID = s.SubscriptionID

Cheers!
Sanjay

TSQL script to retreive last two sale date for each item class with transposed columns.

Today received a request to set up a flash report as follows:

FlashReport

The report is a flash report, meaning it  should popup on demand when requested.  My initial approach was to group by item class and get the max date in the first run and then add a sub query to do same excluding the max date, which meant there would be two scans, obviously  with large number of records, it was very slow.   It was evident that the double group by was not the solution as there were two scans, the solution should be having the ability to select top two records for a virtual group.  The final script which did the task is as follows:

SELECT itmclscd, 
       [1] AS LastPurchaseDate, 
       [2] AS SecondLastPurchaseDate 
FROM   (SELECT docdate, 
               rownumber, 
               itmclscd 
        FROM   (SELECT Row_number() 
                         OVER ( 
                           partition BY custnmbr, itmclscd 
                           ORDER BY docdate DESC) AS rownumber 
                       itmclscd, 
                       custnmbr, 
                       docdate 
                FROM   (SELECT dbo.iv00101.itmclscd, 
                               dbo.sop30200.docdate, 
                               dbo.sop30200.custnmbr 
                        FROM   dbo.sop30300 WITH (nolock) 
                               INNER JOIN dbo.iv00101 WITH (nolock) 
                                       ON dbo.sop30300.itemnmbr = 
                                          dbo.iv00101.itemnmbr 
                               INNER JOIN dbo.sop30200 WITH (nolock) 
                                       ON dbo.sop30300.soptype = 
                                          dbo.sop30200.soptype 
                                          AND dbo.sop30300.sopnumbe = 
                                              dbo.sop30200.sopnumbe 
                        WHERE  dbo.sop30200.voidstts = 0 
                               AND dbo.sop30200.soptype = 3 
                               AND Year(docdate) > @Year
                               AND dbo.sop30200.custnmbr = @CUSTNMBR 
                        GROUP  BY dbo.iv00101.itmclscd, 
                                  dbo.sop30200.docdate, 
                                  dbo.sop30200.custnmbr) Level1) Level2 
        WHERE  rownumber <=2) p 
       PIVOT ( Max(docdate) 
             FOR rownumber IN ([1], 
                               [2]) ) AS level3 
ORDER  BY itmclscd

The key section of the code was:

1) Level 1 group by : Getting the data grouped by itmclscd,  docdate, custnmbr which creates a unique combination for the group by fields.

2) Level 2 (select) :
SELECT Row_number()
OVER (
partition BY custnmbr, itmclscd
ORDER BY docdate DESC
)   AS ‘rownumber’

This section of the TSQL assigns row numbers for each block for  custnmbr,itmclscd  which is ordered by docdate.

3)Level 3 where clause : where rownumber <=2 ensure that only top 2 records are selected

and finally setting up the pivot section to transpose the data at level 3.

It was fun setting up the query, which is much faster than the group by with sub query approach.

Cheers!
Sanjay