Using TYPED for member properties in SSRS MDX queries
Reposted from Jason Thomas blog with the author's permission.
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.
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];
2) Drag and drop the SubCategory and MKey fields into a table.
3) Right click on the Subcategory row group for the properties and then sort it by Mkey field.
4) Now preview the report.
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.
 |
Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com |