James Rogers

Cubes, SQL Server, PerformancePoint, etc..
posts - 6, comments - 4, trackbacks - 10

My Links

News

Archives

NON EMPTY Filtering on PerformancePoint Scorecard

For those who are familiar with PerformancePoint scorecards and dashboards, you have probably run into a problem where empty dimension values cannot be dynamically filtered on scorecards if you map the dimension to the rows or columns on the scorecard in the dashboard designer.  One simple way around this is to use the NONEMPTY MDX function along with an EXISTS function call.  Consider the following example:

Scorecard: Sales
Filters: Org, Time (Site is the lowest level of the Org dimension)

Requirement: Only show Sites that have values for the time selected and are Descendants of the Org selected

To solve this, create a link to the Org filter from the Scorecard (columns or rows).  In the filter link formula (button on the filter link editor in dashboard designer) enter the following (with your particular dimension/hierarchy identifiers, of course):

NONEMPTY(EXISTS([Org].[Hierarchy - Org].[Level 08].members,<<SourceValue>>,'Sales'))

'Sales' is the measure group from the cube being used by the scorecard.  This approach should work as well for using dimensions that may not be the same as the applied filter, though a cross-join may be required.  I have not tried that yet so I will follow up on whether or not my theory is correct.

Print | posted on Monday, October 08, 2007 2:13 PM |

Feedback

Gravatar

# re: NON EMPTY Filtering on PerformancePoint Scorecard

Welcome aboard, fellow Geek!
10/8/2007 7:29 AM | John Workman
Gravatar

# re: NON EMPTY Filtering on PerformancePoint Scorecard

Hi Jame,
very good post! Did you manage to get it to work when using multiple dimensions, as you mentioned?
Regards,
Half
1/22/2008 6:24 PM | Half
Gravatar

# re: NON EMPTY Filtering on PerformancePoint Scorecard

Yes - see the following post: http://geekswithblogs.net/ManicArchitect/archive/2008/03/25/120756.aspx
Thanks!
3/25/2008 10:12 PM | James

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 4 and 2 and type the answer here:

Powered by: