locked
Excel Services Pivot Table Results Different Than Visual Studio Browser Results RRS feed

  • Question

  • I have a base query that I'm trying to convert into a series of Calculated Members & Named Sets, so that it can be used in an Excel Services Pivot Table report.  Running this MDX query below in Management Studio, I get the results I'm wanting:

     

    With 
    	member [0/3] as filter(NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 0).count
    	member [1/3] as filter(NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 1).count
    	member [2/3] as filter(NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 2).count
    	member [3/3] as filter(NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 3).count
    
    Select 
    {
    	[0/3],
    	[1/3],
    	[2/3], 
    	[3/3] 
    }	on columns,
    
    NonEmpty 
    (
    	[Teacher].[Teacher Name].children,
    	([Proficiency Assessment Standards].[Standard Area].&[Algebra I], 
    	 [Proficiency Assessment Standards].[Diagnostic].&[False],
    	 [Location].[Location].&[Doss High], 
    	 [Measures].[At Standard]
    	)
    )	on rows
    
    From	[Proficiency Standards Assessment - All]
    Where	(
    		[Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		[Proficiency Assessment Standards].[Diagnostic].&[False],
    		[Location].[Location].&[Doss High]
    		)
    
    

     

    0/3 1/3 2/3 3/3

    Teacher1  5  9  2  7
    Teacher2  3 12 10  1
    Teacher3   2 10  9  8

    I was able to copy over the member statements and create Calculated Members without making any changes, and I took the "Rows" portion of the query and converted it into a dynamic Named Set by changing the specific keys into the generic .members:

     

    When I set up all the filtering and conditions in the browsers, I get the same results that I'm hoping for.  At this point I thought I had everything done and ready to go; however, when I try to recreate this in Excel, it doesn't filter out the teachers for this location and wants to return all teachers.  The only different I see is that in my "Browser" setup, I was able to filter by the Named Set "Teachers" and use the Dimension Member "Teacher Name" as the row member; and in the Excel Pivot Table, it will not let me use both the Named Set & and Dimension member, it states I must use one or the other.  

     

    So my questions are, why does the filtering work differently in Excel, and what can I do to recreate my "Browser" results?

    Monday, January 9, 2012 5:16 PM

Answers

All replies

  • Base on how Excel works with the subselects you are most likely seeing the different results.  But this is not a PerformancePoint related question. I would suggest posting this in the Analysis Services forums -http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads.

    Thanks.


    Dan English's BI Blog
    • Marked as answer by puffster Thursday, January 12, 2012 8:58 PM
    Tuesday, January 10, 2012 12:23 PM
  • Thank You Sir...is there a way to move the thread, or do I need to copy & delete?
    Tuesday, January 10, 2012 1:23 PM
  • The moderator of the forums could move the thread.  I believe only the Microsoft employees have that ability right now in this forum.  So Heidi could move this.


    Dan English's BI Blog
    Tuesday, January 10, 2012 6:40 PM
  • I went ahead and copied it over myself -- I don't expect anybody else to clean up my mess, just wanted to make sure I wasn't missing anything :).

     

     

    Tuesday, January 10, 2012 7:02 PM