Pages

Saturday, May 29, 2010

Auto-update of parameter datasets in SSRS 2008

The times I have spent with SSRS 2008 can be likened to that of a married couple’s life story. The moment I first laid my eyes on her during the Microsoft seminars and community previews, I knew that she was going to be the one. I secretly started collecting details about her, as much as possible and awaited her arrival. Needless to say, I was one of the very first to have the trial version installed and in the dates that followed, I was just astounded at her potential – she seemed so full and promising. I even started writing sonnets in her praise (read as reviews), love struck that I was. Eventually, the marriage took place (yups, we got the license too!) and we revelled in the honeymoon period. But once the honeymoon period got over, I started noticing some irritating things about her that I had not noticed before – the story of every married couple’s life. And one of those irritating features was the auto update of report parameter datasets.

Everyone who has worked with SSRS 2008 must have experienced this sometime or the other. You use the query builder to make a query with report parameters in them, and then you decide to modify the parameter dataset queries. After that, you play around with the main dataset, make those few important changes that the query builder doesn’t support and then you execute the query. You wouldn’t find anything missing till you actually preview the report and notice that the report parameters are not reflecting the changes that you just did. You double check and yups, the parameter queries have indeed changed and all the hard work that you did to change them has gone for a toss. Well, with this post, this issue is going to be history.

For illustrating the problem, I have made a simple report which has a Year and a Quarter report parameter, and the category and Quantity for the selected values in a table.

Report Parameter Suppression

Now when I expand the parameter list, I can see that there is an option for All Periods which I would not like to display.

Parameter - with All Periods displayed

So I go back and change the query to remove the all period option.

Altering the rep parameter dataset query

Now I can preview the report and the All Periods option is not there anymore.

Parameter without All Periods

Looks good huh? Well it does as long as you don’t touch the main dataset query. The moment I decide to go and display only the top 2 categories instead of all the categories by modifying the query, things change.

Main Dataset query change

Now when I preview the report, I get the following result

Changed Report

The report now displays only the top 2 categories but then the changes that I had done to the parameter is gone and All Periods is back to haunt me.

There are 2 methods to solve this issue -

Method 1

Make sure that wherever the particular parameter (whose dataset query you would not like to be auto updated) is referenced in a query, the default value is specified using the fully qualified name or the unique name rather than selecting the dimension, hierarchy and name.

An example is shown in the image below

Modifying parameter properties

Please note that you might have to delete the old parameter from the Query Parameters window and then create a new one else you would not be able to leave the Dimension, Hierarchy columns empty as shown above.

Method 2

I owe this solution to a blog that I read from Teo Lachev (MVP). Follow the steps below to implement the solution:-

1) Right click on your report and select the View Code option

Select View Code

2) Find the code section which will have the required dataset name (where you want to prevent the auto update). Go to the line just above the </Query> and paste

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

An example is shown in the image below

SuppressAutoUpdate Code

Now that you have got the solution that you had been looking for, why are you still reading on? Oh are you waiting for me to complete the story that I was telling you at the start? :) Ok here it goes. Now that I have spent quality time with SSRS 2008, I have understood her more and have learned to work my way around her when she throws her tantrums. No girl is perfect, and I don’t claim SSRS 2008 to be perfect but for me, she is the best one around and I just love her a lot – the story of every happy married couple’s life :)

Monday, May 24, 2010

Static Named Sets v/s Dynamic Named Sets

It is no big secret that most of the questions that are asked in interviews today are repeated. One of those questions which interviewers just love to ask again and again is the difference between  a static named set and a dynamic named set. The popularity of this question is further aided by the fact that this particular feature of dynamic named sets was newly introduced in SQL Server 2008. So here I continue my interview question series on this topic.

Many times, it can happen that we end up writing very complex MDX expressions revolving sets. To simplify such expressions or to improve the performance of some queries, we can extract the definitions of some sets into a separate named set expression which are called named sets. A named set can be created either using a CREATE SET statement or a WITH SET statement (For syntax, refer Building Named Sets in MDX). In SQL Server 2005, there was only the Static named sets feature but in SQL Server 2008, both Dynamic and Static named sets are present.

Static Named Sets

The value of a static named set is evaluated either when the CREATE SET statement is executed (if it is defined inside the cube) or right after the WHERE clause is resolved (if the set is defined within a WITH clause). Hence, when a query references a static named set, the set is not resolved again in the context of the current coordinates which explains why it is known as a static named set.

To explain this better, let us consider a scenario where we want to count the number of years for a particular country where the order is greater than 4000.

WITH 
  MEMBER [measures].[country count] AS 
    Count
    (
      Filter
      (
        [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
       ,
          (
            [Customer].[Customer Geography].CurrentMember
           ,[Measures].[Internet Order Quantity]
          )
        > 4000
      )
    ) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works];

This will give us the following output

result 1 

Now let us simulate creating a static named set in the Adventure Works cube and see the result by running the following query in SQL Server Management Studio (SSMS).

CREATE 
  STATIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ;

GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]
WHERE 
  [Customer].[Country].&[Canada];

Note that we are using GO statement in between, else you will get an error in SSMS saying that multiple statements are not allowed. As per the explanation above, the set would be evaluated when the CREATE SET statement is executed and the coordinates at that context for Customer Geography is ALL. So now when the set is referenced in the query below, it will the count for the ALL member even if Canada is selected.

