Wednesday, May 2, 2012

Use Member Properties, Filter and With keyword in MDX

This is not a complete post. I came across a situation that I needed to show [Date] dimension values which is not empty as report parameter.

image

But I need yeas that only have values in measure. there for I used following MDX to get that.

with
member yKey as [Date].[Year].currentmember.uniquename
member yValue as [Date].[Year].currentmember.MEMBER_CAPTION
select{yKey,yValue}on 0,
{
filter([Date].[Year].[Year],[Measures].[Total Amount]<>null)
} on 1
from [RetailigenceBI]

this code result in getting


image

Post a Comment