A Quick Note About Data Driven Subscriptions

I recently changed jobs and have much more to do with maintaining SSRS native mode servers and configuring Subscriptions -- both Data Driven and regular.

We will be cleaning up the Data Driven subscriptions with the goal of making them more consistent, easier to deploy, and more conducive to being updated. One idea, that will be expanded in later posts, is creating a function that can deliver values for Subject, To, Body, etc.

A question quickly arose. Can the provided parameters, @ReportName and @ExecutionTime be used with Data Driven subscriptions? The short answer is, Yes!

For example, returning a string from the Data Driven query for Subject, for example, with @ReportName in it, will cause SSRS to substitute the report name. Likewise for @ExecutionTIme.

This should mean a little more direct reuse between subscriptions. 

Distinct Counts with Rollup

A post on SQL Central brought up an interesting question. It pointed out that SQL Server 2005 does not allow a Discount Count to be computed when also using Group by with Rollup, but wondered if it was possible to accomplish the result, anyway. This functionality is available in SQL Server 2008. I came up with a solution that created a derived table with the Rollups and used the Coalesce to provide the proper join. Based on this table:

Drop Table EmployeeLocation
go

Create Table EmployeeLocation(
 RegionCode varchar(2) not null,
 StateCode varchar(2) not null,
 OfficeCode varchar(2) not null, 
 EmployeeID int not null
)
go

Insert EmployeeLocation values ( 'AA', 'NJ', '01', 1 )
Insert EmployeeLocation values ( 'AA', 'NJ', '01', 2 )
Insert EmployeeLocation values ( 'AA', 'NJ', '01', 3 )
Insert EmployeeLocation values ( 'AA', 'NJ', '02', 1 )
Insert EmployeeLocation values ( 'AA', 'PA', '03', 1 )
Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )
Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )
go

Here is the query with a derived table. This could be replaced by a CTE.

Select t1.RegionCode,
  t1.StateCode,
  t1.OfficeCode,
  Count(Distinct t2.EmployeeID )
From (
   Select RegionCode,
     StateCode,
     OfficeCode
   From EmployeeLocation
   Group
   By  RegionCode,
     StateCode,
     OfficeCode
   With Cube
  )t1
Join EmployeeLocation t2
On  1=1
And  Coalesce( t1.RegionCode, t2.RegionCode ) = t2.RegionCode
And  Coalesce( t1.StateCode, t2.StateCode ) = t2.StateCode
And  Coalesce( t1.OfficeCode, t2.OfficeCode ) = t2.OfficeCode
Group
By  t1.RegionCode,
  t1.StateCode,
  t1.OfficeCode
Order
By  RegionCode ,
  StateCode ,
  OfficeCode 

The Coalesce pattern can also be useful when checking a date against an open-ended range, i.e. one where one end is NULL. The original question poster has not replied, which might indicate that this is of limited value.