Pages

Monday, 25 October 2010

Subscriptions SQL query

Due to the fact the reporting services allows you to query the tables you can easily write sql which will allow you to return data from the reportserver database.

The script below specifically looks at the subscriptions table which can be created into a report with filters. It is quite handy when you have hundreds of subscriptions to easily find the one you are looking for.

Also as some developers will chose to create specific report subscriptions rather than use shared schedules it can be difficult to work out when they will be sent.

The way that the fields for the report specific times are stored is bascially
1,2,4,8,16 etc for example for the day of the week field
1 is Sunday, 2 is Monday, 4 is Tuesday, 8 is Wednesday etc

All the fields are coded like this so it may be best to look at all the distinct values and then work out all the coding in your case statements.

The script will work for both reporting services 2005 and 2008 r2

note remember to change the from table it is looking a reportserver_native!

Enjoy!!


select
case when parameters like '' then 'No Parameters' else
parameters end as Para,substring(path,2,50) as path_link,
recipients+' - '+cc+' - '+bcc as AllRecipients,
* from (

SELECT
month as month2,

case
when recurrencetype = 1 then 'One off'
when recurrencetype = 2 then 'Hour'
when recurrencetype = 4 then 'daily'
when recurrencetype = 5 then 'monthly'
when recurrencetype = 6 then 'month Week'

end as Typ,


case when daysofmonth = 1 then '1'
when daysofmonth = 2 then '2'
when daysofmonth = 4 then '3'
when daysofmonth = 8 then '4'
when daysofmonth = 16 then '5'
when daysofmonth = 32 then '6'
when daysofmonth = 64 then '7'
when daysofmonth = 128 then '8'
when daysofmonth = 256 then '9'
when daysofmonth = 512 then '10'
when daysofmonth = 1024 then '11'
when daysofmonth = 2048 then '12'
when daysofmonth = 4096 then '13'
when daysofmonth = 8192 then '14'
when daysofmonth = 16384 then '15'
when daysofmonth = 32768 then '16'
when daysofmonth = 65536 then '17'
when daysofmonth = 131072 then '18'
when daysofmonth = 262144 then '19'
when daysofmonth = 524288 then '20'
when daysofmonth = 1048576 then '21'
when daysofmonth = 2097152 then '22'
when daysofmonth = 4194304 then '23'
when daysofmonth = 8388608 then '24'
when daysofmonth = 16777216 then '25'
when daysofmonth = 33554432 then '26'
when daysofmonth = 67108864 then '27'
when daysofmonth = 134217728 then '28'
when daysofmonth = 268435456 then '29'
when daysofmonth = 536870912 then '30'
when daysofmonth = 1073741824 then '31'
when daysofmonth = 8193 then '1st and 14th day'
end as daysofmonth,

case
when Month = 4095 then 'All Months'
when MONTH = 585 then 'Jan,April,July,October'
when Month = 1 then 'Jan'
when Month = 2 then 'Feb'
when Month = 4 then 'Mar'
when Month = 8 then 'Apr'
when Month = 16 then 'May'
when Month = 32 then 'Jun'
when Month = 64 then 'Jul'
when Month = 128 then 'Aug'
when Month = 256 then 'Sep'
when Month = 512 then 'Oct'
when Month = 1024 then 'Nov'
when Month = 2048 then 'Dec' end as Month,
isnull(MinutesInterval,0) AS MinutesInterval,
case when isnull(MinutesInterval,0) <60 then 0 else minutesinterval/60 end AS Hours, case when daysofweek = 1 then 'Sun' when daysofweek = 2 then 'Mon' when daysofweek = 4 then 'Tue' when daysofweek = 8 then 'Wed' when daysofweek = 16 then 'Thu' when daysofweek = 32 then 'Fri' when daysofweek = 64 then 'Sat' when daysofweek = 62 then 'Mon-Fri' when daysofweek = 10 then 'Mon AND Wed' when daysofweek = 127 then 'All Days' end as Daysofweek, subs.subscriptionid, c.path,c.Name as Report_Name, c.description as Report_Description, --right(,len(u1.username)-len(left(u1.username,12))) , right(u1.username,len(u1.username)-len(left(u1.username,12))) as Createdby, subs.Description,subs.laststatus,subs.lastruntime, right(u.username,len(u.username)-len(left(u.username,12))) as Modifiedby, subs.modifieddate, parameters, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']') as varchar(1000)), '', ''), '', '') + '; ' as Recipients, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''CC'']') as varchar(1000)), '', ''), '', '') + '; ' as CC, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''BCC'']') as varchar(1000)), '', ''), '', '') + '; ' as BCC, case when s1.eventtype <>'SharedSchedule' then 'ReportSpecific' else s1.Name end as Schedule_Name,
s1.startdate,s1.nextruntime,s1.eventtype,subs.description as [Subscription Description]
FROM
ReportServer_native.dbo.Subscriptions subs with(nolock)
join catalog c on c.itemid = subs.report_oid
join users u on u.userid = subs.modifiedbyid
join users u1 on u1.userid = subs.ownerid
join reportschedule rs on rs.subscriptionid = subs.subscriptionid
join schedule s1 on s1.scheduleid = rs.scheduleid
) as D

4 comments:

  1. Hi. Do you know where you found the details that allowed you to create this? I am trying to figure out why a scheduled report executed on 1/1/2012 when it's schdule is for the 1st of May. In this table, under "Month" it has 16, which would make sense if month was done based on 1,2,4,8,16 instead of 1,2,3,4,5.

    If you have any thoughts on the matter, I'd be interested to know. cmay AT walshgroup DOT com

    ReplyDelete
  2. Hi, I found all this info on the internet and posted it on my blog so wouldn't have to search for it every time I wanted to look at it.
    For the report that you have scheduled for the 1st of May I would check the description of the schedule in report manager first and then look at your query. The query above doesn't account for everything its just a starter.

    ReplyDelete
  3. Hey,

    Thanks for this. Thought I'd share this bitwise function to avoid larger case statements.

    SELECT
    A.DaysOfWeek
    , A.DaysOfWeek & 1 As 'Sunday'
    , A.DaysOfWeek & 2 As 'Monday'
    , A.DaysOfWeek & 4 As 'Tuesday'
    , A.DaysOfWeek & 8 As 'Wednesday'
    , A.DaysOfWeek & 16 As 'Thursday'
    , A.DaysOfWeek & 32 As 'Friday'
    , A.DaysOfWeek & 64 As 'Saturday'
    FROM [ReportServer].[dbo].[Schedule] A

    ReplyDelete
    Replies
    1. Thanks, I didn't know you could do this, great tip. :-)

      Delete