To get a named set, for example the years between 2015-2018 (four values -- 2015, 2016, 2017, 2018) I can do:
WITH set [2015-2018] as {[Season].[Season].[Season].&[2015]:[Season].[Season].[Season].&[2018]}
SELECT {
[Measures].[Wins]
} ON COLUMNS, {
[2015-2018]
} ON ROWS
FROM [Nfl2]
And it gives me:
Wins
2015 256
2016 254
2017 256
2018 254
How would I create a single grouped set (or whatever it is termed in MDX) where I could do the following:
Wins
2015 256
2016 254
2017 256
2018 254
2015-2018 1020 *** how to do this in mdx?
In Mdx we don't say Grouped set, but members, your aim is to create a member that holds the sum of wins from 2015 to 2018. In order to achieve that you need to specify an aggregation function for the calculated member [2015-2018] like this:
WITH
Member [Season].[Season].[All].[2015-2018] AS SUM({[Season].[Season].&[2015]:[Season].[Season].&[2018]})
set [2015-2018-All] as {[Season].[Season].&[2015]:[Season].[Season].&[2018],[2015-2018] }
SELECT {
[Measures].[Wins]
} ON COLUMNS, {
[2015-2018-All]
} ON ROWS
FROM [Nfl2]