Section based headers – SSRS

I got an interesting requirement this week. My client has requested for Claim Movement report in different currency and currency profile. He also asked whether it is possible to show exchange rate only for certain profile. I don’t want to add another column; instead I went to show an additional header only for certain currency profile
Example output

Let us take a simple example from Adventure works to illustrate the problem

Database: AdventureWorksDW2008R2
Tables: FactInternetSales, DimCurrency and DimSalesTerritory
Scenario: Report the Sale amount grouped by Territory and currency. Also show an additional header with “Test” only when the customer name is “US Dollar”
Create a report with below Report Query: (Let us use the SSRS grouping)

SELECT ster.SalesTerritoryRegion RegionName

,cur.CurrencyName

, fis.SalesAmount

FROM [dbo].[FactInternetSales] fis

JOIN dbo.DimSalesTerritory ster ON fis.SalesTerritoryKey = ster.SalesTerritoryKey

JOIN dbo.DimCurrency cur ON cur.CurrencyKey = fis.CurrencyKey

Group the results based on Territory and Currency in the SSRS.

Section the reports based on Territory

Sample view of the report

Page 1:

Last page:

Now we want to show an additional header just for the USDollar.

Step1 : Insert an additional row above the currency group.


Step 2: Open the grouping pane in advanced mode

Step 3: select the newly added row group column and find the static element

Step 4: Open the properties pane of the Static element and select the visibility Criteria as shown below

Against the hidden parameter enter the below expression. This will show the row only when Currency name is US Dollar
=iif(Fields!CurrencyName.Value = "US Dollar",False,True)
 

Final Output

Page 1: No additional Header for the Canadian Dollar Section

Last Page: Us dollar with additional Test Header
As you can see Headers can be shown based on sections.

Happy Learning

MDX Where clause slicer axis – AND/OR confusion

This is one of the repeated question and causes confusion among the MDX developers, especially if they migrate from SQL development. Before continuing this article, it is better not to compare the MDX and SQL as this will only lead to confusion and error.

What is “where” clause in MDX?

As per MSDN “The slicer axis filters the data returned by the Multidimensional Expressions (MDX) SELECT statement, restricting the returned data so that only data intersecting with the specified members will be returned”

Does it support OR/AND syntax ?

No, not directly but can be achieved with caution

Simple example for where clause

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,[Date].[Fiscal].[Fiscal Year] ON ROWS
FROM [Adventure Works]
WHERE [Product].[Subcategory].[caps]
The above query will simply slice the cube for Caps sales as shown below. So in SQL term it has restricted the result set for Subcategory = ‘caps’ sales. 

Now let us also check the same data with the below query
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,{[Product].[Subcategory].Members }* {[Date].[Fiscal].[Fiscal Year]} ON ROWS
FROM [Adventure Works]
This will produce the complete set without slicing anything and as expected result are matching

Let us imitate SQL OR clause in the slicer axis

What if I want the helmets sale as well as caps sale. Then in SQL world we will simply say subcategory = ‘caps’ or Subcategory = ‘Helmet’
In MDX world this can be achieved by using multi select in where clause. This was introduced in 2005sp1
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,[Date].[Fiscal].[Fiscal Year] ON ROWS
FROM [Adventure Works]
WHERE {[Product].[Subcategory].[caps]
       ,[Product].[Subcategory].[Helmets]}

Now let us verify the result by running the below query with cross join
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,{[Product].[Subcategory].[caps]
       ,[Product].[Subcategory].[Helmets]} * {[Date].[Fiscal].[Fiscal Year]} ON ROWS
FROM [Adventure Works]

if you sum up the caps and Helmets then you will get the results

Year	Helmets	Caps	Total
FY2008	215923.29	18834.05	234757.3
fy2009	9412.31	854.05	10266.36

Let us imitate SQL “AND” clause in the slicer axis
What if want to get the Helmets UK sales alone. In SQL world we would simply say this by subcategory = ‘caps’ and Country = ‘United Kingdom’
in MDX world we use the same multi select principle. Slight change in syntax as we use curved brackets to represent Tuple rather than Set . But as we are slicing by different dimension it will automatically act as AND condition. if you think about the two 2D graph, (x,y) will define a point in the space. If we mention this in words then we might say point  of “x AND y” in the space
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,[Date].[Fiscal].[Fiscal Year] ON ROWS
FROM [Adventure Works]
WHERE([Product].[Subcategory].[Helmets]
       ,[Geography].[Country].&[United Kingdom])

We can verify the results with Crossjoin
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,([Product].[Subcategory].[Helmets]
       ,[Geography].[Country].&[United Kingdom])  * [Date].[Fiscal].[Fiscal Year] ON ROWS
FROM [Adventure Works]

What is the Problem then?

