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.
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];