Pages

Sunday, April 3, 2011

Using TYPED for member properties in SSRS MDX queries

Recently I got a very interesting query from one of my colleagues. He was trying to sort a table in a SSRS report based on an integer member property, but the sort results were coming wrong according to him. He said that the report was treating the property field as a character and claimed that this was a problem reported by many people all over the MSDN forums. That is when I thought of putting forth this post.

Sort by member property report

To illustrate the problem as well as the solution, I will be using the Adventure Works cube.

1) The requirement is to sort the Sub Categories based on the key values in the report. For this, a new dataset is made with the following MDX query

WITH 
  MEMBER [Measures].[Mkey] AS 
    [Product].[SubCategory].CurrentMember.Properties("key") 
SELECT 
  NON EMPTY 
    {[Measures].[Mkey]} ON COLUMNS
,NON EMPTY 
    {
      [Product].[SubCategory].Children
    } ON ROWS
FROM [Adventure Works];

MDX Query original

2) Drag and drop the SubCategory and MKey fields into a table.

SSRS report table with SubCategory and Mkey

3) Right click on the Subcategory row group for the properties and then sort it by Mkey field.

Table sort by member property SSRS report

4) Now preview the report.

Incorrect sort order

We can see that the key has been considered as a string for sorting, because of which 10 comes after 1. For the proper numerical sorting, we will have to follow the steps below.

Solution

1) Modify the MDX query to include the TYPED keyword.

WITH 
  MEMBER [Measures].[Mkey] AS 
    [Product].[SubCategory].CurrentMember.Properties("key",TYPED) 
SELECT 
  NON EMPTY 
    {[Measures].[Mkey]} ON COLUMNS
,NON EMPTY 
    {
      [Product].[SubCategory].Children
    } ON ROWS
FROM [Adventure Works];

To quote MSDN – “The Properties function returns the value of the specified member for the specified member property. By default, the value is coerced to be a string. If TYPED is specified, the return value is strongly typed. If the property type is intrinsic, the function returns the original type of the member. If the property type is user defined, the type of the return value is the same as the type of the return value of the MemberValue function.”

2) Now preview the report and the sorting would be done in the expected way.

Correct sorting order

1 comment:

  1. Wow what a great post.I am impressed from it.Can you more share with me.I will come back as soon.

    Thanks for more sharing........



    zvnproperties

    ReplyDelete