MDX filters to restrict empty rows in performance point server RRS feed

  • Question

  • HI

    I am using an MDX filter in the performance point server dashboard to restrict the rows which does not have any value.
    I will summerize the steps.

    1.First in the dashboard, i  create one mdx filter with following querry
    NON EMPTY { [Dimension].[<name>].members } ON COLUMNS,
    [<cube name>]'

    when i check this querry in the SSMS it works pretty well.

    But at the end of filter creation in PPS i get following message:

    No filter data retrieved

    Warning 1:  Filter was created, but no default data was retrieved.
    To resolve the problem, follow the step(s) below.
    1. Verify that the data source for this filter is properly configured.
    2. Check the server event log.  Additional information about this problem may have been recorded

    2. then  link this filter to scorecard in the dashboard editor.

    When i try to see the preview i can still see the empty rows even though i have applied filter to it.

    Plz help....

    Tuesday, June 30, 2009 1:10 PM

All replies

  • Hi,

      You just need to use the following expression in the filter

    Nonempty{[dimension].[hierarchy name].allmembers} or ex: nonempty([time].[Month].allmembers)

    Tuesday, June 30, 2009 5:05 PM
  • Thanks for quick reply..

    I have tried this.the filter was successfuly created, but this did not solve my purpose.

    I will try to explain my case.

    Suppose I have 10 suppliers and for say 5 supliers i have data and for the rest i dont have data.
    when i create one scorecard in PPS, for 5 suppliers it gives me the data and for rest five it does not. I want to show only those Suppliers which have data and rest i dont want to show in the scorecard.

    Is there anyway in which i can implement this?
    Wednesday, July 1, 2009 8:15 AM
  • Hi,

      I don't think so in scorecard this is possible. Just can you briefly outline your scorecard structure so that i can try it out in my environment.

    Wednesday, July 1, 2009 12:56 PM
  • You can setup a filter link formula based on a selection from say a Date filter on your dashboard to generate the members that will be displayed in your scorecard.  Take a look at these postings:

    Dynamic Dimension members on a PerformancePoint KPI (specifically in the comments by Nick Barclay)

    Create a custom MDX filter using the Formula Editor dialog box
    Dan English's BI Blog
    Please mark posts as answer or helpful when they are.
    Wednesday, July 1, 2009 4:33 PM