Performance Point – Restricting Years with a custom MDX Parameter Filter

If your first Performance Point report parameter is Year, you may want to restrict the list of available years to a more manageable size.   For example, you may only care for non empty years or the last few years that have measure group data associated.

First, you will need to create a new filter in Performance and select “MDX Query” when prompted.

A sample set of MDX is provided below to select the Non Empty Years for the Measure used for this demo … “MeasureX”. The output is returned in descending order for the parameter value.

NONEMPTY( {[Year].[Year Name].[Year Name].Members},
                  Measures.MeasureX ), [Year].[Year Name].CurrentMember.MEMBER_KEY, DESC

To restrict the list further you could get top 3 performing years i.e.

NONEMPTY( TOPCOUNT({[Year].[Year Name].[Year Name].Members},
                  Measures.MeasureX )), [Year].[Year Name].CurrentMember.MEMBER_KEY, DESC

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s