The only thing I’ve found to be a friction point is that every time someone updates the Template (which you’re storing somewhere in source control) you need to copy it over to that folder.  That’s no big deal, but when you have a lot of developers that all have to do that frequently, it’s a pain.

The easy way to solve this is with symlinks.  You can’t use a shortcut, but you can create a soft symlink to point back at your template in source control.  That way you will always create a report off of the latest version in your repository.  (Yeah, you still have to keep that up to date… no getting around that.)

[sourcecode language="bash" padlinenumbers="true" wraplines="true"]
mklink "C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesProjectItemsReportProjectTemplatePortrait.rdl"  "C:SourceControlframeworksSsrsFrameworkReportingTemplatesTemplatePortrait.rdl"

mklink "C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesProjectItemsReportProjectTemplateLandscape.rdl" "C:SourceControlframeworksSsrsFrameworkReportingTemplatesTemplateLandscape.rdl"
[/sourcecode]

That’s all there is to it!

Off the top of my head I can think of three major issues with the way SSRS 2008 handles MDX queries.  To be fair, only one of those issues is related to 2008.  The other two issues, which I’m saving for another blog post (no, I’m not even going to hint at them!), are related to SSRS in general, not 2008 specifically.

So what issue am I talking about?  Well, if you’ve ever developed a report in SSRS 2008 that used a parameterized MDX query then I’m pretty sure you’re familiar with it: after switching to MDX mode, making and saving changes to the report query, the MDX Query Designer overwrites the parameter datasets and wipes out all changes made to these parameter datasets.

If you define a parameter in your MDX query, SSRS 2008 will automagically create the parameter, and its dataset, for you.  I have to admit, that’s pretty slick.  Unless the dataset already exists, in which case it gets overwritten.  In fact, anytime you modify an MDX query that references the parameter, the dataset will get overwritten.  If you’re using the generic dataset then this isn’t a problem.  But if you’ve customized the dataset in any way, then it gets annoying fast.  Really fast.  Can you imagine having to update an MDX query that referenced several parameters?  And can you imagine having to make multiple tweaks to said MDX query, just so it would operate just right?

After suffering a mild aneurism I finally said Enough is Enough.  Something must be done.  And, thanks to Teo, something has been done.  Well, kinda-sorta-not really.  There is a bug listed on the Microsoft Connect site but due to introducing backward-compatibility issues, Microsoft is unable to fix this behavior in the current release.  However, they have promised to fix the behavior in a future release.  (Has it been fixed in R2?)

In the meantime, you can perform the workaround described below.  But be warned, it involves manually editing the XML code, so you should probably make a backup of your RDL in case things go horribly awry. 

How to Prevent SSRS 2008 from Overwriting Datasets:

1. Open the report in XML mode.  There are two ways to do this: One way is to navigate to the RDL file in Windows Explorer, right click on the RDL, and select Open With…  and then choose Notepad.  The second way is to open the report in BIDS, right click on the report in the Solution Explorer, and select View Code. 
2. Do a search on “<Dataset>”, and add “<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>” to the “<Query>…</Query>” tags, as shown below.  Just to be on the safe side, I added this line to all of my datasets.

On one report, I knew that my column headers were always going to be the same, no matter what group of data I was running the report against. In this instance, I let SQL Server do the pivot operation.

In another report, I knew that my column headers were going to change depending on the parameters that were passed into the report. In this case, I had no choice but to let SSRS do the pivot.

If I had a choice, I would do the pivot in SQL Server instead of SSRS, if not for simplicity’s sake. I’m more of a coding type of guy. However, there are instances where you cannot avoid a pivot in SSRS. This tutorial will walk you through the steps of setting up a report in SSRS to pivot data on the fly.

First, we need some data to operate with. The data below is what we are going to start with:

CREATE TABLE tmpTable
(
grp INT
,SortOrder INT
,AssessmentTitle NVARCHAR(50)
,AssessmentLevelLabel NVARCHAR(5)
,LevelScaleLow INT
)

