Performance Point Cascading Parameters Connection Formula Example

A simple but intuitive requirement in a report is the ability to restrict the 2nd parameter values based on what you selected in the 1st parameter.   Such an example might be to restrict the list of Staff depending on the Office you selected previous.

In Performance Point this is known as Cascading parameters and was only ushered in with Sharepoint 2010 Service Pack 1.

The implementation of this requires you to insert the following code in the Connection Formula when creating the connection between the 1st filter and the 2nd filter within the Performance Point dashboard.  When creating the filter you could either select “MDX Query” or “Member selection”.

Last but not least you need to create a connection between the 1st parameter and the 2nd parameter inside your Performance Point dashboard.  Drag in the filters as you normally would, then create a connection from the 1st filter to the 2nd filter.  The connection from the 1st to the 2nd filter has a Connection formula defined along the lines of the following.  Note <<SourceValue>> is a moniker and needs to be typed as is.  If your 1st filter and 2nd filter come from different Analysis Services dimensions, then the Measure Group is used to determine the list of member’s in the 2nd filter that relate to the selection made in the 1st filter.

NONEMPTY(EXISTS({[Dimension you want to restrict].children},&lt;&lt;SourceValue&gt;&gt;,'Your Measure Group'))

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