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.


ORDER(
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.


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

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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