Hi,
This is actually a very common problem with multiselects but there are ways to solve it. It's just that the performance might suffer as the only safe method is to aggregate from the leaf level of the time dimension if the user can pick any members in the calendar hierarchy eg (2 months or 2 dates or even a combination).
Anyway here are the examples on Adventure Works R2:
1. As a lot of clients (excel for instance) generate these kind of queries using subselects you have to use a combination of dynamic sets (2008+) with existing instruction applied to the set and aggregate using generate() function (see measure agg1) or use SUM over a constructed tuple (see measure agg2).
Dynamic sets can be forced to evaluate in the context of subselects and slicers.
Query 1 (please note that when subselects are used the aggregations on parent members are totals of child members in subselect - thus years on rows are just used to show results for comparison):
with
set [months] as
existing [Date].[Calendar].[Month]
member [Measures].[Internet Sales Amount LY agg1] as
Aggregate
(
Generate
(
existing [months]
,{parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)}
)
,[Measures].[Internet Sales Amount]
), Format_String ="Currency"
member [Measures].[Internet Sales Amount LY agg2] as
SUM
(
existing [months]
,
(
parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount]
)
), Format_String ="Currency"
select
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount LY agg1],
[Measures].[Internet Sales Amount LY agg2]
} on 0
,
[Date].[Calendar].[Calendar Year]
on 1
from
(
select
{ [Date].[Calendar].[Month].&[2007]&[1] : [Date].[Calendar].[Month].&[2007]&[3]}
+
{ [Date].[Calendar].[Month].&[2006]&[1] : [Date].[Calendar].[Month].&[2006]&[3]}
on 0
from [Adventure Works]
)
2. Multiselect in slicer (where part) - Here dynamic sets are not used as it is enough to apply the existing clause directly w/o using a dynamic set. This is how multiselect in slicer behave. Note also that the above example (1) works also with multiselects in slicer.
Query 2:
with
member [Measures].[Internet Sales Amount LY agg1] as
Aggregate
(
Generate
(
existing [Date].[Calendar].[Month]
,{parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)}
)
,[Measures].[Internet Sales Amount]
), Format_String ="Currency"
member [Measures].[Internet Sales Amount LY agg2] as
SUM
(
existing [Date].[Calendar].[Month]
,
(
parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount]
)
), Format_String ="Currency"
select
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount LY agg1],
[Measures].[Internet Sales Amount LY agg2]
} on 0
from
[Adventure Works]
where
(
{
[Date].[Calendar].[Month].&[2006]&[1]
:
[Date].[Calendar].[Month].&[2006]&[3]
}
)
Just a final note - I have found another optimization with this approach that I intend to blog about soon.
Oh, you might also post this MDX questions on MSDN Analysis Services forum as there is a lot of activity and I usually "help" there.
Hope that helps 🙂
Cheers,
Hrvoje Piasevoli
Hrvoje Piasevoli