SSRS/MDX Parameter values based on a (filtered) dimension (which actually relates to records in the facts)

[Dim Period] dimension values:
For this demo, I have a dimension called [Dim Period] in my SSAS cube which looks like:

ssas-cube-dim-period-id-values


All values
So here’s how you could diplays all values (children) of a certain dimension:
ssas-cube-dim-period-id-values-all-code

ssas-cube-dim-period-id-values-all-results



Filtered by NOW()/FORMAT

([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")


ssas-cube-dim-period-id-values-filtered-by-now-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Filtered by HAVING

HAVING NOT ISEMPTY([Measures].[--Value--])


ssas-cube-dim-period-id-values-filtered-by-having-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Code
Here’s the MDX script I used:

--All members in the [Dim Period] dimension:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM [CUBE]

--Filtered based on the current date:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT
--Here's a trick that will work on date/time values:
([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)

--But for other non time related dimensional members, you need to a different method:

WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC)
HAVING NOT ISEMPTY([Measures].[BS Balance])
ON ROWS
FROM [CUBE]








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s