INSERT INTO tmpTable
SELECT 1 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,110 AS LevelScaleLow
UNION
SELECT 2 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,138 AS LevelScaleLow
UNION
SELECT 3 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,147 AS LevelScaleLow
UNION
SELECT 4 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,159 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,116 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,153 AS LevelScaleLow
UNION
SELECT 3 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,174 AS LevelScaleLow
UNION
SELECT 4 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,198 AS LevelScaleLow

This is how your table should appear:

This is ultimately how we want our data to appear in the Report:

Since this data can change depending on the Assessment that we pass to the stored procedure, a Matrix is going to be needed in the report in order to facilitate dynamic columns. As I have discussed in an earlier post, a Matrix is needed for situations where dynamic columns are required. Else, you can get by with using a static table.

Now that our data is setup for our tutorial, it’s time to start working with pivoting the data in SSRS.

1. We need to create a new report and add a Matrix dataset to it. Here, I’m assuming that you have already created a data source and populated it with the data from the table above.
2. Grab the Matrix from the Toolbox and drag-n-drop it onto the Report. The Matrix data region provides helpful clues as to where you need to place your fields, specifying Rows, Columns, and Data.
   1. If we look in our table above we can tell that our Rows are going to be the AssessmentLevelLabels with their associated LevelScaleLow values, and the columns are going to be the AssessmentTitles.
3. So, for Rows, select AssessmentLevelLabel, for Columns, select AssessmentTitle, and for data, select LevelScaleLow.

Grouping and Sorting is automatically done when you assign the fields to the Row Grouping and the Column Grouping (AssessmentLevelLabel and AssessmentTitle in our case).

Now, run the report, and VIOLA! Notice that your data is now displayed in the pivoted format!

This approach of pivoting the data in SSRS instead of in SQL Server has a couple of advantages:

• You don’t have to design the report to accommodate every single column. This isn’t such a big deal in our example, since we ultimate only have two columns that end up as output. But imagine if you have the possibility of having 30 columns at any one time. You have to design the report and place out every single one of the 30 columns. What a pain!
• Portability – If you decide to drive your pivoted data from a stored procedure, and you are using this pivot across multiple reports, then you only need to go to one central location to make your changes, and they will all be propagated to each report.

That’s all there is to it! If you have any questions, feel free to leave a comment.

Last week, I needed to prototype a report against an Analysis Services 2005 cube for a client.  The report wasn’t too complicated… I just had the requirement to allow a user to drill down a hierarchy by selecting a member at one level, then displaying all the children for that member, etc.  This is really easy to do in SSRS… but I wouldn’t go so far as to call it super intuitive.  It had been awhile since I wrote a MDX report, and it took me a few minutes to remember just how to do it… so I thought I’d document the steps, for my own benefit if no one else’s.

The sample requires the Adventure Works AS DB.  You can download the sample here.

Scenario

So, I want to create a report that returns the Order Count by Product.  The users need to select a particular Category, and then Subcategory for the products they want to see.  The report also needs to return summary information for both the Category and Subcategory level, and it needs to contain the Category and Subcategory on the report.  This can be a pretty standard business case for reports where the potential data set is to large to report on.

Underlying Data

In order for this to work, I need a hierarchy to drill down.  Here, I’m going to use the Product Categories hierarchy of the Product dimension in the Adventure Works DW.

The Report

Next, I’m going to create a data set to use in my report.  I’m going to go ahead and switch to the MDX view for the data set.  Now, I’m going to go ahead and create a few parameters.

Now, I’m going to go to the Report Layout tab,  right-click off the report, and choose to view my parameters.  Yup, they’re there.  Now, I’m going to go back to the Data tab, and look at my list of available data sets…

Cool!  BIDS just created two new data sets for me, based on the parameters I created.  Note that you have to view your Report Parameters on the Layout tab for these data sets to be created (you just have to open the dialog, you don’t have to do anything).  This seems to be the part that I always forget.

