PDA

View Full Version : Filter and Except in Query MDX



snake64
01-04-2013, 04:13 PM
Hi everyone,

well, At first I want to explain my cube, because maybe you can understand better the problem:

The cube Have a diversals dimension, but my MDX works with only one. this Dimension have one hierarchie, and this have many levels...The structure for this dimension is:

[Dimension].[empresa].[clase1].[clase2].[clase3].[numero_cuenta].

the level "[clase3]" can have many "[numero_cuenta]", for example:
[clase3].[230503, 230505, 230506, ...]. So, this MDX have to get every number of [numero_cuenta], except the 230503; and display the measures consolidate in the level [clase3], something like this:
9956

the problem is when I use de function EXCEPT, this function doesn't work because display the subtotal of measures including numero_cuenta 230503. this is the MDX query:


with member [periodo].[ano_anterior] as '(Sum({([periodo].FirstChild : ParallelPeriod([periodo].[periodo], 12, [periodo].[201205]))}) / 21809.84)'
member [periodo].[ano_actual] as '(Sum({([periodo].FirstChild : [periodo].[201205])}) / 22620.80)'
select {[periodo].[ano_actual], [periodo].[ano_anterior]} ON COLUMNS,
Except([clase_cuenta].[L_PARQUES].[PATRIMONIO].[PATRIMONIO].Children, {[clase_cuenta].[L_PARQUES].[PATRIMONIO].[PATRIMONIO].[Utilidad del Ejercicio].[230503]}) ON ROWS
from [balance]
where [numero_cuenta]

when I see this problem with this function, I try with the function Filter, but also didnt work. this is the MDX with the function Filter:

with member [periodo].[ano_anterior] as '(Sum({([periodo].FirstChild : ParallelPeriod([periodo].[periodo], 12, [periodo].[201205]))}) / 21809.84)'
member [periodo].[ano_actual] as '(Sum({([periodo].FirstChild : [periodo].[201205])}) / 22620.80)'
select {[periodo].[ano_actual], [periodo].[ano_anterior]} ON COLUMNS,
Filter([clase_cuenta].[L_PARQUES].[PATRIMONIO].[PATRIMONIO].Children, [clase_cuenta].[L_PARQUES].[PATRIMONIO].[PATRIMONIO].[Utilidad del Ejercicio].CurrentMember <> 230503) ON ROWS
from [balance]
where [numero_cuenta]
The strange here when i put the symbol "no equal to"(<>), brings me all the accounts including 230503, but when I put the Symbol "equal to"(=) brings me NULL...

So I dont know what else can do to exclude this account (230503).

Please Helpme, I will be Very Grateful