Tuesday, October 29, 2013

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
update subs
set subs.[Description] = coalesce(ovr.[Description],subs.[Description])
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!

No comments:

Post a Comment