Custom Subscription Description SSRS

One major limitation in SSRS is the inability to set the description property of a normal subscription.  Data-driven subscriptions allow for custom descriptions, but normal subscriptions do not.  Here's an image of what I'm talking about.

The description property is auto-generated for you.  In the example above, this report has 3 different subscriptions, each one with a different parameter setting.  Granted, this subscription could be turned into a data-driven subscription if none of the parameters are multi-select parameters.  In the case where the report requires multi-select parameters, data-driven subscriptions will not work.  Managing these subscriptions is a huge pain because the descriptions are so useless.

Background Story

Not long ago, I worked for a company that managed a daily regional report to managers through a subscription.  The parameter in the report was a multi-select State parameter that allowed one report to be used for all regions in the US.  For example, the Western region had California, Oregon, Washington, and Hawaii selected.  The Mid-Western region had Nevada, Utah, Idaho, and Wyoming, etc.
Each subscription emailed the report to the regional management team with the appropriate states selected in the parameter (because the parameter was a multi-select, using a data-driven subscription would not work!). After getting the 12 or so unique subscriptions set up on the report, the subscription management page looked a lot like the one above, with completely useless descriptions.  It wasn't long before I would get calls saying, "Please remove Roger from the Mid-West Region 2 report and add Ann".  How did I locate the Mid-West Region 2 subscription?  By going through the list and opening up each subscription detail and looking at the subject line.  I was at least smart enough to put the region name in the subject line of the email delivery options.  Needless to say, it wasn't long before I reached the end of my patience in this extremely cumbersome task of managing these subscriptions.

The Solution

The solution I came up with was to hijack the Subscriptions table in the ReportServer database.  I needed to over write my own subscription names in the [Description] field of that table.

Step 1

Create an override table with the names of the custom subscription names you want.  I chose to create my table in the ReportServer database.  Here's my code:

create table tblSubscriptionDescriptionOverride

(

SubscriptionID uniqueidentifier not null primary key

,[Description] nvarchar(512) not null

)

Step 2

Populate the override table with the SubscriptionID and custom descriptions.  To find the SubscriptionID, I made a simple modification to the Subscription, then immediatly ran this code on the ReportServer database.

select *  from [ReportServer].[dbo].[Subscriptions]

  order by ModifiedDate desc

This query will return all the subscriptions in the order in which they were last modified, so the subscription that was just changed will be on top, as shown below.

 Once I had the SubscriptionID, I used it to populate the insert statement into my override table as follows:

insert into tblSubscriptionDescriptionOverride (SubscriptionID,[Description])

values ('4B1BE0ED-72C3-45AB-B366-B2EEB400B7BF',N'North East Region')

Step 3

Create a trigger on the Subscriptions table so that any future updates to the subscription will always re-populate the custom description.  In a nutshell, this trigger will take the subscription being updated, compare it to any values in the override table and use the value in the override table as its description instead of the auto-generated one.  If no values are in the override table for that subscription, then it will use the default, auto-generated description as normal.

create trigger trgCustomSubscriptionDescriptionUpdate on dbo.Subscriptions

after update

as

update subs

set subs.[Description] = coalesce(ovr.[Description],subs.[Description])

from

Subscriptions subs

join inserted i

on subs.SubscriptionID = i.SubscriptionID

left join tblSubscriptionDescriptionOverride ovr

on subs.SubscriptionID = ovr.SubscriptionID

Step 4

Test it out to make sure it is working.  Open up each subscription and save it again.  You should see that the Description property is now being populated with the values in the override table!

SSRS 2012 Custom Indicator

In this demonstration, I will show how I used custom indicator graphics to create this report.

I'm using SSRS 2012 in SharePoint 2010, however, I believe the content will work with 2008 R2 and above versions of Reporting Services either in native or SharePoint mode.

Background Story

A week or two ago, I was given the assignment to use SSRS to mimic a report the executive management team used in Roambi.  Roambi is a mobile app that displays data in very rich, graphical ways.  All the executives use this report on their iPads and they love it.  Since Roambi only has a mobile platform, the executives cannot access the report on their desktops—hence the reason for me to mimic the report in SSRS.

The Unique Chart Image

Roambi has many more chart types in its interface than SSRS.  One that was used heavily in this report was the horizontal bar chart with a vertical goal.  The images below show what the charts look like.

75% of goal

125% of goal

These two images represent two different states.  The one on the left shows a value that is under target.  The one on the right is slightly above target.  These graphs visually display one metric compared to a goal or target value (think sales vs a sales goal).  If the metric is under the goal, the bar is displayed in red, and if it is at or above the goal, then the bar is green. The closest thing SSRS has to this chart type is a bar chart—however, there is no way (at least one that I could see) to display the vertical goal line, and change its bar color based on a calculation.  The SSRS Bar chart is simply a column chart flipped horizontally.

Realizing that the report will require custom graphics, I went to work using GIMP to create the following images to represent the various scenarios/states (creating these images is a tutorial in an of itself, but here is a link to a great article that helped me in creating these).  By the way, if you like my graphics, you're welcome to download them and use them yourself--or if you want the entire GIMP file that I used to create them, send me an email and I'll be happy to send you my project file.

I created images to map the range from 0-300+% of the goal.  I added the images to my SSRS project. The images folder in my SSRS project looks like this (there are more images in my folder than just these custom graphics).  I labeled all of the custom graph images as "BarToGoal_".

Add the Indicator Control Type

In the cell that I wanted to display the graph, I selected the indicator control type from the report toolbox or from the ribbon if using Report Builder 3.0

Visual Studio--Tool box

Report Builder 3.0

Configure the Indicator

Once the Indicator is added, it pops up a window to select an indicator type.  It doesn't matter which one is chosen since it will be customized.  For this example, I selected the "5 Quarters" one.

I open up the configuration options by right clicking on the indicator and selecting "Indicator Properties".

When the Indicator properties window opens, I select the "Values and States" option from the left-hand menu of the properties window.

Set the Value

In my example I want to compare one field to another.  I open up the value box and type in my expression. A simple example could be something like =Fields!SalesDollars.Value/Fields!SalesGoal.Value

Set the Measurement Unit

I change the "States Measurement Unit" drop down to "Numeric".

At this point, my properties window looks like this:

Set the Image

In this example, the indicator has 5 defaulted images and values to use. I want to override those with my own images and values.  I click the drop down in the Icon column and select "Image".

The image source is "Embedded" and the name of the image I want is BarToGoal_10.  I set the Start value to 0.0 and the End value to 0.10. What this means is that if the value of my metric is between 0 and 10% of the goal, then this image will be displayed.

I continue to configure the remaining indicator states, adding additional ones as needed.

Note:  I made sure to include an indicator for all values ranges.  If the value evaluates to something not within a specified indicator Start and End value, no indicator will display.

That is how to configure custom indicator graphics in an SSRS 2012 report.  You may also find Microsoft's documentation helpful.

Introduction

Hello,

My name is Jayson Seaver.  I'm a BI (Business Intelligence) developer.  I use the Microsoft BI Stack, and I've done quite a bit of web-searching in my career to help me figure out what I'm doing.  I appreciate how well we are able to communicate with one another through blogs, forums, and articles.  Much of what I call "experience" has come from reading some stranger's blog or forum post--and then a lot of trial and error.  In that spirit, I wish to now post my own thoughts and knowledge in the things that I'm learning.  My hope is that someone may find my articles useful.  In addition, writing my thoughts down, in a way to teach someone else, has the greatest impact on my ability to learn and retain new things. Here's to my new BI Blog!