Multi-select has it’s own disadvantage or in other words issue. The statement given above by MSDN is not entirely accurate. Please read this blog by mosha
One of the main advantage of Where clause is, it sets the context for the whole query and gets evaluated well before the calculated members and other axis. But unfortunately,  if you use the one of the member of slicer axis hierarchy in the where clause then it reset the context and produces wrong results.
select  {[Measures].[Internet Sales Amount]} ON COLUMNS      
        ,[Product].[Category].[Category].MEMBERS
          * {[Product].[Subcategory].[Helmets]
             , [Product].[Subcategory].[caps]
            }  ON ROWS
from [Adventure Works]    

If we write the same query with slicer axis as shown below then we get wrong results
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,[Product].[Category].[Category].MEMBERS  ON ROWS
FROM [Adventure Works]
WHERE {[Product].[Subcategory].[caps]
      ,[Product].[Subcategory].[Helmets]
      }

Conclusion

MDX where clause and SQL where clause are not directly comparable. In MDX we can achieve filtering by three different ways "Where”,Subcube and “Filter” function. We need to use them based on the need and query context.
I am writing another blog to compare this three function and their right usage. As of now the above issue can easily resolved by using subcube. This is the one of the reason why SSRS always uses Subcube
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,[Product].[Category].[Category].MEMBERS  ON ROWS
From  (select {[Product].[Subcategory].[caps]
               ,[Product].[Subcategory].[Helmets]
              } on columns
       FROM [Adventure Works])

SSRS– Multi value parameter–Choosing predefined List

 

I read the below wiki article from satynarayan and found it very interesting as this help in some peculiar scenarios. In SSRS, multi select can be easily allowed but sometime we want to force the combination rather then allowing all possible values  .  This might be due to wrong aggregated values or meaningless figures.

let us say we have parameter with A,B,C and D values. Now we want to force the user to choose only A,B,C or A,C,D or A or B or C.

Instead of Allowing all possible 16 combination, we want to allow only 5 predefined combination. This is very hard to achieve and often need custom code to validate the combinations. But  Satyanarayan proposed a simple solution.

Please read the below article.

In nutshell define the combination as individual parameter values like “A,B,C” and then parse it in the SQL using XML functions.

http://social.technet.microsoft.com/wiki/contents/articles/19621.ssrs-multi-valued-parameter-as-stored-procedure-input.aspx

Happy Reading

Profile MDX & SQL queries from SSRS

 

Disclaimer : I have published the article in the TechNet Wiki as well. http://social.technet.microsoft.com/wiki/contents/articles/19980.profile-mdx-and-sql-queries-from-ssrs.aspx

One of the challenging aspect of integrating MDX queries with SSRS is debugging.  This is mainly because SSRS ignores the #Error messages and simply returns the empty cells. Another area of concern is parameterisation and query building. If you are using parameters for the MDX queries then even a simple mistake would be very hard to analyse without seeing the actual final query.

E.g.

Check the below query I am trying to display all the descendants set in column axis and resulted in error.

WITH MEMBER MEASURES.AXISText AS

Descendants([Geography].[Geography].currentmember,3,self_and_before)

SELECT {[Measures].[Internet Sales Amount], MEASURES.AXISText} ON COLUMNS

     ,Descendants([Geography].[Geography].[Country].&[Australia]

                   ,[Geography].[Geography].[State Province],self_and_after)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on Rows

FROM [Adventure Works]

CELL PROPERTIES VALUE,

                              BACK_COLOR

                            , FORE_COLOR

                            , FORMATTED_VALUE

                            , FORMAT_STRING

                            , FONT_NAME

                            , FONT_SIZE

                            , FONT_FLAGS

SQL server management studio Output sample:

I built a simple report on top of it and got the below result. As you can see the “#Error” has been replaced by empty cells. This is confusing isn’t it?

So knowing the direct SQL would have easily highlighted the issue

Now in order to easily trace the query from SSRS follow the below steps

     1. Create a common Template.

    2. Select the "Query End Event" as mentioned below

    3. Create a new trace and make sure to use the recently created template

    4. Now this will trace both SSAS main Data set query and Parameter query from the SSRS in the computed form

E.g. For the Parameter

E.g. For the Main Dataset query

Include Filters

In real life the server will host multiple databases and even development server will get accessed by multiple team members. So it is better to customise your template based on the filters to focus on the query of your interest.

Best candidate for column filters

• Database name
  
  
  
  • If you have multiple SSAS database then use this filter to narrow it down
  
  
• NTUsername
  
  
  
  • If you using windows login or trying to access from Microsoft visual Studio then this would be your name 

• Success flag
  
  
  
  • This is one of my favourite. If you are receiving an error in the SSRS window and you want to investigate it. Then please set a filter to “Success like 0”

