How to use result of MDX query in another MDX?

0
votes

I use MDX query on Microsoft Adventure Work DW 2012 AND I want to have the "SUM" of "Reseller Order Count" in each "country" for the "product"s that have "Reseller Order Count" grater than 30 in "Australia"

in other words,

first I want to find the "Product" s that have "Reseller Order Count" > 30

in "Australia" then find "SUM" of "Reseller Order Count" for the finded "Product"s in other countries

the Measure is

[Measures].[Reseller Order Count]

and the dimentions are

[Geography].[Country].[Country]

and

[Product].[Product].[Product]

Thanks All @};-

2
mdx

2 Answers

0
votes

I think you need to do the ozzy filter in a WITH clause and then use that in the SELECT:

WITH
SET [AustraliaProducts] AS
  FILTER(
    [Product].[Product].[Product].Members,
   ([Geography].[Country].&[Australia],[Measures].[Reseller Order Count]) > 30
  )
SELECT 
  [Measures].[Reseller Order Count] ON 0,
  Non Empty 
    [Geography].[Country].[Country].MEMBERS
  * [AustraliaProducts] ON 1
FROM [Adventure Work];

I'm a little unsure if then in the results you need Australia or not - if not then a further addition in the WITH clause can filter out Australia:

WITH
SET [AustraliaProducts] AS
  FILTER(
    [Product].[Product].[Product].Members,
   ([Geography].[Country].&[Australia],[Measures].[Reseller Order Count]) > 30
  )
SET [exclAustralia] AS
  EXCEPT(
    [Geography].[Country].[Country].MEMBERS,
    [Geography].[Country].&[Australia]
  )
SELECT 
  [Measures].[Reseller Order Count] ON 0,
  Non Empty 
    [exclAustralia]
  * [AustraliaProducts] ON 1
FROM [Adventure Work];
0
votes

Try that:

With 
Member [Measures].[AustraliaProducts] as
SUM(
    [Product].[Product].[Product].Members,
    IIF(
        ([Geography].[Country].&[Australia],[Measures].[Reseller Order Count]) > 30,
        [Measures].[Reseller Order Count],
        Null
    )
)

Select 
[Measures].[AustraliaProducts] on 0,
Non Empty [Geography].[Country].[Country].Members on 1
From [Adventure Work]