none
filter value function not working on analytic grid

    Question

  • i created a analytic gird report and only have one column which is dollar value.

    when i go to filter by value or top 10 function under Edit--> filters, it doesn't work. it display "there are no data rows to display."

    any help will be appreciated .

    nick


    nick
    Tuesday, September 14, 2010 4:28 AM

Answers

  • Based on my test it is definitely only applying the topcount filter at the top level.  I reviewed the MDX that is being generated and I can see that is what is being generated.  That is not what I would expect.  If I apply a filter I would want it to display the Top X of what is being displayed.  This is exactly what the Chart does and I would expect the behaviors of this functionality to be the same.

    The example from my blog post was using the PDW sample data, but you could do that same one with Adventure Works.  I did another example with the Contoso and placed the Product user defined hierarchy on the Rows and Sales Amount on the Columns.  For the Product I have the All along with its Children selected (9 members in all are displayed).  If I apply the Top 5 I still have 9 members displayed.  If I switch over to a Bar Chart and re-apply the Top 5 filter I get 5 members (All, Home Appliances, Computers, Cameras and camcorders, TV and Video).  The MDX is much cleaner as well and does not include all of the calculated sets and members.

    I would expect results to be like the Bar Chart and not like the what is being done in the Analytical Grid.  I would definitely want these to both function the same and implement the MDX logic that is being performed in the Charts and not that of the Grid which seems very inefficient.

    Analytical Grid MDX:

    WITH
    SET [HierSet_Product.Product] AS DISTINCT( HIERARCHIZE( { [Product].[Product].[All], [Product].[Product].[All].CHILDREN } ) )
    MEMBER [Product].[Product].[MinLevel] AS MIN([HierSet_Product.Product], [HierSet_Product.Product].CurrentMember.Level.Ordinal)
    SET [TopLevelSet_Product.Product] AS INTERSECT([HierSet_Product.Product], AddCalculatedMembers([Product].[Product].Levels([Product].[Product].[MinLevel])))
    SET [HierSet04313e5a924e47d7bdebb563672e1f79] AS GENERATE(TOPCOUNT(NonEmpty([TopLevelSet_Product.Product],( [Measures].[Sales Amount] )),5,( [Measures].[Sales Amount] )) AS [FilteredSet_Product.Product],  INTERSECT([HierSet_Product.Product],DESCENDANTS([FilteredSet_Product.Product].CurrentMember)),ALL)

    SET [HierSetBreakSort04313e5a924e47d7bdebb563672e1f79] AS ORDER([HierSet04313e5a924e47d7bdebb563672e1f79], ( [Measures].[Sales Amount] ), BDESC)
    SET [HierSetSort04313e5a924e47d7bdebb563672e1f79] AS ORDER([HierSet04313e5a924e47d7bdebb563672e1f79], ( [Measures].[Sales Amount] ), DESC)
    MEMBER [Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79] AS MIN([HierSet04313e5a924e47d7bdebb563672e1f79], [HierSet04313e5a924e47d7bdebb563672e1f79].CurrentMember.Level.Ordinal)
    MEMBER [Product].[Product].[MaxLevel04313e5a924e47d7bdebb563672e1f79] AS MAX([HierSet04313e5a924e47d7bdebb563672e1f79], [HierSet04313e5a924e47d7bdebb563672e1f79].CurrentMember.Level.Ordinal)
    SET [LevelSet04313e5a924e47d7bdebb563672e1f79] AS ORDER([Product].[Product].Levels([Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79]).Members, ( [Measures].[Sales Amount] ), BDESC)

    SELECT
    { [Measures].[Sales Amount] }
    ON COLUMNS,

    NON EMPTY IIF ([Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79] = [Product].[Product].[MaxLevel04313e5a924e47d7bdebb563672e1f79], [HierSetBreakSort04313e5a924e47d7bdebb563672e1f79],GENERATE([LevelSet04313e5a924e47d7bdebb563672e1f79],{INTERSECT([HierSetSort04313e5a924e47d7bdebb563672e1f79], DESCENDANTS([Product].[Product].CURRENTMEMBER))}))
    ON ROWS

    FROM [Sales]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    Bar Chart MDX:

    SELECT
    { [Measures].[Sales Amount] }
    ON COLUMNS,

    NON EMPTY { ORDER( TOPCOUNT(NonEmpty(DISTINCT( HIERARCHIZE( { [Product].[Product].[All], [Product].[Product].[All].CHILDREN } ) ),( [Measures].[Sales Amount] )),5,( [Measures].[Sales Amount] )), ( [Measures].[Sales Amount] ), BDESC ) }
    ON ROWS

    FROM [Sales]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR


    Dan English's BI Blog
    Tuesday, December 28, 2010 1:39 PM