Tracing queries in the SQL server database instance

Select the below events for tracing SSRS queries against SQL server instance

Note: Irrespective of using the Stored procedure from SSRS, RPC completed should be chosen

If you are connecting to SQL database engine instead of SSAS instance then you must use the filter to reduce the number of traces.
Best filter candidate

• Application Name
  
  
  
  • If you are debugging a solution which is connecting to remote server then use “.Net SqlClient Data Provider” as your filter against Application name
  
  
• Database name
  
  
  
  • Use the name of the database against which the query has been issued
  
  
• NTUsername
  
  
  
  • If you are using windows login or trying to access from Microsoft visual Studio then this would be your name

Tabular, Multidimensional and Powerpivot which one to go for

 

I came across this wonderful article in the MSDN . Between power pivot and Multidimensional the choice would be quite simple because the moment we want to build large scale DW the ideal choice would be Multidimensional SSAS cube.

Due to the introduction of Tabular model in 2012 and it’s simplicity most of us want to move towards Tabular. I can even see lot of queries related to tabular model in the forum as most of the Mid cab and small cab companies are choosing the Tabular models now. But I would definitely suggest checking the below article as not all of them supports all the feature. The best part of this article is feature comparison.

Before making the model decision please check this Article.

http://technet.microsoft.com/en-us/library/hh212940.aspx

Happy Reading

Exist vs. Existing and Auto Exist–With Measures–Part 3

Please read the other two parts for continuity

Exist–With Measures

Most of the examples around the Existing keyword talks about the effect of Exists on Dimension . But what will happen if i use it against measure

Will that make any difference?

Existing keyword set the current context on measure calculation which is really invaluable

Let us check the below query

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

As expected, the Calculated measure has summed up the sales related to Australia and Canada, which is way lower than total amount against a year.

Let us execute the below query and get the sales against different countries

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Geography].[Geography].[Country].members on 1
from [Adventure Works]

As you can see, it displays the summed value against each country and they are same. Though theoretically this is correct, we would ideally want to get the data only against Australia and Canada for this new measure.

So what is happening here?

Calculated members doesn’t get affected by the current query context . If we need the current context then we need existing keyword

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Geography].[Geography].[Country].members on 1
from [Adventure Works]

What will happen if we  change the query back to Date dimension?

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

It produces the same result as above because in the above query the current context for Geography dimension is “All Geographies”

let us confirm this with below query

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
member Geographycurrenthier as
[Geography].[Geography].membervalue
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada,Geographycurrenthier} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

This is very useful technique for End user reporting.

Happy reading

Exist v.s Existing and Auto Exist in MDX – Part 2–Usual confusion

Please read the Part1 of the same series to get the continuity

Usual confusions

• Exists and “Auto exists” are nearly same only difference is Exists function don’t display the second set .

Actually no. One set the context other doesn’t

SELECT [Measures].[Reseller Sales Amount] ON 0           
      , Exists( [Product].[Subcategory].[Subcategory].Members 
                ,[Product].[Category].[Clothing]             
              )on 1
FROM [Adventure Works] 
where [Product].[Category].[Bikes]

Above query will produce no results because we are slicing by two different members(Bikes & Clothing) at the same time. But the the query allows you to use the same dimension hierarchy in both rows Axis and slicer axis.

But the below query will produce errors, because it sets the context

SELECT [Measures].[Reseller Sales Amount] ON 0      
     ,  [Product].[Subcategory].[Subcategory].Members *
              [Product].[Category].[Clothing]

        on 1
FROM [Adventure Works] 
where [Product].[Category].&[1]

• 
  

  Existing keyword is just an extension of Exists, we can achieve the similar results with exist and currentmember function

Yes, But use of existing will improve the maintainability of code

Both the below codes will produce the same results

with member countofsubcategory as 
 count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

with member countofsubcategory as 
 count( exists( [Product].[Subcategory].[Subcategory].Members
                ,[Product].[Category].currentmember ))
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

• 
  

  DO we have not exists function ?

No. But this can be simulated with except function

The below query produces 8 rows of data

SELECT [Measures].[Reseller Sales Amount] ON 0      
     , Exists( [Product].[Subcategory].[Subcategory].Members , 
                      [Product].[Category].[Clothing]
                    )     

        on 1
FROM [Adventure Works] 

With the except function in the below query it produces the opposite set with 30 rows 

SELECT [Measures].[Reseller Sales Amount] ON 0      
     ,EXCEPT([Product].[Subcategory].[Subcategory].Members,
              Exists( [Product].[Subcategory].[Subcategory].Members , 
                      [Product].[Category].[Clothing]
                    )     
             )
        on 1
FROM [Adventure Works] 

• 
  

  Generate function forces it’s own context against existing keyword

