0

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.

Jota
  • 17,281
  • 7
  • 63
  • 93
gowtham
  • 3
  • 3
  • people are voting to close your question because you have not provided an example of the mdx you have attempted. – whytheq May 28 '15 at 08:43
  • did Sourav or myself help with our answer's - any feedback would be appreciated. – whytheq Jun 20 '15 at 11:22
  • Sorry for the delay. What i was looking for is, Say there is a [Region1] with [Store1[], [Store2] and [Store3] as its child, say i have role Manager who authorised for [Store][Region1][Store1] and [Store][Region1][Store2] alone and when i need aggregation across [Region1], MDX should give me just aggregation over [Store1] and [Store2] alone instead of displaying it for all the 3 stores. – gowtham Jul 02 '15 at 16:37
  • ok - easy enough I'll add an edit to my answer - you need a custom measure. – whytheq Jul 02 '15 at 16:55

2 Answers2

1

A very simple way of doing it. @Whytheq gave a much better solution.

SELECT      
      (
       [Product].[Dairy].[GV]* 
       {[Time].[2015].[01].[01] : [Time].[2015].[02].[01]}*
       [Location].[US West].[CA]
      ) ON 1,
[Measures].Revenue ON 0
FROM [YourCube]
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • (upped) exactly that sort of stuff Sourav... imagine the reaction people would get from the community if a question was tagged `sql` and the answer was "SELECT X AS "X", Y AS "Y", SUMZ = SUM(Meas) FROM SOMETABLE GROUP BY X, Y" ... – whytheq Jun 02 '15 at 23:34
0

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];
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • I have exactly tried this. May be i should have posted question in different way. Actually what i need is the output should contain location at Region level which should contain only specific members of its children. Say if Region: [DFW South] includes Stores [Dallas], [Arlington], [Irving] but i need [Location].[DFW South] that should contain only [DFW South].[Dallas] and [DFW south].[Arlington] for the measures at region level – gowtham Jun 04 '15 at 18:37
  • The edit is kind of useful. Only problem i have with this approach is say i need descendants of store dimension to be displayed in my table as well. In that case the above approach with give only the result from [Measures].[Store1and2Measure] across every descendants. Say i need
    Region1 100 Store1 40 Store2 60
    but with above query it will display only 100 across every member.     – gowtham Jul 02 '15 at 17:39