none
Simple MDX Query Question From a Newbie RRS feed

  • Question

  • My MDX query below works, but doesn't do what I want:

     

    Select 
    
    	filter([Student].[Person ID], [Measures].[Not At Standard] = 0) on columns,
    	NonEmpty([Teacher].[Teacher Name].members) on rows
    
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Location].[Location].&[Doss High]
    		)
    

     


    It simply returns a list of teacher names that match this criteria.  What I want, is the list of teacher names in column 1 and the count of the # of rows that match this criteria in column 2.  I was hoping it would be as simple as altering my columns row to look like this:

    filter([Student].[Person ID], [Measures].[Not At Standard] = 0).count on columns,

    But I get fussed at and told: 

     

    The Axis0 function expects a tuple set expression for the  argument. A string or numeric expression was used.

    I understand what it means, but I still am uncertain as to how to get the count that Iwant.  Can somebody take pity and help me transform this output:
    Teacher 1
    Teacher 2
    Teacher 3
    Into this output:
    Not At Standard
    Teacher 1 12
    Teacher 2 15
    Teacher 3 10

     

    Wednesday, December 28, 2011 4:33 PM

Answers

  • Create a Calculated measure using your FILTER expression and sue that on COLUMNS. That should do it.

     

    WITH MEMBER Measures.[StudentCount] AS 
    filter([Student].[Person ID], [Measures].[Not At Standard] = 0).COUNT
    
    Select Measures.[StudentCount]
    	on columns,
    	NonEmpty([Teacher].[Teacher Name].members) on rows
    
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Location].[Location].&[Doss High]
    		)
    


     


    http://dailyitsolutions.blogspot.com/
    • Edited by Umair.Khan Wednesday, December 28, 2011 5:15 PM
    • Marked as answer by puffster Wednesday, December 28, 2011 6:58 PM
    Wednesday, December 28, 2011 5:14 PM

All replies

  • Create a Calculated measure using your FILTER expression and sue that on COLUMNS. That should do it.

     

    WITH MEMBER Measures.[StudentCount] AS 
    filter([Student].[Person ID], [Measures].[Not At Standard] = 0).COUNT
    
    Select Measures.[StudentCount]
    	on columns,
    	NonEmpty([Teacher].[Teacher Name].members) on rows
    
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Location].[Location].&[Doss High]
    		)
    


     


    http://dailyitsolutions.blogspot.com/
    • Edited by Umair.Khan Wednesday, December 28, 2011 5:15 PM
    • Marked as answer by puffster Wednesday, December 28, 2011 6:58 PM
    Wednesday, December 28, 2011 5:14 PM
  • Thanks for your answer, this is exactly what I was looking for and got me on the right track!!
    Thursday, December 29, 2011 2:11 PM