Let’s look at the ParameterCategory data set first.  The MDX that BIDS created looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 

   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 

   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 

   4: SELECT 

   5:     {

   6:         [Measures].[ParameterCaption]

   7:         , [Measures].[ParameterValue]

   8:         , [Measures].[ParameterLevel]

   9:     } ON COLUMNS , 

  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 

  11: FROM 

  12:     [Adventure Works]

But when we run that, it returns everything in the hierarchy… including that nasty ‘All Products’, which we don’t want our users selecting.  So, we’ll make a slight modification, and change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘[Product].[Product Categories].Children’.  Now, we get just the Categories that we want our users to select.

Now, we want to look at the ParameterSubCategory dataset.  The MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 

   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 

   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 

   4: SELECT 

   5:     {

   6:         [Measures].[ParameterCaption]

   7:         , [Measures].[ParameterValue]

   8:         , [Measures].[ParameterLevel]

   9:     } ON COLUMNS , 

  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 

  11: FROM 

  12:     [Adventure Works]

Again, not precisely what we want… plus, we want this one to be parameter driven.

First, we need to add the ParameterCategory (re-use the original Parameter name to keep an extra data set from being created later) to the report:

Then, we change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘STRTOMEMBER(@ParameterCategory).Children’.  Now, our MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 

   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 

   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 

   4: SELECT 

   5:     {

   6:         [Measures].[ParameterCaption]

   7:         , [Measures].[ParameterValue]

   8:         , [Measures].[ParameterLevel]

   9:     } ON COLUMNS , 

  10:     STRTOMEMBER(@ParameterCategory).Children ON ROWS 

  11: FROM 

  12:     [Adventure Works]

and it will only return the Children of the selected category.  You could also create your MDX using an expression, and directly use the the ‘Parameters!ParameterCategory.Value’ embedded in a quoted string.  There is a performance hit for using anything like STRTOMEMBER, but in this case it isn’t worth the trouble to me to do it that way.

Almost done.  Except that we haven’t gotten around to writing the actual query that drives the report…  so we go back to our main data set.

Now, when we create our query, we might be tempted to return the children of the Subcategory… but this won’t get us summary information at the Category and Subcategory levels.  So we’re going to write some MDX using the Hierarchize function to make sure our data set contains the selection at the Category level, at the Subcategory level, and all of the Products underneath the selected subcategory:

   1: SELECT

   2:     [Measures].[Order Count] ON 0,

   3:     HIERARCHIZE(

   4:         {

   5:             STRTOMEMBER(@ParameterCategory),

   6:             STRTOMEMBER(@ParameterSubCategory),

   7:             STRTOMEMBER(@ParameterSubCategory).Children

   8:         }

   9:     ) ON 1

  10: FROM

  11:     [Adventure Works]  

Now I’m going to go back to the Layout tab, right-click off the report, pull up my Report Parameters, and change the Default value to Null (so the user will always have to make a selection… though you can make this part as clever as you’d like).

In your report, you may need to use some expressions, filters or other logic to format your results, suppress rows (i.e., the Category will have a Null for both Subcategory and Product… how you want to handle this is up to you).  For this sample, I just wrote a little VB

   1: Dim _Indent As String = "  "

   2:  

   3: Public Function FormatProduct(ByVal Category As String, ByVal SubCategory As String, ByVal Product As String) As String

   4:     Dim ret As String = String.Empty

   5:  

   6:     If Not String.IsNullOrEmpty(Product) Then

   7:         ret = _Indent & _Indent & Product

   8:     Else If String.IsNullOrEmpty(Product) And Not String.IsNullOrEmpty(SubCategory) Then

   9:         ret = _Indent & SubCategory

  10:     Else

  11:         ret = Category

  12:     End If

  13:     

  14:     Return ret

  15: End Function

And used an expression to format my Product column in the report.

Summary

That’s about it.  We now have a simple report with some cascading parameters.