TryCatchFinally.net Some SQL, some .NET, and whatever else

10Jan/170

Microsoft Connect item for an SSRS pre-report canvas

Current state:

About six months ago, I submitted a connect request for SQL Server (my first one) to suggest an improvement to the SSRS interface - a customizable canvas that's displayed before an SSRS report renders, while parameters are being entered. My issue was that there was information I wanted report users to have (like expected wait time, suggestions for the parameters, the report title and a short description that might help them, etc.) and there was no way to get it to them.

With a giant empty screen staring at them, it seemed like a great place to add that information:

SSRS Report pane current layout

The suggestion:

Ideally, while the user is considering parameters, I'd love to see something like this (ignoring that it's not a super-photogenic report):

proposed-layout

This would be a great place for details about the report that help the user before (or while) they run it, like:

  • The name of the report (not always clearly visible in the URL or browser title bar, especially when you're Sharepoint-integrated. Especially helpful if they've left multiple report tabs open or if report generation fails, they're often left with a white screen and no easy way to tell the tabs apart.
  • A short description of the report, including when you'd use it or any notable caveats.
  • An estimated runtime - this isn't always possible exactly as parameters vary, but even a general estimate (or better yet, an average or a 95% confidence range, pulled from the report server database) would be better than the complete blank they get today.
  • Suggestions/restrictions on the parameters - If a report can't be run for all offices at once, you can say so here. If running it for everybody at once means you can't export to Excel because it's too large, let the user know.
  • A mock-up/screenshot of the report - so they user can see if it's the one they want. They may be looking for a report with a specific chart, and waiting for 3 minutes for each one to render may not be ideal.
  • A link to any other documentation/resources, or related reports, or pretty much anything else.

It wouldn't need to be anything fancy - I'd even settle for text you can format, or some kind of additional panel on the report that you can design, but isn't rendered with the report itself. Access to data (to pull estimated on execution times or dynamic owner/description details, for example) would be nice, but not required - I'd rather have a design-only, no-data canvas than nothing at all.

The plea:

t-sql-tuesdayThanks to the motivation from the folks at Brent Ozar Unlimited, I'm asking for your help in voting for this suggestion (if you think it would be beneficial, of course). As part of T-SQL Tuesday, they've asked people to find a connect request that they'd like to see filled and post about it, so here I am asking humbly for your support. : )

If you like the idea, please vote for it - if you don't, please comment and let me know why not (I'm always open to understanding the opposition). There are a ton of great Connect items out there and this is only one, but I think it would help out (especially for our longer-running reports).

Thanks for your help!

Direct URL for the Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2809098/ability-to-customize-report-splash-screen-during-parameter-submission

Side note:

I've attached the two images (current/proposed) to my connect item three different times and they're still not showing up there - if anybody knows how to get those images published, people can see what I'm talking about when I describe my suggestion! : )

2Jan/150

Removing expired/unused SSRS subscriptions

SQL Reporting Services doesn't do a very good job keeping the SQL Agent clean by removing expired or otherwise unusable subscriptions from the job list. To deal with this, we created a script that pulls some details about these old subscriptions, including the report responsible, the last run time and status, and the user who originally scheduled it. If you notice your SQL Agent job list getting excessively long, you can use this query to identify the culprit reports and owners, and then either notify them or remove the old subscriptions manually yourself (run this on the server with your SSRS databases):

  select c.Name as ReportName,
         s.EventType,
         s.Description as SubscriptionDescription,
         s.LastStatus as LastSubscriptionStatus,
         s.LastRunTime SubscriptionLastRunTime,
         case
            when recurrencetype = 1 then 'One Time'
            when recurrencetype = 2 then 'Hourly'
            when recurrencetype = 4 then 'Daily'
            when recurrencetype = 5 then 'Monthly' 
            when recurrencetype = 6 then 'Month Week' 
            else 'Other'
         end as RecurranceType,
         s.DeliveryExtension,
         u.UserName as SubscriptionSetUpBy,
         s.ModifiedDate as SubscriptionLastModifiedDate
    from [ReportServer].[dbo].[Subscriptions] s
    join [ReportServer].[dbo].[Catalog] c
      on c.ItemID = s.Report_OID
    join [ReportServer].[dbo].[Users] u
      on u.UserID = s.OwnerID
    join [ReportServer].[dbo].[reportschedule] rs
      on c.itemid = rs.reportid 
     and s.subscriptionid = rs.subscriptionid
    join [ReportServer].[dbo].[schedule] sch
      on rs.scheduleid = sch.scheduleid
   where s.EventType <> 'RefreshCache'
     and s.LastRunTime < dateadd(m, -3, getdate())
order by c.name

There are a number of similar scripts out there that pull much of this information together, but there wasn't one that collected all the details we were looking for in one place. From here, you can deal with the subscriptions as you see fit.

Note that you can just remove the old subscriptions by brute force if you'd prefer, and SSRS will clean up the orphaned SQL jobs, but I've preferred to review the list and notify users as we've never had too much volume to deal with. If you want to just delete them straight away, you can do so here:

DELETE ReportServer.dbo.Subscriptions
WHERE InactiveFlags != 0
	OR LastRunTime < dateadd(m, -3, getdate())