Check the below query. It is clear that existing keyword uses the generate function’s context and that is why we got 3 subcategory against all category in the result set

with member countofsubcategory as 
Generate ([Product].[Category].[Bikes]
          ,  count( existing [Product].[Subcategory].[Subcategory].Members)
          )
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

 

This is an another proof that existing keyword is simply an extension of Exists function

Happy Reading

Exist v.s Existing and Auto Exist in MDX – Part 1

Though Exist, existing and Auto exist are conceptually very simple, they are the root cause for many confusion in the complex MDX queries. So let us do a simple comparisons

	Normal Behaviour	Auto Exists	Exists	Existing
Theory	When the two sets are cross joined it will produce all possible combination. E.g. if we join two sets with 4 dimension each then it will produce 16 members	It forces the natural hierarchy in the below Scenarios When two sets containing members of the same dimension are cross-joined, the resulting set is limited to those combinations of members actually observed in that dimension. When the WHERE clause contains a member of a dimension, sets along the axes containing members from that same dimension are limited as well.	It forces the natural hierarchy with out returning the forcing/second set	Auto exist is not applicable to calculated members and if we want to force this behaviour then we need existing keyword
When to use	When we want to display the measures based on two different dimension	When we want to display the measures based on the members in different level from same hierarchy	Achieve the same Auto exists results with out displaying second Dimension	Forces the local context in the calculated members

Normal Behaviour

Run the below query

SELECT [Measures].[Reseller Sales Amount] ON 0
      , [Product].[Category].[Category].Members on 1
from [Adventure Works] 

Based on the above query we have 4 Categories in the Product Categories Hierarchy

SELECT [Measures].[Reseller Sales Amount] ON 0
      , [Geography].[Geography].[Country].members
          on 1
from [Adventure Works] 

Based on the above query we have 6 countries under geography hierarchy

Let us Cross Join it

SELECT [Measures].[Reseller Sales Amount] ON 0
      ,[Product].[Category].[Category].Members 
      * {[Geography].[Geography].[Country].Members
         } on 1
from [Adventure Works]

So along with the header row it has returned 25 rows which is direct cross product of 4 Products and 6 countries (24 data row + 1 Header row)

What other ways to achieve the cross join

You can use the cross join function as shown below

SELECT [Measures].[Reseller Sales Amount] ON 0
      ,crossjoin ([Product].[Category].[Category].Members 
      , [Geography].[Geography].[Country].Members
         ) on 1
from [Adventure Works] 

Even if you remove the Crossjoin keyword in the above query,  it will still produce the same result .

 

SELECT [Measures].[Reseller Sales Amount] ON 0
      ,([Product].[Category].[Category].Members
, [Geography].[Geography].[Country].Members
         ) on 1
from [Adventure Works]

 The reason is MDX will try to resolve the other dimensions of the cube for every combination (remember the partial tuple law)and will get you the cross join.

Auto Exists

In the above example we have used the cross join between two different hierarchies (Product.category and geography.country). let us join between category and sub category from same product hierarchy.

Step 1: Let us find the count of subcategory first

SELECT [Measures].[Reseller Sales Amount] ON 0
      , [Product].[SubCategory].[SubCategory].Members on 1
from [Adventure Works] 

 Along with header row we have 38 subcategories, let us cross join subcategory and category.

 

SELECT [Measures].[Reseller Sales Amount] ON 0
      , [Product].[Category].[Category].Members *
         [Product].[Subcategory].[Subcategory].Members   on 1
from [Adventure Works] 

As you can see from the output though it has given the cross product, it still only has the same 38 (37 rows of data + 1 header) rows in the output. as per the normal behaviour it should be 4*38 = 152

The reason is, based on the Product category hierarchy, only valid category and Subcategory combinations are produced. 

 

What is Existing 

Let us create a calculated member in the above query

with member countofsubcategory as 
 count( [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members *
      [Product].[Subcategory].[Subcategory].Members
          on 1
from [Adventure Works] 

 

Though we are displaying each subcategory in the list, still we got the total count . This is because Auto exist doesn’t control the calculated members  

 

So by inserting Existing keyword in the above query,it will produce the expected result which 1 per subcategory

with member countofsubcategory as 
 count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members *
      [Product].[Subcategory].[Subcategory].Members
          on 1
from [Adventure Works] 

 

What is Exists function ?

Exists uses the “Auto exist” functionality but avoid displaying the “second set” in the results

SELECT [Measures].[Reseller Sales Amount] ON 0      
     , Exists( [Product].[Subcategory].[Subcategory].Members , 
              [Product].[Category].[Clothing]
             )        
        on 1
FROM [Adventure Works] 

It only displays the subcategories which are related to Clothing category without displaying category in the result.