Reporting Services

Reporting Services (SSRS) is a tool that permits you to create and deliver feature rich reports. The reports can be delivered in various formats and can even be scheduled to be delivered at various times. I even recently wrote about creating more advanced custom schedules.

With the ability to create content delivery schedules, or to subscribe to report content delivery, this imposes a requirement to also know when the various reports are scheduled to be delivered.

SSRS provides the means to be able to review the scheduled reports (subscriptions). That means is held within the ReportServer database. This article will help to uncover one of the sources of this scheduling information within the ReportServer database.

Review Schedules

When looking into the database for SSRS, I can see there are different means to be able to review the report schedules. This article is going to cover just one of those methods. And if I am going to be entirely up front about this method, I don’t like it and I recommend that it not be use.

I can hear the moans now. “If you don’t like it, then why show it to us?” Well, that is a very good question and there is a very good reason for this decision. A lesson I learned a long time ago is sometimes you need to learn the hard way, or less desirable way, to do various things. One of my favorite Calculus teachers from years ago drilled this into my head over and over again. Why? Well, there are three good reasons that come to mind: a) it makes the more desirable method seem much easier, b) it helps you to appreciate the more desirable method all that much more, and c) because if all else fails, you will have another method to fall back to just in case.

Less Disérables

The least desirable method (at least of the methods I will share) is to parse XML from a field stored in the ReportServer database. If I look into the Schedule table within the RepotServer database, I will find this column called MatchData. Up front, this field is not very intuitively named. I would not think this field actually represented the schedule, but it actually does.

Before we start diving into parsing XML, we need an example of what this XML may look like. The following will provide that very example that we need.

<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">2017-03-27T02:00:00.000-06:00</StartDateTime>
  <MonthlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
    <Days>1</Days>
    <MonthsOfYear>
      <January>true</January>
      <February>true</February>
      <March>true</March>
      <April>true</April>
      <May>true</May>
      <June>true</June>
      <July>true</July>
      <August>true</August>
      <September>true</September>
      <October>true</October>
      <November>true</November>
      <December>true</December>
    </MonthsOfYear>
  </MonthlyRecurrence>
<MinuteRecurrence xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
<MinutesInterval>75</MinutesInterval>
</MinuteRecurrence>
<WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Sunday>true</Sunday>
<Tuesday>true</Tuesday>
<Thursday>true</Thursday>
<Friday>true</Friday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>

Are your gears grinding yet? The XML is not terribly difficult to follow. I am sure you have realized the problem from this format at this point. If I query this to make it human readable in a tabular format (you know DBA format), I will end up with a really wide table that is pretty ugly to look at (unless I get super creative to combine fields etc).

Let’s take a look at the query to parse something like the preceding XML example.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer' AS rs)
, getxml AS (
SELECT SUB.SubscriptionID
, ReportName = CAT.[Name]
, ExtensionSettings = CAST(SUB.ExtensionSettings AS XML)
, ReportSchedule = CAST(SUB.MatchData AS XML)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID 
  --WHERE CONVERT(CHAR(8), SUB.LastRunTime, 112  ) > '20170101' 
)
SELECT SubscriptionID
, ReportName
, [StartHour] = SUBSTRING(( c.nd.value('(rs:StartDateTime/text())[1]',
'VARCHAR(500)') ), 12, 2)
, [StartMin] = SUBSTRING(( c.nd.value('(rs:StartDateTime/text())[1]',
'VARCHAR(500)') ), 15, 2)
, [Days] = c.nd.value('(rs:MonthlyRecurrence/rs:Days/text())[1]', 'INT')
, [January] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:January/text())[1]',
'BIT')
, [February] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:February/text())[1]',
'BIT')
, [March] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:March/text())[1]',
'BIT')
, [April] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:April/text())[1]',
'BIT')
, [May] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:May/text())[1]',
'BIT')
, [June] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:June/text())[1]',
'BIT')
, [July] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:July/text())[1]',
'BIT')
, [August] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:August/text())[1]',
'BIT')
, [September] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:September/text())[1]',
'BIT')
, [October] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:October/text())[1]',
'BIT')
, [November] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:November/text())[1]',
'BIT')
, [December] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:December/text())[1]',
'BIT')
, [Sunday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Sunday/text())[1]',
'BIT')
, [Monday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Monday/text())[1]',
'BIT')
, [Tuesday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Tuesday/text())[1]',
'BIT')
, [Wednesday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Wednesday/text())[1]',
'BIT')
, [Thursday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Thursday/text())[1]',
'BIT')
, [Friday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Friday/text())[1]',
'BIT')
, [Saturday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Saturday/text())[1]',
'BIT')
, [WeeksInterval] = c.nd.value('(rs:WeeklyRecurrence/rs:WeeksInterval/text())[1]',
'INT')
, [MinuteRecurrence] = c.nd.value('(rs:MinuteRecurrence/rs:MinutesInterval/text())[1]',
'INT')
FROM getxml
CROSS APPLY ReportSchedule.nodes('//ScheduleDefinition') c ( nd )
ORDER BY SubscriptionID
, ReportName;

And there we have that ugly query to produce a really wide ugly result set. The query is not difficult to write. It’s just extremely repetitive. In similar fashion, the results are very repetitive. This makes, in my eyes, this particular method less desirable.

I haven’t even gotten to the part about the shortcoming in scheduling reports through SSRS that I wrote about recently – here. In that article I discussed a workaround to overcome the SSRS scheduling options. If you employ methods such as I discussed there, then this query will never fully cover the scheduling related to your reports. Because of that, I will be discussing the better solution in the next article.

Recap

SSRS provides built-in mechanisms to help report on the scheduled reports that have been deployed. While parsing the XML is less desirable than what I will be sharing in the near future, it is better than doing nothing at all. I recommend you start looking into the various report schedules you may already have in your environment. Also, stay tuned for the next article that will better show these schedules.