I need an MDX query that operates like a Group by Statement

Question

I need an MDX query that is able to aggregate a Fiscal Year while only pulling parts of a year. Let's say the client wants to see sales for a fiscal year but the given date range is 20210601 - 20230303.

As of now if I try to ask for sales crossed with Year and sliced with the above date range I will get all of 2021 and all of 2023 when I really only need the months specified in the date range.

Given how this query needs to interact within a stored procedure, what I would like to do is essentially put a Month Combined on the rows instead of year. This will break down sales for a time period that would look like this for the entire date range

2021:Jun
2021:Jul
2021:Aug
etc

Then I would like sum the figures for each month in the same year and present that as a partial Fiscal year figure. If it isn't possible to separate year from month in the month combined dimension, is it possible to do the same if I used Month and Year separately? For example if I used a code like this

select 
 sum({[Measures].[Claim Charge Amount]} )on columns
 ,NON EMPTY{([Date].[Month].[Month])} on rows

 from 
 [Cube]
 where ([Date].[Date Key].&[20220101] : [Date ].[Date Key].&[20221231])
and then grouped by year

What I am currently getting is this

2021:Jun    5000
2021:Jul    3500
2021:Aug    4000

what I would like to get is this

2021  12500  (only representing the three months from this year)

The only other work around would be if I could have a single query that returned figures for three different date ranges. In other words, is it possible to return the following three queries from a single select statement and alias the results based on the year they are being pulled from?

select 
 sum({[Measures].[Sales]} )on columns
 ,NON EMPTY{([Date].[Month].[Month])} on rows

 from 
 [Cube]
 where ([Date].[Date Key].&[20210601] : [Date ].[Date Key].&[20211231])

 and
select 
 sum({[Measures].[Sales]} )on columns
 ,NON EMPTY{([Date].[Month].[Month])} on rows

 from 
 [Cube]
 where ([Date].[Date Key].&[20220101] : [Date ].[Date Key].&[20221231])

and
select 
 sum({[Measures].[Sales]} )on columns
 ,NON EMPTY{([Date].[Month].[Month])} on rows

 from 
 [Cube]
 where ([Date].[Date Key].&[20230101] : [Date ].[Date Key].&[20230303])

I think using "With" statement to create something similar to a subquery would work, but I'm not sure. Thanks for the help.

whytheq · Answer 1 · 2023-06-04T14:11:46.850

Using a WITH clause is exactly the right approach, as you suggested!

Slight syntax suggestion - measures should already have default aggregations set in the model so no need to wrap SUM around a measure.

WITH 
  MEMBER [Date].[Date Key].[combined] AS 
    Aggregate([Date].[Date Key].&[20220101] : [Date ].[Date Key].&[20221231]) 
SELECT 
  [Measures].[Claim Charge Amount] ON COLUMNS
 ,NON EMPTY 
    [combined] ON ROWS
FROM [Cube];

Example using MS AdvWrks cube.

SELECT 
  [Measures].[Sales Amount] ON COLUMNS
 ,NON EMPTY 
    [Date].[Calendar Quarter of Year].[Calendar Quarter of Year] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]:[Date].[Calendar Year].&[2014]

Returns this:

WITH 
  MEMBER [Date].[Calendar Year].[combined] AS 
    Aggregate([Date].[Calendar Year].&[2013]:[Date].[Calendar Year].&[2014])
SELECT 
  [Measures].[Sales Amount] ON COLUMNS
 ,NON EMPTY 
    [combined] ON ROWS
FROM [Adventure Works];

Returns this

I need an MDX query that operates like a Group by Statement

1 Answers1