Month: January 2010

Linked Reports: Getting the Montagues and Capulets To Share

By on

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:

image

For the Montagues:

image

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:

image

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:

image

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.

image

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

image

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:

image

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

Jinkies! Passing MultiValue SSRS Parameters To Stored Procedures

By on • ( 8 Comments )

SQL Server Reporting Services provides a great way to allows report users to select more than one value from the available values list in parameters, creating a multivalue parameter.

SSRS dynamically creates a comma separated list of the values selected by the user to pass in to the data source.

Great.

It also dynamically creates the Select All option at the top of the available values list.

Also great.

And, it is really easy to enable this feature.

Also also great.

But this great functionality does have a limitation right out of the box. You cannot pass a multivalue parameter to a stored procedure (see Using Single-Value and Multivalue Parameters).

Ruh roh. Not great.

In the past, I have had the data source sproc return all of the records and then used a filter on the data region to only show the records matching the values selected in the parameter. It got the job done, but returning records you are not going to use is suboptimal, to be sure.

Now, you may be thinking, dear reader, that I am a moron for choosing the above title for this post and then almost right away saying that you cannot do it. Well, provided that your database source is SQL 2005 or later, you actually CAN do it through the beauty of XML. And what is really really cool to me is that the amount of work you must perform to achieve this is miniscule. AND, it does not require much XML expertise, which I must confess, I currently lack. I have seen solutions for this obstacle involving the creation of user-defined functions, but I find this XML solution far more elegant and so easy to implement.

As I often do, I must shout out some thanks to Jason Strate (blog: www.jasonstrate.com, twitter: @stratesql) for showing me this.

You may be asking why I am so fond of having reporting data sources be stored procedures instead of just writing the query right into the report. While that is not truly the scope of this post, I will say that I really like having data access code in the database. Also, the security implications are great, since with sprocs, users need only Execute permission on the sproc and none against the underlying tables. There are other reasons, too, but those two are enough for now.

Setting Up The Data

Let’s start off by creating a table to house our data. I am using AdventureWorks (to make it easy) but creating my own schema (to keep things clean). NOTE: I looked but could not find any ScoobyDoo references in AdventureWorks. 

USE AdventureWorks

GO

 

CREATE SCHEMA MarkVSQL

GO

 

CREATE TABLE MarkVSQL.ScoobyDooCharacter

(

      CharacterID int not null IDENTITY(1,1)

    , CharacterName varchar(10) not null

)

 

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Fred')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Daphne')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Velma')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Shaggy')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Scooby Doo')

NOTE: You may be thinking that Scrappy Doo is conspicuously absent from my list. There is a good reason for that. I can’t stand Scrappy Doo. He drives me nuts. I would rather bite down on a metal fork really hard with my front teeth than watch Scrappy Doo. </Scrappy Doo>

Creating Available Values List

Let’s create the stored procedure that will supply our Character parameter with available values. This is where we start to really have some fun. 

CREATE PROCEDURE MarkVSQL.GetMysteryIncMemberList

 

AS

 

SELECT

      CharacterName

    , '<Character ID="'

        + CONVERT(varchar,CharacterID)

        + '"/>' AS CharacterIDXML

FROM MarkVSQL.ScoobyDooCharacter

This stored procedure returns the CharacterName along with the CharacterID field that has been modified to return the CharacterID value formatted as an xml node instead of just the int. Here is the fabulous output of our MarkVSQL.GetMysteryIncMemberList sproc.

CharacterName CharacterIDXML

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

Fred          <Character ID="1"/>

Daphne        <Character ID="2"/>

Velma         <Character ID="3"/>

Shaggy        <Character ID="4"/>

Scooby Doo    <Character ID="5"/>

Creating Data Source Sproc 

Now we create the stored procedure that will supply our report with data.

CREATE PROCEDURE MarkVSQL.GetMysteryIncMembers

 

    @CharacterID varchar(max)

    

AS

 

DECLARE @CharacterIDXML xml

 

SET @CharacterIDXML = REPLACE(@CharacterID,',','')

 

SELECT

      c.CharacterName

FROM MarkVSQL.ScoobyDooCharacter c

WHERE c.CharacterID IN

    (SELECT c.value('@ID','int') FROM @CharacterIDXML.nodes('//Character') AS x(c))

There are several things to note about this proc:

  • The parameter is a varchar(max) to allow as much space as we can for the string of comma separated XML nodes that SSRS will pass in.
  • We then declare an XML typed variable, @CharacterIDXML.
  • We remove the commas (which SSRS automatically adds as value separators) from the contents of the @CharacterID parameter and put the resulting list of XML nodes in the @CharacterIDXML variable.
  • Our WHERE clause uses the XML “nodes” method to shred out the CharacterID values from the XML.

Let’s run our MarkVSQL.GetMysteryIncMembers sproc with various parameters as they would be passed in from SSRS.

In this example, we just want to return Fred.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>'

We get the following results:

CharacterName

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

Fred

 

(1 row(s) affected)

Now let’s get Fred and Daphne together. I think it is no secret that there is a spark between them.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>'

Here they are.

CharacterName

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

Fred

Daphne

 

(2 row(s) affected)

Now let’s get all of those meddling kids and their dog.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>,<Character ID="3"/>,<Character ID="4"/>,<Character ID="5"/>'

Here they are, in all of their mystery solving glory.

CharacterName

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

Fred

Daphne

Velma

Shaggy

Scooby Doo

 

(5 row(s) affected)

The configuration of the parameter in SSRS is nothing out of the ordinary, so I don’t feel it is necessary to go into the step by step process here. You can go here if you need more guidance on the SSRS piece, you can go here: Adding Parameters to Your Report. The article in this hyperlink is for 2008 but includes a hyperlink to the 2005 version as well. The configuration goals are the same, but interface differences between 2005 and 2008 make the exact steps a little different.

You just configure the @CharacterID parameter of the report to do the following:

  • Allow multiple values
  • Get its available values from the MarkVSQL.GetMysteryIncMemberList sproc.
  • Use the CharacterName field as the “Label” field so that the user is presented with the list of names.
  • Use the CharacterIDXML field as the “Value” field so that our XML nodes in a comma separated string are passed to the stored MarkVSQL.GetMysteryIncMembers sproc, just like in our examples above.

That’s really all there is to it. Roh boy!