Static set result

To verify this, I queried the cube to just return the count of countries when no country is selected and the country count matches with the above result.

Total result for all countries

Now what do we do if I just wanted to consider the coordinates in the where condition and return the result based on it (in this eg, consider the filter condition on Canada and return just the periods having order greater than 4000 for Canada which is 1)? That is where Dynamic sets come to our aid.

Dynamic Named Sets

With the help of dynamic named sets, we can create a named set which would be revaluated in context of each query that references it. It is evaluated in the context of WHERE clause and SubSelect of every query but are not evaluated in the context of every cell. The most visible difference would be in the case of dynamic named sets created in the cube. Let us use the previous example and see what happens when we use a dynamic set instead of a static set.

CREATE 
  DYNAMIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ;

GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]
WHERE 
  [Customer].[Country].&[Canada];

The output is shown below

Dynamic Set result

Finally we get the result we want taking the filter conditions in the WHERE clause (for Canada). We can also test what happens when we remove Canada from the WHERE clause and introduce it on the rows.

CREATE 
  DYNAMIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ;

GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].&[Canada]    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]

As mentioned, the set will be evaluated using the current coordinates which would be ALL for Country as there is nothing on the WHERE clause. The output is shown below

Dynamic set result with Canada on rows 

Now that you have understood that the difference between a static named set and dynamic named set is not only the difference in their syntax, you might want to go to the following blogs by Mosha (who is called the father of MDX) and understand how a named set improves performance

 

Thursday, May 20, 2010

Converting a single comma separated row into multiple rows

Ever since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma separated row back to multiple rows. I checked up a few blogs and I found out that it was possible to do with the help of custom functions or stored procedures, but I was not interested in all of them. Finally I got the answer that I was looking for, and now that I did, I did not waste precious time in noting it down here, lest I forget.

Let me try to show you what we are trying to achieve here with the help of an image

Convert comma seperated row to multiple rows

This can be done with the help of the below query

 SELECT A.[State],  
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [State],
CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String
FROM TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);


Again, no stored procedures or function, just plain old SQL :)



Update (23/05/2010) : I saw this blog by Brad Schulz (MVP) explaining this scenario and the performance implications in detail. So people who are serious about getting this in their code, please read his blog before you do so - Delimited String Tennis Anyone?

Tuesday, May 18, 2010

Creating navigation panel for reports

Recently, I was asked to design a set of reports for a dashboard in SQL Server 2008 which would all have the same report parameters passed to them. Also, the users wanted to switch between the reports frequently and hence needed a navigation panel on the left hand side preferably. The moment I heard about navigation panel on the left side, the first thing that came to my mind was document maps (yes, this is the post that I said I would come up with in my previous post). But then the conservative within me was trying to stifle out this idea saying that it can not be used, and even if you are able to use, it would be a big performance issue as all the reports would have to be rendered within the same report at the same time. Another five minutes of brainstorming and I shut the <beep> out of the conservative in me.

Before we start, it is good to know that the main concepts we are going to apply in this post are Document Maps and On Demand Report Processing. So if you are not familiar with these concepts, please go through How to add a Document Map to your reports and On-Demand Report Processing in Reporting Services 2008. Once you are set, follow the below instructions:-

1) Suppose you have 3 reports and you want to have a welcome page with a navigation panel. First, design your home page in the layout of the report.

1 Home Page section 

2) Then, make a matrix below and drag & drop a subreport from the toolbox to the cell section of the matrix. Right click on the subreport and select subreport properties.

2 Subreport Properties 

3) Select the subreport name that you want to display from the drop down list.

3 Add the subreprot name 

Do steps 2 and 3 for each of the report that you need to display in the navigation panel.

4) Right click on each of the tablix, select the tablix properties and tick the ‘add a page break before’ option. Also make sure that there is a dataset name associated with the tablix, else it would throw an error when we preview. Just make a dummy dataset for this purpose if it is not already there.

4 Tablix properties

Note that I have deleted the header row of the matrix. Even though this is not necessary, I did it to reduce the spacing between the start of the page and the subreport. 

5) Make sure that border lines of the tablix have been made invisible.

6) Select each tablix and press F4 to open the properties panel. Write the name that you want to see in the navigation panel to the DocumentMapLabel property

5 Adding document map label to tablix 

7) Now we are all set to preview the report.

6 Home Page 

On clicking the first report, we get the following page

Order Count by Cat Report 

On clicking the second report, we get the following page

Rich text report 

On clicking the third report, we get the following page

Subcat sales by Prod report 

This technique can be extended to passing the same report parameters to all the subreports and hence, creating a feeling of having a single parameter toolbar. And there would be no nasty green processing symbol also :)

P.S. : This report has been done in SQL Server 2008 and even though it can be replicated in SQL Server 2005, it is not advisable unless the reports are very small. The reason is that SSRS 2008 has a new feature called On Demand Report Processing, because of which processing can be delayed until a user navigates to a particular page showing that data region or hitting an expression that references information from those other datasets. Hence, in effect, the time taken to display the report would be equal to the time taken to display the subreport in that page (as there is only one subreport in one page). But in 2005, it would be equal to the sum of the times of all the subreports and the initial load time of the report can become too high. Anyways, it is worth a try in SSRS 2005, and if it works for you, give me a party ;)