Skip to content

How to set SSRS parameters with non-empty available values for a dimension using MDX

by tony on July 22nd, 2011

Recently, I have to write a SSRS report which the parameter is a drop-down list showing only non-empty available values for a dimension the dimesion. The below example returns all the Currency Codes where the closing balances is not empty by using FILTER function.

WITH

MEMBER [Measures].[ParameterCaption] AS [Currency].[Currency Code].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Currency].[Currency Code].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Currency].[Currency Code].CURRENTMEMBER.LEVEL.ORDINAL

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
FILTER ( [Currency].[Currency Code].CHILDREN,NOT ISEMPTY( [Measures].[Closing Balance] ) ) ON ROWS

FROM ( SELECT ( STRTOSET(@Agency, CONSTRAINED) ) ON COLUMNS FROM [My Cube])

From → MDX, SSRS

3 Comments
  1. I merely want to say Im beginner to blogs and genuinely savored your weblog web site. Probably Im planning to bookmark your web-site . You surely come with impressive posts. Regards for revealing your blog site.

  2. It is important i like around your blog is that you simply often post primary to the point advice.

  3. Love your site, it has so much great information. thanks for the share. :-)

Leave a Reply

Note: XHTML is allowed. Your email address will never be published.

Subscribe to this comment feed via RSS

Please type the characters of this captcha image in the input box

Please type the characters of this captcha image in the input box

Optimized by SEO Ultimate