0

I am trying the following query on the demo Sales cube of icCube:

WITH 
    SET [Amer.Countries] 
        AS Descendants([North America],[Country])
SELECT 
    Filter([Amer.Countries],([Measures].[Amount], {[2009], [2010]}) > 13240) on Rows,
    [Measures].members on Columns
FROM [Sales]
WHERE [Time].[2011]

What is wrong with the condition ([Measures].[Amount], {[2009], [2010]}) > 13240 and how do I fix it?

I get the following error message from icCube:

operator '>' syntax error (left-operand:'set') (right-operand:'numeric')

The only thing I understand from this message is that the use of the set  {[2009], [2010]} is out of place. However, I do not understand why that is the case and what the fix should be.            

AlwaysLearning
  • 7,257
  • 4
  • 33
  • 68

1 Answers1

1
([Measures].[Amount], {[2009], [2010]}) > 13240

The left side of 13240 expects a numerical value or at least an expression that can be evaluated to a value. What you wrote is not a tuple and could be thought of as a crossjoin. To calculate the aggregated [Amount] for the year 2009 & 2010 of the current country:

([Measures].[Amount], {[2009], [2010]})

you can use eval function to compute the filtering value:

eval( {[2009], [2010]}, [Measures].[Amount] )

And use it within the SELECT to compute a static set that is ignoring the year [2011] specified in the slicer:

with 
    static set [Amer.Countries] as 
       filter( [North America].children , 
               eval( {[2009], [2010]}, [Measures].[Amount]) >= 13240
       )  

select [Measures].members on 0, [Amer.Countries] on 1
from [Sales] where [Time].[2011]

You can have a look to the following introduction of MDX to better understand the notions of tuples, sets, etc...

Hope that helps.

Marc Polizzi
  • 9,275
  • 3
  • 36
  • 61
  • This query produces an empty result-set, which does not make sense as there are countries in North America in which the sales in 2009 and 2010 totaled more than 13240. It looks like slicing on 2011 is applied before calculating `[FilterValue]`, which is not what I want... – AlwaysLearning Oct 23 '17 at 15:39
  • I've edited my response to use a static set instead of a calc. member. This way the slicer is ignored while computing the set of countries. – Marc Polizzi Oct 24 '17 at 06:46