All replies

  • Is your "dollar value" a calculated member?
    http://dailyitsolutions.blogspot.com/
    Wednesday, September 15, 2010 6:23 AM
  • no. it's not.

    it is a normal attribute member in the cube.

     


    nick
    Wednesday, September 15, 2010 6:45 AM
  • I finally got around to testing this a bit and I am getting the exact same results as you are "there are no data rows to display".  I thought for sure I have used this before, but I must just be thinking of the filtering of empty rows and/or columns.  Weird.  I ran the profiler trace to view the MDX generated and there is a lot going on, but in the end, no results...

    This definitely appears to be a bug unless I am missing something here...anyone from MSFT care to chime in?  Are we missing something?  I did a very basic test with a dimension member on rows, a regular measure on columns, and then tried to use either of the Value or Top filter functionality and nada.


    Dan English's BI Blog
    Thursday, September 16, 2010 5:21 PM
  • Same results here using PPS 2010. Here is what Microsoft states:

    http://office.microsoft.com/en-us/sharepoint-server-help/whats-new-in-performancepoint-dashboards-HA101812884.aspx#_Toc249959346

    Somehow it don't work. I used a simple quantity value by item hierarchy.


    http://dailyitsolutions.blogspot.com/
    Friday, September 17, 2010 9:21 AM
  • Very odd.  The filtering appears to work with Analytical Charts, but not Analytical Grids...  this definitely needs to get escalated and put on the bug list if it is not already being worked on.

    MSFT?


    Dan English's BI Blog
    Friday, September 17, 2010 2:29 PM
  • Well, it appears that a partial fix has been included in SQL Server 2008 R2 CU5.  I tested it out, but there still seems to be an issue when referencing a user defined hierarchy versus a attribute hierarchy - PerformancePoint Services 2010 Analytical Grid Filter Fix, Sort of.

    FIX: An analytic grid that is connected to SSAS 2008 R2 returns incorrect data when you apply a filter to the analytic grid in PerformancePoint Dashboard Designer

    http://support.microsoft.com/kb/2463203/

    Here is the CU5 link - http://support.microsoft.com/kb/2438347

    Can someone from MSFT comment on this at all?


    Dan English's BI Blog
    • Edited by Dan EnglishMVP Thursday, December 23, 2010 1:13 PM add MSFT feedback request
    Thursday, December 23, 2010 1:10 PM
  • Dan,

    The CU5 for SQL 2008 R2 does fix the Top 10 issue within PerformancePoint.  I have tested and confirmed this.

    You have made a comment about an issue between User Defined hierarchy vs. attribute hierarchy.  Can you give me an example of what you think the issue is?  I did notice that it does sort only at the top level of attribute that has been selected in the grid.  Is this what you are looking at too?

    Thanks
    Heidi Tr - MSFT

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, December 27, 2010 10:02 PM
    Moderator
  • Based on my test it is definitely only applying the topcount filter at the top level.  I reviewed the MDX that is being generated and I can see that is what is being generated.  That is not what I would expect.  If I apply a filter I would want it to display the Top X of what is being displayed.  This is exactly what the Chart does and I would expect the behaviors of this functionality to be the same.

    The example from my blog post was using the PDW sample data, but you could do that same one with Adventure Works.  I did another example with the Contoso and placed the Product user defined hierarchy on the Rows and Sales Amount on the Columns.  For the Product I have the All along with its Children selected (9 members in all are displayed).  If I apply the Top 5 I still have 9 members displayed.  If I switch over to a Bar Chart and re-apply the Top 5 filter I get 5 members (All, Home Appliances, Computers, Cameras and camcorders, TV and Video).  The MDX is much cleaner as well and does not include all of the calculated sets and members.

    I would expect results to be like the Bar Chart and not like the what is being done in the Analytical Grid.  I would definitely want these to both function the same and implement the MDX logic that is being performed in the Charts and not that of the Grid which seems very inefficient.

    Analytical Grid MDX:

    WITH
    SET [HierSet_Product.Product] AS DISTINCT( HIERARCHIZE( { [Product].[Product].[All], [Product].[Product].[All].CHILDREN } ) )
    MEMBER [Product].[Product].[MinLevel] AS MIN([HierSet_Product.Product], [HierSet_Product.Product].CurrentMember.Level.Ordinal)
    SET [TopLevelSet_Product.Product] AS INTERSECT([HierSet_Product.Product], AddCalculatedMembers([Product].[Product].Levels([Product].[Product].[MinLevel])))
    SET [HierSet04313e5a924e47d7bdebb563672e1f79] AS GENERATE(TOPCOUNT(NonEmpty([TopLevelSet_Product.Product],( [Measures].[Sales Amount] )),5,( [Measures].[Sales Amount] )) AS [FilteredSet_Product.Product],  INTERSECT([HierSet_Product.Product],DESCENDANTS([FilteredSet_Product.Product].CurrentMember)),ALL)

    SET [HierSetBreakSort04313e5a924e47d7bdebb563672e1f79] AS ORDER([HierSet04313e5a924e47d7bdebb563672e1f79], ( [Measures].[Sales Amount] ), BDESC)
    SET [HierSetSort04313e5a924e47d7bdebb563672e1f79] AS ORDER([HierSet04313e5a924e47d7bdebb563672e1f79], ( [Measures].[Sales Amount] ), DESC)
    MEMBER [Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79] AS MIN([HierSet04313e5a924e47d7bdebb563672e1f79], [HierSet04313e5a924e47d7bdebb563672e1f79].CurrentMember.Level.Ordinal)
    MEMBER [Product].[Product].[MaxLevel04313e5a924e47d7bdebb563672e1f79] AS MAX([HierSet04313e5a924e47d7bdebb563672e1f79], [HierSet04313e5a924e47d7bdebb563672e1f79].CurrentMember.Level.Ordinal)
    SET [LevelSet04313e5a924e47d7bdebb563672e1f79] AS ORDER([Product].[Product].Levels([Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79]).Members, ( [Measures].[Sales Amount] ), BDESC)

    SELECT
    { [Measures].[Sales Amount] }
    ON COLUMNS,

    NON EMPTY IIF ([Product].[Product].[MinLevel04313e5a924e47d7bdebb563672e1f79] = [Product].[Product].[MaxLevel04313e5a924e47d7bdebb563672e1f79], [HierSetBreakSort04313e5a924e47d7bdebb563672e1f79],GENERATE([LevelSet04313e5a924e47d7bdebb563672e1f79],{INTERSECT([HierSetSort04313e5a924e47d7bdebb563672e1f79], DESCENDANTS([Product].[Product].CURRENTMEMBER))}))
    ON ROWS

    FROM [Sales]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    Bar Chart MDX:

    SELECT
    { [Measures].[Sales Amount] }
    ON COLUMNS,

    NON EMPTY { ORDER( TOPCOUNT(NonEmpty(DISTINCT( HIERARCHIZE( { [Product].[Product].[All], [Product].[Product].[All].CHILDREN } ) ),( [Measures].[Sales Amount] )),5,( [Measures].[Sales Amount] )), ( [Measures].[Sales Amount] ), BDESC ) }
    ON ROWS

    FROM [Sales]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR


    Dan English's BI Blog
    Tuesday, December 28, 2010 1:39 PM
  • Any word on whether or not the filtering functionality will ever be fixed so it works and behaves properly like the charts?  It would be nice to have this fixed and working properly.  This is from my previous post - http://denglishbi.wordpress.com/2010/12/23/performancepoint-services-2010-analytical-grid-filter-fix-sort-of/.  I am running the SharePoint 2010 Dec 2011 CU and still nothing has been fixed for this.
    Dan English's BI Blog
    Saturday, January 07, 2012 9:30 PM