How to set SSRS parameters with non-empty available values for a dimension using MDX
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])
3 Comments
→
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.
It is important i like around your blog is that you simply often post primary to the point advice.
Love your site, it has so much great information. thanks for the share.