Month: August 2013

Technical Article: Report Model (SMDL) Alternatives in SQL Server 2012

It is with tremendous joy that I announce the release of my very first Technical Article! Click the image below to get a copy of your very own.


When I first started in the SQL Server business with Digineer about 6.5 years ago, one of my earliest projects was implementing a SQL Server 2005 Report Model for a client. Lara Rubbelke (Blog|Twitter), who led Digineer’s Information Management (SQL Server) Team at the time, set me up with that project along with some top cover from the mighty Jason Strate (Blog|Twitter). With two great mentors like I was lucky enough to have, that project set the stage for my career in SQL Server. It was during that project that I came to firmly believe in empowering end users in a managed environment. In the next few years, I ended up working on several Report Models for clients and giving well attended Intro To Report Model presentations.

Upon discovering that SQL Server 2012 meant the beginning of the end for Report Models, I was concerned for the folks that had been investing in them since their release. I saw forum posts that expressed frustration at their demise since many organizations had come to rely on them. It was clear that there were some technologies available that were superior to Report Models that could make great replacements in different scenarios. I looked for a source that combined all that information into a single location to help ease the transition of Report Models to other alternatives in the Microsoft BI stack. Since I could not find one, I decided I should go ahead and make one. Behold!

I want to thank Lara and Jason for their mentorship at the beginning of my career and through today. I also want to thank my great Technical Reviewers for this article:

Jin Cho (LinkedIn) – Microsoft

Mike Donnelly (Blog|Twitter) – Digineer

Aaron Drinkwine – Digineer

Chris Fish (Twitter) – Digineer

I need to thank Digineer for their continued support and being an awesome place to call home.

SSRS Cascading Multi-Value Parameters

Not too long ago, there was a question posted to #ssrshelp on Twitter regarding having multi-value parameters in SSRS 2005 that ALSO cascade. While I don’t have access to a SQL 2005 instance anymore, I wanted to try it in 2012 since I had an idea how to accomplish it. My idea worked just fine in 2012 and uses functionality that, if I recall correctly, should be available in 2005 as well.

To start, I want to define these two types of parameters so we are all on the same page.

Multi-Value parameters allow the user to select one, some, or ALL values from a drop-down list. This is native SSRS functionality and it creates the Select ALL option for you. The result(s) of the user selection are placed into a comma separated list of the values chosen, which you can then pass to a dataset or use in some other way.

Cascading parameters allow the value(s) select by one parameter (the Parent, if you will) to control the available values presented in another parameter (the Child, if you will). An example would be a list of US States in one parameter and a list of Counties in the second. The Counties parameter would limit its available values to the Counties that are in the State(s) chosen in the State parameter. With Cascading parameters, the Child parameter is not activated until a valid choice has been made on the Parent parameter.

I pretty much always use stored procedures for my datasets with reporting. Now, natively, passing multi-value parameters to a stored procedure is not supported. I know of two ways around that, both of which I have used at clients.

The XML Method

I wrote about this method myself in this post.

The Table-Value Function Method

This one is demonstrated in a great post by David Leibowitz (Blog|Twitter) here.

I will be using the Table-Value Function method in this post.

We start by creating a quick database and a few objects. Feel free to use an existing database to house these objects instead.

USE master;
      RaceID int IDENTITY(1,1) NOT NULL
    , RaceName varchar(50) NOT NULL
, ('Elves')
, ('Men')
      PersonID int IDENTITY(1,1) NOT NULL
    , RaceID int NOT NULL
    , PersonName varchar(100) NOT NULL
  (1, 'Balin')
, (1, 'Gloin')
, (1, 'Thorin')
, (2, 'Elrond')
, (2, 'Legolas')
, (2, 'Buddy?')
, (3, 'Aragorn')
, (3, 'Boromir')
, (3, 'Faramir')
CREATE FUNCTION dbo.fn_String_To_Table (
   @String VARCHAR(max), /* input string */
   @Delimeter char(1),   /* delimiter */
   @TrimSpace bit )      /* kill whitespace? */
RETURNS @Table TABLE ( [Val] VARCHAR(4000) )
    DECLARE @Val    VARCHAR(4000)
    WHILE LEN(@String) > 0
        SET @Val    = LEFT(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
        SET @String = SUBSTRING(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
             LEN(@String)) + 1, LEN(@String))
  IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
    INSERT INTO @Table ( [Val] )
        VALUES ( @Val )
CREATE PROC dbo.getRace
      RaceID AS Value
    , RaceName AS Label
FROM dbo.Race
CREATE PROC dbo.getPerson
    @RaceList varchar(max)
      p.PersonID AS Value
    , p.PersonName AS Label
