0

i have some Measure, let's say [Measure].[m1]. Furthermnore there is i dimension [Dim1] with 3 different Hierachies [H1],[H2],[H3]. The Hierachies are just some kind of categories so the Sum of m1 on H1 equals Sum of m1 on H2 and H3 as well.

i want to compute overall ratios so i want to set a filter as WHERE expression. in the same statement i want to get the sum of all of m1 on All Items of Dim1 no matter what filter is aplied.

Problem is when i apply a filter everything is affected. i somehow want to be able to calculate the unaffected sum of m1 although a filter is applied.

select { [Measures].[m1] , <calculated sum of any item in Dim1 without the WHERE filter> } 
on 0
from MyCube

So in the statement above both columns shall print the same number since i did not applied a filter yet

select { [Measures].[m1] , <calculated sum of any item in Dim1 without the WHERE filter> } 
on 0
from MyCube
where [OtherDim].[Type].&[specialType]

In the statement above the first column should have filter applied but the second should still display the total amount of m1 on all of Dim1.

Is this possible without further Dimension just by using MDX?

tuxmania
  • 906
  • 2
  • 9
  • 28

1 Answers1

1

You need a calculated member for that:

with member [Measures].[Total] as ( [Measures].[m1], [OtherDim].[Type].[All Types] )
select { [Measures].[m1] , [Measures].[Total] } on 0
from MyCube
where [OtherDim].[Type].&[specialType]
nsousa
  • 4,448
  • 1
  • 10
  • 15
  • That works, Problem is When i have two filters like where ([OtherDim].[Type].&[specialType] , [OtherDim].[Type2].&[specialType2]) Then Total still filters the 2nd condition. [OtherDim].[All] Does not work, so i don't know how to bypass arbitary numbers of filters – tuxmania May 19 '14 at 11:13
  • well i am such a fool :( adding my other stuff to the tuple works like a charm, thank you sir! i.e. ([Measures].[m1], [OtherDim].[Type].[All Types], [OtherDim].[Type2].[All Types] ) – tuxmania May 19 '14 at 11:20
  • It's always the same principle. The Where tuple will affect everything in the query. If you want to use the All member in any given cell you need to explicitly state it. – nsousa May 19 '14 at 11:51