Two households, both alike in dignity,

In fair Verona, where we lay our scene,

From ancient grudge break to new mutiny,

Where civil blood makes civil hands unclean.

          ~ Romeo and Juliet, William Shakespeare

Those Montagues and Capulets don’t do so well with the sharing thing. They fight. A lot. This causes a lot of trouble in Verona, often resulting in various Montagues and Capulets winding up in jail.

Let’s suppose that Prince Escalus, essentially the mayor of Verona, decides to commission a report detailing what members of each family have been jailed. Escalus has decreed that an Excel attachment of this report is to be distributed to each of the families via subscription from SQL Server 2008 Reporting Services (Standard Edition). Each report will contain only the information for that family and the file name of the Excel attachment must include the name of the family as well. The definition of each report is identical, merely the records and report name (and thus the Excel attachment) must be personalized for each family. This is based on a solution I implemented for a client a while back. I think this solution is fine when there are not a lot of different Family values. For example, if you have 100 different families, then perhaps creating 100 different linked reports and their subscriptions may not be ideal. In such a scenario, you may want to create an SSIS solution that will perform the exports and emails using package variables for the personalization of each ( have not tried it but it seems like it should work). But for now, I will instead recount the process I used that worked well for the client. If you have solved this a different way, I would love to see it and possibly even shamelessly steal it should I encounter this situation again. 🙂

One could accomplish the report personalization goal by creating two reports, one for each family. This would get the job done. However, from a manageability standpoint, this is not ideal. Suppose there were 20 families involved? Do you really want to maintain 20 all but identical reports, propagating changes to all 20 reports when they are required? Instead, why not create a single report with a Family parameter? That would take care of the manageability issue since you would have only one report definition to maintain. However, this would not solve the personalization goal. This is because the name of an Excel attachment exported from SSRS defaults to the the name of the report itself. For sooth!

Enter Linked Reports! (MSDN How To: Create A Linked Report) This link is for SQL 2008 but 2005 linked reports are essentially the same as far as I have scene. And it was actually in SQL 2005 that I implemented this solution for a client (not Prince Escalus) and it behaved the same in 2008 when I did it for this post.

A linked report is one that is based on the report definition of another report. Changes to the definition of the base report are propagated to all linked reports. But the linked reports have their own name (Aha!) and subscriptions (Aha! again), as well as some other properties. Zounds!

Let’s create our table. I am keeping this in the Adventureworks database for simplicity, but creating my own schema. I added the IF EXISTS for the schema since I am SURE you implement all of the code in my posts and therefore already have a schema named markvsql in your copy of the Adventureworks database. 🙂

USE Adventureworks

GO

--Create the markvsql schema if it does not already exist

IF NOT EXISTS

(

    SELECT * FROM sys.schemas WHERE [name] = 'markvsql'

)

EXEC sp_executesql N'CREATE SCHEMA markvsql'

GO

CREATE TABLE markvsql.VeronaJailing

(

      PrisonerName varchar(10)

    , Family varchar(10)

)

GO

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Romeo','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Benvolio','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Balthasar','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Juliet','Capulet')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Tybalt','Capulet')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('The Nurse','Capulet')

Now that we have some data, we will create the stored procedure to return our report dataset.

CREATE PROC markvsql.GetVeronaPrisoners

      @Family varchar(10)

AS

SELECT

      vj.PrisonerName

FROM markvsql.VeronaJailing vj

WHERE vj.Family = @Family

GO

Now let’s give our fabulous sproc a spin around the block.

Let’s meet our Capulets <<applause>>:

EXEC markvsql.GetVeronaPrisoners @Family = 'Capulet'

GO

/* Capulet RESULTS:

PrisonerName

------------

Juliet

Tybalt

The Nurse

*/

And our Montagues <<applause>>:

EXEC markvsql.GetVeronaPrisoners @Family = 'Montague'

GO

/* Montague RESULTS:

PrisonerName

------------

Romeo

Benvolio

Balthasar

*/

And now it’s time to play the Family Feud! …not really. That just seemed to fit here.

Now that the database code is all set to go, let’s take a look at our report, the Verona Jailing Report. I kept it very simple. It just pulls the list of prisoners for the Family selected.

For the Capulets:

For the Montagues:

I set up a subscription to the Verona Jailing Report which passes in Capulet for the Family parameter and emails an Excel copy of the report. Here is what the email looks like:

Notice that the name of the Excel file (and the report name in the subject line) are just the generic Verona Jailing Report. Until you open the attachment, there is no indication which Family this report is for. You could hard code the family name in the subscription so that it appears in the Subject line, but that still would not meet the requirement of having the Family name part of the Excel file name. Verily, ‘tis true.

The contents of the above Verona Jailing Report look like this:

Notice that the worksheet name has also taken on the name of the report. I just used an expression to bring the value of the Family parameters into textbox reading Verona Jailing Report: Capulet.

We therefore create a linked report derived from Verona Jailing Report called Verona Jailing Report – Capulet. We then modify the properties of the Family parameter of the Capulet report in Report Manger so that the user is no longer prompted and set its default value to Capulet. The parameter properties are shown below.

We then create a subscription for the Verona Jailing Report – Capulet report just like the one we created for the base report above. Behold:

Via our linked report, we have killed two of the birds with one of the stones. Both the subject and the Excel attachment now indicate this report is for the Capulet family. Hazzah!

DISCLAIMER: No birds were harmed during the creation of the post.

The contents of the Verona Jailing Report – Capulet file look like this:

We then create a linked report and subscription for the Montague family following the same steps and we have one happy Prince Escalus. And as a bonus, he LOVES the fact that the Montagues and Capulets are unknowingly sharing the report from which their own reports are derived.

And the Montagues and Capulets lived happily ever after….um….wait….

<theater soapbox rant>

While I am here, I need to get this off my chest. One famous line often quoted from Romeo and Juliet is Juliet asking “O Romeo, Romeo, wherefore art thou Romeo?” I just have to make it clear that Juliet is most certainly NOT asking where Romeo is (“Wherefore” means “Why”). Nor is she calling out to a voice-activated tracking system for the homing device she discretely planted on his lapel. Rather, she is calling attention to the lamentable tragedy that she, a Capulet, just fell for Romeo, a Montague, in a world where the Capulets and Montagues are essentially at war. Alas, poor Juliet, if only one of you could be from a different house! It makes more sense when you include more than just the one line:

O Romeo, Romeo, wherefore are thou Romeo?

Deny they father and refuse thy name.

Or if thou wilt not, be but sworn my love

And I’ll no longer be a Capulet.

          ~Romeo and Juliet, William Shakespeare

</theater soapbox rant>

NOTE: I have uploaded the rdl for the my report.

Verona Jailing Report