MS SQL Server OLAP - Getting Measure Total Without One Dimension Value (EXCEPT?)

Author

Getting Measure Total Without One Dimension Value (EXCEPT?)

Kevin

2006-01-26, 4:58 pm

I'm running into a problem computing a measure total. Instead of
computing the measure across all values in the dimension or individual
values in the dimension I'd like to compute it across a subset of the
values in the dimension. For example, if I have a time dimension I'd
like to be able to compute the values for January and February without
March. It appears that EXCEPT should allow me to do that but it simply
leaves out one of the dimension values from the result set and then
returns the full total.

One workaround that I came up with was to create a hierarchy for the
dimension by introducing a snowflake dimension: "Dimension Group" would
contain the individual members of "Dimension" and also include the
subsets I wanted. However, this is very messy and doesn't work well for
all dimensions. Is there another way to do this with MDX?

Thanks,
Kevin.

Deepak Puri

2006-01-26, 4:58 pm

Will VisualTotals() serve your purpose, like:
[color=darkred]
select {[Measures].[Unit Sales]} on 0,
VisualTotals(Except(

Descendants([Time].[1997].[Q1]),
{[Time].[1997].[Q1].[3]})) on 1
from Sales[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Kevin

2006-01-26, 4:58 pm

Deepak Puri wrote:
> Will VisualTotals() serve your purpose, like:
>
> select {[Measures].[Unit Sales]} on 0,
> VisualTotals(Except(

> Descendants([Time].[1997].[Q1]),
> {[Time].[1997].[Q1].[3]})) on 1
> from Sales

Thanks, while I was waiting for a reply I found this solution:

WITH MEMBER [Time].[1997].[Q1 Jan and Feb]
'[Time].[1997].[Q1].[1] + [Time].[1997].[Q1].[2]'
SELECT {[Measures].[Unit Sales]} ON COLUMNS,
{[Time].[1997].[Q1].[1], [Time].1997.[Q1].[2],
[Time].[1997].[Q1 Jan and Feb]} ON ROWS
FROM [Sales]

Is VisualTotals better or is my solution essentially the same thing?
They seem to come up with the same results...

Thanks,
Kevin.

Deepak Puri

2006-01-26, 8:23 pm

Hi Kevin,

It depends on your scenario - VisualTotals() substitutes the necessary
calculated members dynamically, so no calculated members need to be
explicitly created.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links