I am using icCube's Sales cube to learn MDX. I would like to write an MDX query to show the revenue from sales in 2009 for those countries where the revenue from sales in 2010 was over $80,000. I tried the following, but it returned an empty table with only one column ([Amount]):
WITH
SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [Country].members on 0, [Amount] on 1
from (select [myset] on 0, [Amount] on 1 from [sales])
where [2009]
Just use
WITH
SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [myset] on 0, [Amount] on 1
from [sales]
where [2009]
In MDX, this type of query does not need a subselect or WHERE
. And you even could omit the set myset
, writing
select Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
[Amount] on 1
from [sales]
where [2009]
I tried a similar query on Adventure works, and it shows All Customers, Australia, Canada, and US, but not France, Germany, and UK:
SELECT Filter([Customer].[Country].Members,
([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2006]) > 600000
)
ON 0,
{[Measures].[Internet Sales Amount]}
ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2008]
UPDATE : The tuple evaluation is a case we missed, the bug has been fixed in icCube 4.2. Pay attention that using the same hierarchy on a where clause and an axis it's tricky. Any sets is filtered out : "{[2010],[2011]} on 0 .. where [2010] will return [2010] but a tuple is evaluted without filtering. Different hierarchies of the same dimension will apply the autoexists filter.
As this is a bit tricky we decided to extend SETS to allow to declare a set in a query that is evaluated with the global context - without taking into account where clause and subqueries :
WITH
-- always the same regardless of the where clause and subquery
STATIC SET [Global] Filter([Country].members, ([Measures].[Amount], [2010])>80000)
..
The easy and most efficient solution is to go as Frank is proposing, adding directly the filtered countries in the axis.
SELECT
// this will return the countries with sales amount bigger than 80'000 for [2010]
// e.g. { [USA], [UK] } ... [2010] is is just used for filterting
Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
// When evaluated we're going to use [2009] as it's the slicer default value
[Amount] on 1
FROM [sales]
WHERE [2009]
Regarding your query it should work, I agree, it's a bug in icCube that we will fix asap (PTS). In the meantime you have to avoid using the same hierarchy in the slicer and in the Filter function used in a slicer, something like :
WITH
MEMBER [Amount 2009] AS ([Amount],[2009])
SELECT
Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
[Amount 2009] on 1
FROM [sales]
Or you can change to a version that works in icCube (less efficient than Frank's one) :
SELECT [Country].members on 0, [Amount] on 1
FROM (select Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0 from [sales])
WHERE [2009]
If you're playing with grouping based on different critierias you can use a new feature icCube introduced a couple of months ago : Categories
They allow to create a hierarchy as member of other hierarchies. You could define those countries as [80000+] to afterwards perform calculations based on this new clasification.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With