SSRS Subscription Administration – Part 2

Subscriptions Report

We have some query results courtesy of the query we looked at in Part 1, but some of those columns aren’t in the most readable or presentable format. Enter an SSRS report built via Report Builder/BIDS/SSDT.  I’m going to assume that if you’re an SSRS admin, you know your way around building a basic SSRS report, so we’ll keep these instructions high level.

I’ve used these techniques against both a SQL 2012 SSRS native mode instance and aSQL 2012 instance integrated into SharePoint 2010, so I’ll try to point out where there are differences. I’m reasonably confident these will work with SQL Server 2005/2008/2008 R2 native mode instances, but can’t say for certain on integrated mode instances before SQL 2012.

1. Create your new report and use the query from Part 1 of the series as your dataset.
2. Plop a nice table on your report using the dataset you created in step 1. Style as you see fit.
3. Create a Text parameter named @pFolderName , with the Prompt text of your choice.
4. Modify your report dataset query’s WHERE clause to incorporate your parameter. For example:
   WHERE c.path like '/' + @pFolderName + '%'

Special notes for Integrated SSRS instances:

• You may wish to name your parameter @pSiteName, as you’re publishing reports to sites rather than folders.
• If you want to trim the SharePoint site GUID off of the report’s path (because it’s really just ugly and uninformative), use this modification to your query. It’s not fancy, but it does clean things up.
  RIGHT(c.path, Len(c.path)-39) as ReportPath

Let’s work with column visibility a bit so we show our Parameter columns only when they have something to show us for all the reports. If you’re looking at subscriptions for a folder of simple, parameter-free reports, there’s no need to waste the space.

1. Right-click on the Parameter1 column and choose Column Visibility
2. Use this Expression for the “Show or Hide based on expression” value=IsNothing(Fields!Parameter1.Value)

3. Repeat as needed for the remaining Parameter columns you’ve chosen.

The audience of this report might be your fellow SSRS admins, or it might be the main users of the reports to help them get a feel for the status of their subscriptions. We should highlight those subscriptions that haven’t been run in over a month, as it might be time for some cleanup work.

1. Highlight your table’s detail row and view its properties
2. Enter an expression like this for the Backcolor property (substituting whatever highlighting color makes you sit up and take notice).=IIF(Fields!RunInLast30Days.Value="Y","No Color","Yellow")

Note: if a subscription has never been run before, it will also show up as highlighted, as it’s also missing a current Last Run Date. Feel free to alter the CASE statement for the dataset to further differentiate between current, old, and never-run subscriptions.

This report can only display so much information about a subscription. For example, you won’t be able to see just what a “Custom” schedule is for a subscription. Wouldn’t it be nice if you could just click and open the subscription itself for more details right from the report? It would indeed – time to leverage the textbox Action property.

Note: this URL expression works for Native Mode SSRS only! I have not yet figured out the right kind of hack to get this to work for SSRS Integrated Mode

1. Add a column to your report titled “Link” with the detail text “Edit”
2. Right-click on the Edit cell and choose Properties
3. Under Action, use this Expression for the “Go to URL” box. Note that every part of the expression is necessary, even the Redirect URL.="http:// myreportsite.domain.com/Reports/Pages/SubscriptionProperties.aspx?ItemPath=" & Fields!ReportPath.Value & "&IsDataDriven=" & Fields!IsDataDriven.Value & "&SubscriptionID=" & Fields!SubscriptionID.Value & "&RedirectUrl=http://myreportsite.domain.com/Reports/Pages/Report.aspx?ItemPath=" & Fields!ReportPath.Value

I tried leaving off the Redirect URL, but that was a mistake. If you don’t feed a Redirect URL, you will get an error when you click OK or Cancel in the report because Report Manager doesn’t know where to go back to.

The Code

If you have not been successful in convincing your users to embrace sending subscriptions to email groups, it’s likely you have subscriptions that send to a long list of individual email addresses. The Report Manager UI makes you scroll and scroll to review these, and the query output also has all emails mashed together. Time to leverage a little .NET code to make our report present these email addresses more cleanly.

1. Go to the Report’s properties window through your favorite method. Mine is to right-click on the area outside the body of the report and choose “Properties” so I get a nice dialog box.
2. Choose the Code section of the properties. Bad news kids, it’s just a free-form text box, no nice editor so you’ll have to test your code live or via a separate .NET project. I’d love for Microsoft to make this a real editor like in SSIS.
3. Enter the following code in the Code section:

Public Function AddLineBreaks(sDelimList as String)

	If Not String.IsNullOrEmpty(sDelimList) Then

		Dim sList as String() = sDelimList.Split(";")
 		Dim sLineBreakList as String = String.Empty
		Dim sItem as String = String.Empty

		For each sItem in sList
			sLineBreakList = sLineBreakList & sItem & vbCrLf
		Next

		Return sLineBreakList
	Else
		Return sDelimList
	End If
End Function

1. OK your way back to the report.
2. Open the Expression editor for the Send To column (another right-click operation)
3. Enter the following code into the Expression editor: =Code.AddLineBreaks(Fields!SendTo.Value)

As you can see from the syntax, the report code is called via the “Code” object, and each function you enter into that Report Code section lives under it.

1. OK your way out of the Expression Editor
2. Repeat the process for the Send to CC column

Time to Run the Report

You’ve done all this work, time to harvest the fruits of your labors. The screenshot below has been edited to protect production data, so take it as an example and get cracking with creating your own if you want real proof of this report’s usefulness.