FROM dbo.Person p
        SELECT Val
        FROM dbo.fn_String_To_Table (@RaceList,',',1)
CREATE PROC dbo.getPersonReport
      @PersonList varchar(max)
      p.PersonName + ' is of the Race of ' + r.RaceName AS PersonRace
FROM dbo.Person p
INNER JOIN dbo.Race r
ON p.RaceID = r.RaceID
        SELECT Val
        FROM dbo.fn_String_To_Table (@PersonList,',',1)

.csharpcode, .csharpcode pre
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
background-color: #f4f4f4;
width: 100%;
margin: 0em;
.csharpcode .lnum { color: #606060; }


The purpose of each procedure is as follows:

getRace provides a list of the Races available in the dbo.Race table. This will be the parent parameter whose selected values will cascade as a filter for the Person parameter.

getPerson provides a list of the folks in the dbo.Person table based on the list of Races passed in to its @RaceList parameter. This will be the child parameter whose values will be pre-filtered by those selected in the Race parameter.

getPersonReport returns the folks from the Person table based on the list of PersonID values passed in via the @PersonList parameter. This will be the dataset for the report.

Let’s add the three datasets to the report, each using the Shared Data Source pointing to the database created above. You can see them in Figure 1.

Figure 1


Each dataset maps to the stored procedure of the same name.

The report for this example is very basic. It is simply a table (tablix) which shows the PersonRace column for each row returned in the getPersonReport dataset. The report layout is shown in Figure 2.

Figure 2


Once all three datasets are added, there will be two report parameters added for you, as shown in Figure 3.

Figure 3


We need to do a little work with each parameter.

Double-click the RaceList parameter, revealing the Report Parameter Properties dialog shown in Figure 4.

Figure 4


Ensure that the Allow Multiple Values option is checked, as shown by the red arrow in Figure 4. Then click the Available Values option shown in the oval in the upper left. This will bring up the Available Values dialog just like Figure 5.

Figure 5


Select the Get Value From a Query option and choose the getRace dataset. From there, just match up the Value and Label fields like in Figure 5. Then click OK.

Double-click the Person parameter to bring up its Properties, as in Figure 6.

Figure 6


Make sure the Allow Multiple Values option is checked. In my report, the Allow Blank Value for this parameter was checked automatically. I tried running the report both with it checked and with it unchecked and noted no difference in functionality. Click the Available Values option to bring up a dialog similar to Figure 7.

Figure 7


Check the Get Value From a Query option and set the Dataset to getPerson. Set the Value and Label fields respectively as shown in Figure 7 and click OK.

Now let’s run the report, which should resemble Figure 8.

Figure 8


Notice the Race List parameter has checkboxes for each Race along with a (Select All) option, which SSRS built for us. Also notice that the Person List parameter is grayed out. Because it requires Races to be passed to it, SSRS knows that we cannot use until at least one Race has been selected. When we choose a Race, perhaps Dwarves, and click away from the Race List parameter, that value is passed to the getPerson stored procedure, producing the available values list for the Person List parameter, as shown in Figure 9.

Figure 9


Notice that we have a list of folks along with the (Select All) option that SSRS created for us. If you are familiar enough with the works of J.R.R. Tolkien, you will recognize that all of these folks are Dwarves. Either way, click (Select All), which will put a check in every checkbox available, and click View Report. Your results will resemble Figure 10.

Figure 10


That’s really all there is to it. Play around with different combinations (you may enjoy the Easter Egg among the Elves; What’s your favorite color?).

Since it has been a long time since I used SSRS 2005, I cannot fully promise this will work. But none of the functionality above is all that fancy, so it seems to me it could possibly work just fine. If you try it in 2005, please let me know how it goes.

A Milestone: Presenting at PASS Summit

I have been presenting for several years. I can’t properly explain how much I love it. Since before I even attended my first PASS Summit, it has been a career goal that I would one day present at the premier SQL Server focused training event on the planet. When I got the email a while back that my DANGER: The Art and Science of Presenting session had been chosen as an Alternate for the 2013 Summit, I was pretty happy. It was my best result yet.

It is with tremendous joy that I now report that I am no longer an Alternate. Due to a cancellation, PASS had an opening in the Professional Development track and asked me to fill it. Not only do I get to fulfill a career goal of presenting at the Summit, but I get to do so with an exciting new session. I honestly feel this session will not only help new presenters get started on the right foot, but will also help re-invigorate more experienced folk.

You can read the abstract for it here: DANGER: The Art and Science of Presenting