mdxolapolap-cubemondrianolap4j

How to include members with specific children in MDX query cross join?


I'm new to MDX, and I have following scenario. I have to calculate revenue across specific department (product dimension), specific store (location dimension) and across specific time range.

I have my cube levels as follows.
Product <- Department <- Item
Location <- Region <- Store
Time <- Year <- Month <-Day

Say if I have following members

[Product].[Dairy].[Oak Farm]
[Product].[Dairy].[GV]

[Location].[US West].[LA]
[Location].[US West].[CA]

[Time].[2015].[01].[01] : [Time].[2015].[02].[01]


Then I should get result as where in Product should include only GV and location should include only CA

2015-01-01        US West       Dairy       $100
2015-02-01        US West       Dairy       $100

Any help would be appreciated.


Solution

  • Probably several ways depending on exact requirements.

     SELECT 
       [Measures].[SomeCubeMeasure] ON 0,      
       {[Time].[2015].[01].[01] : [Time].[2015].[02].[01]}*
        Exists
        (
          [Location].[Region].MEMBERS
         ,[Location].[US West].[CA]
        )*
        Exists
        (
          [Product].[Department].MEMBERS
         ,[Product].[Dairy].[GV]
        ) ON 1
    FROM [yourCube];
    

    Edit

    To create a measure that just looks at certain stores you could use something like this:

     WITH MEMBER [Measures].[Store1and2Measure] AS
        Aggregate
        (
          {
            [Store][Region1][Store1]
           ,[Store][Region1][Store2]
          }
         ,[Measures].[SomeCubeMeasure]
        )       
     SELECT 
       [Measures].[Store1and2Measure] ON 0,      
       [Location].[US West].[LA] ON 1
    FROM [yourCube];