Using TYPED for member properties in SSRS MDX queries

IMG_0032_thumb6
Crappy Code Games 2011 – London
March 31, 2011
MDX-Basics8
MDX Basics
April 5, 2011
Show all

Using TYPED for member properties in SSRS MDX queries

Sort-by-member-property-report-_thum

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

Leave a Reply

Your email address will not be published. Required fields are marked *