Geeks With Blogs
James Rogers BI from the trenches...
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. Posted on Monday, October 8, 2007 2:13 PM PerformancePoint | Back to top


Comments on this post: NON EMPTY Filtering on PerformancePoint Scorecard

# re: NON EMPTY Filtering on PerformancePoint Scorecard
Requesting Gravatar...
Welcome aboard, fellow Geek!
Left by John Workman on Oct 08, 2007 7:29 AM

# re: NON EMPTY Filtering on PerformancePoint Scorecard
Requesting Gravatar...
Hi Jame,
very good post! Did you manage to get it to work when using multiple dimensions, as you mentioned?
Regards,
Half
Left by Half on Jan 22, 2008 6:24 PM

# re: NON EMPTY Filtering on PerformancePoint Scorecard
Requesting Gravatar...
Yes - see the following post: http://geekswithblogs.net/ManicArchitect/archive/2008/03/25/120756.aspx
Thanks!
Left by James on Mar 25, 2008 10:12 PM

# re: NON EMPTY Filtering on PerformancePoint Scorecard
Requesting Gravatar...
hi jame

I am also working towards hiding the rows which does not contain the data.
Can you be more specific in your approach as how to filter empty dimensions.
i am compltely new to MDX, so if could explain in detail, it would be very much helpful.

Regards.
Left by Anivesh on Jun 30, 2009 3:40 AM

Your comment:
 (will show your gravatar)


Copyright © James Rogers | Powered by: GeeksWithBlogs.net