Case syntax MDX - SAP 4.0 OLAP connection to SQL 2008

[florencialimongi]

Hi, I'm trying to do a case-when syntax, but it's not working. No result is displayed when doing a query.

Any ideas? Thanks in advance!

The syntax I'm using is:

CASE [Concepto Cascada].[Concepto Key]

   WHEN [Concepto Cascada].[Concepto Key].&[1] THEN @Select(Vision1\BN\Real\BN - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[2] THEN @Select(Vision1\Impuestos\Real\Impuestos - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[3] THEN @Select(Vision1\BAI\Real\BAI - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[4] THEN @Select(Vision1\Otros Resultados\Real\Otros Resultados - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[5] THEN @Select(Vision1\MEN\Real\MEN - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[6] THEN @Select(Vision1\Costes\Real\Costes - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[7] THEN @Select(Vision1\OGA\Real\OGA - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[8] THEN @Select(Vision1\Personal\Real\Personal - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[9] THEN @Select(Vision1\Amortizaciones\Real\Amortizaciones - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[10] THEN @Select(Vision1\MON\Real\MON - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[11] THEN @Select(Vision1\PI\Real\PI - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[14] THEN @Select(Vision1\MOB\Real\MOB - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[15] THEN @Select(Vision1\Comisiones\Real\Comisiones - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[16] THEN @Select(Vision1\OREX\Real\OREX - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[17] THEN @Select(Vision1\ROF\Real\ROF - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[18] THEN @Select(Vision1\MIB\Real\MIB - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[19] THEN @Select(Vision1\Depositos\Real\Depositos - Importe Real)

   WHEN [Concepto Cascada].[Concepto Key].&[20] THEN @Select(Vision1\Creditos\Real\Creditos - Importe Real)

    ELSE @Select(Real\Importe Real)

END

[marc_daniau · Accepted solution]

here is a sample MDX query with a CASE

WITH

MEMBER [Measures].[Summer_Qty] AS

CASE [Date].[Month of Year].CurrentMember

   WHEN [Date].[Month of Year].&[6] THEN [Measures].[Order Quantity]

   WHEN [Date].[Month of Year].&[7] THEN [Measures].[Order Quantity]

   WHEN [Date].[Month of Year].&[8] THEN [Measures].[Order Quantity]

   ELSE Null

END, format_string = "#,##0"

SELECT

{[Measures].[Order Quantity] , [Measures].[Summer_Qty]} ON COLUMNS,

Hierarchize([Date].[Month of Year].[Month of Year].Members)

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Adventure Works]

[florencialimongi · Accepted solution]

Marc:

Thanks for your reply!! The problem in that I can't create the query, i can only create the objects, because I'm working with Information Design Tool.

In the THEN sentence i need to put an object created in BO that contains an AGGREGATE function. Could you give me an example of how can i achieve this?

Thanks again!!

Florencia

[marc_daniau · Accepted solution]

The syntax remains the same except for the @select you are using.

If you go to the IDT query panel and view the script you will see the MDX query and can compare with the given example. My guess is that your are missing .CurrentMember in your expression.

[florencialimongi · Accepted solution]

Hi Marc, thanks for your reply.

I've checked it with Current Member. It is working for some dates but for other it doesn't.

The problem is that for some date hierarchies the query returns the correct value, but for the Month hierarchy it doesn't return any value.

Here's the screenshot and the SQL statement:

The SQL statement is:

WITH

          MEMBER [Measures].[BAI - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[15],[Linea_Escalar].[Linea Key].&[16]},[Measures].[Importe Real])

'

          MEMBER [Measures].[BN - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[17],[Linea_Escalar].[Linea Key].&[18]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Impuestos - Importe Real] AS 'AGGREGATE([Linea_Escalar].[Linea Key].&[59],[Measures].[Importe Real])

'

          MEMBER [Measures].[Otros Resultados - Importe Real] AS 'AGGREGATE([Linea_Escalar].[Linea Key].&[50],[Measures].[Importe Real])

'

          MEMBER [Measures].[MEN - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[10],[Linea_Escalar].[Linea Key].&[11]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Costes - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[12],[Linea_Escalar].[Linea Key].&[13], [Linea_Escalar].[Linea Key].&[14]},[Measures].[Importe Real])

'

          MEMBER [Measures].[OGA - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[38],[Linea_Escalar].[Linea Key].&[43], [Linea_Escalar].[Linea Key].&[47]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Personal - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[37],[Linea_Escalar].[Linea Key].&[42], [Linea_Escalar].[Linea Key].&[46]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Amortizaciones - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[39],[Linea_Escalar].[Linea Key].&[44], [Linea_Escalar].[Linea Key].&[48]},[Measures].[Importe Real])

'

          MEMBER [Measures].[MON - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[8],[Linea_Escalar].[Linea Key].&[9]},[Measures].[Importe Real])

'

          MEMBER [Measures].[PI - Importe Real] AS 'AGGREGATE([Linea_Escalar].[Linea Key].&[8],[Measures].[Importe Real])

'

          MEMBER [Measures].[MOB - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[4],[Linea_Escalar].[Linea Key].&[5], [Linea_Escalar].[Linea Key].&[6],[Linea_Escalar].[Linea Key].&[7]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Comisiones - Importe Real] AS 'AGGREGATE([Linea_Escalar].[Linea Key].&[22],[Measures].[Importe Real])

'

          MEMBER [Measures].[OREX - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[25],[Linea_Escalar].[Linea Key].&[32], [Linea_Escalar].[Linea Key].&[33]},[Measures].[Importe Real])

'

          MEMBER [Measures].[ROF - Importe Real] AS 'AGGREGATE([Linea_Escalar].[Linea Key].&[27],[Measures].[Importe Real])

'

          MEMBER [Measures].[MIB - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[1],[Linea_Escalar].[Linea Key].&[2],[Linea_Escalar].[Linea Key].&[3]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Depositos - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[254],[Linea_Escalar].[Linea Key].&[255], [Linea_Escalar].[Linea Key].&[302],[Linea_Escalar].[Linea Key].&[303], [Linea_Escalar].[Linea Key].&[256],[Linea_Escalar].[Linea Key].&[257], [Linea_Escalar].[Linea Key].&[304],[Linea_Escalar].[Linea Key].&[305], [Linea_Escalar].[Linea Key].&[97]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Creditos - Importe Real] AS 'AGGREGATE({[Linea_Escalar].[Linea Key].&[235],[Linea_Escalar].[Linea Key].&[283], [Linea_Escalar].[Linea Key].&[236],[Linea_Escalar].[Linea Key].&[284], [Linea_Escalar].[Linea Key].&[237],[Linea_Escalar].[Linea Key].&[285], [Linea_Escalar].[Linea Key].&[238],[Linea_Escalar].[Linea Key].&[240], [Linea_Escalar].[Linea Key].&[286], [Linea_Escalar].[Linea Key].&[288]},[Measures].[Importe Real])

'

          MEMBER [Measures].[Indicador prueba] AS 'CASE [Concepto Cascada].[Concepto Key].CurrentMember

   WHEN [Concepto Cascada].[Concepto Key].&[1] THEN [Measures].[BN - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[2] THEN [Measures].[Impuestos - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[3] THEN [Measures].[BAI - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[4] THEN [Measures].[Otros Resultados - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[5] THEN [Measures].[MEN - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[6] THEN [Measures].[Costes - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[7] THEN [Measures].[OGA - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[8] THEN [Measures].[Personal - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[9] THEN [Measures].[Amortizaciones - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[10] THEN [Measures].[MON - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[11] THEN [Measures].[PI - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[14] THEN [Measures].[MOB - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[15] THEN [Measures].[Comisiones - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[16] THEN [Measures].[OREX - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[17] THEN [Measures].[ROF - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[18] THEN [Measures].[MIB - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[19] THEN [Measures].[Depositos - Importe Real]

   WHEN [Concepto Cascada].[Concepto Key].&[20] THEN [Measures].[Creditos - Importe Real]

       ELSE [Measures].[Importe Real]

END

'

SELECT

          NON EMPTY {[Measures].[BAI - Importe Real],[Measures].[Indicador prueba]} ON COLUMNS,

          NON EMPTY Hierarchize(Subset(Crossjoin([Concepto Cascada].[Concepto Key].[Concepto Key].Members, {[Fecha].[Fecha].[All],[Fecha].[Fecha].[AÑO].&[2.013E3],[Fecha].[Fecha].[SEMESTRE].&[2.0131E4],[Fecha].[Fecha].[SEMESTRE].&[2.0132E4],[Fecha].[Fecha].[TRIMESTRE].&[2.0131E4],[Fecha].[Fecha].[MES].&[2.01301E5],[Fecha].[Fecha].[MES].&[2.01302E5],[Fecha].[Fecha].[MES].&[2.01303E5],[Fecha].[Fecha].[MES].&[2.01308E5],[Fecha].[Fecha].[MES].&[2.01307E5],[Fecha].[Fecha].[MES].&[2.01309E5],[Fecha].[Fecha].[MES].&[2.01312E5],[Fecha].[Fecha].[MES].&[2.01311E5],[Fecha].[Fecha].[MES].&[2.0131E5],[Fecha].[Fecha].[MES].&[2.01304E5],[Fecha].[Fecha].[MES].&[2.01306E5],[Fecha].[Fecha].[MES].&[2.01305E5]}), 0, 5000))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM( SELECT Filter([Concepto Cascada].[Concepto Key].[Concepto Key].Members, ([Concepto Cascada].[Concepto Key].CurrentMember.Properties("MEMBER_CAPTION") = "3")) ON COLUMNS

FROM( SELECT {[Oficina_Comercial_JOC01].[Oficina Comercial JOC01].[All]} ON COLUMNS

FROM( SELECT {[Entidad_Juridica_JEJ01].[Entidad Juridica].[All]} ON COLUMNS

FROM( SELECT {[Area_Negocio_JAN01].[Area de Negocio].[All]} ON COLUMNS

FROM( SELECT {[Fecha].[Fecha].[All]} ON COLUMNS

FROM [Escalar])))))

[florencialimongi · Accepted solution]

Hi Marc, thanks for your reply.

I've checked it again and a clearer picture of the bug is the following:

Thanks again!!

[marc_daniau · Accepted solution]

I would suggest you preview the keys of the members and double check that the "WHEN" condition is using the proper value of the key.

[florencialimongi · Accepted solution]

Hi Marc,

The behaviour of the case-when function seems to be erratic, sometimes it's returning the correct values and sometimes it's returning null instead.

For this reason, i've changed it to an iif-else clause.

The iif-else clause works ok if i have a maximum of 3 nested iif-else clauses.

When i nested the fourth one, the query returns some values right, but i'm getting some nulls that were supose to be valid figures.

Do you have any idea if there's a maximum of nested if-clauses i can use?

E.g.:

measure 1:

iif([Concepto_Cascada].[Concepto Cascada] = [Concepto_Cascada].[Concepto Cascada].&[BAI] , @Select(Vision1\BAI\Real\BAI - Importe Real),iif([Concepto_Cascada].[Concepto Cascada] = [Concepto_Cascada].[Concepto Cascada].&[Amortizaciones], @Select(Vision1\Amortizaciones\Real\Amortizaciones - Importe Real),iif([Concepto_Cascada].[Concepto Cascada] = [Concepto_Cascada].[Concepto Cascada].&[Beneficio Neto] , @Select(Vision1\BN\Real\BN - Importe Real),-1)))

measure 2:

IIF([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Beneficio Neto], AGGREGATE({[Linea_Escalar].[Linea Escalar].&[17],[Linea_Escalar].[Linea Escalar].&[18]},[Measures].[Importe Real]), IIF([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[BAI],AGGREGATE({[Linea_Escalar].[Linea Escalar].&[15],[Linea_Escalar].[Linea Escalar].&[16]},[Measures].[Importe Real]),IIF([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Amortizaciones],AGGREGATE({[Linea_Escalar].[Linea Key].&[39],[Linea_Escalar].[Linea Key].&[44], [Linea_Escalar].[Linea Key].&[48]},[Measures].[Importe Real]),IIF([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Comisiones],AGGREGATE([Linea_Escalar].[Linea Key].&[22],[Measures].[Importe Real]),null))))

[marc_daniau · Accepted solution]

Could you sketch with a simple example what you are trying to achieve, with the input data and the expected output? Ideally using Adventure Works demo cube so that we have the same reference.

[florencialimongi · Accepted solution]

Hi Marc,

Thanks for your reply. I'm sorry I couldn't do the demo you asked me, but i believe i've found the cause of the error.

When i create a mesure with a nested if-else clause with measures that are already created in the cube, the results are ok.

If-else clasue:

Here's the picture with the results:

The error arises when i use measures created in the program Information Design Tool (IDT).

The measures have the following structure:

AGGREGATE({[Linea_Escalar].[Linea Escalar].&[17],[Linea_Escalar].[Linea Escalar].&[18]},[Measures].[Importe Real])

So the measure i create in IDT with the if-else clause has the following structure:

iif([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Amortizaciones],AGGREGATE({[Linea_Escalar].[Linea_Escalar].&[39],[Linea_Escalar].[Linea_Escalar].&[44], [Linea_Escalar].[Linea_Escalar].&[48]},[Measures].[Importe Real]),iif([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[BAI],AGGREGATE({[Linea_Escalar].[Linea Escalar].&[15],[Linea_Escalar].[Linea Escalar].&[16]},[Measures].[Importe Real]),iif([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Beneficio Neto],AGGREGATE({[Linea_Escalar].[Linea Escalar].&[17],[Linea_Escalar].[Linea Escalar].&[18]},[Measures].[Importe Real]),iif([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Comisiones],AGGREGATE([Linea_Escalar].[Linea_Escalar].&[22],[Measures].[Importe Real]),iif([Concepto_Cascada].[Concepto Cascada]=[Concepto_Cascada].[Concepto Cascada].&[Costes],AGGREGATE({[Linea_Escalar].[Linea_Escalar].&[12],[Linea_Escalar].[Linea_Escalar].&[13], [Linea_Escalar].[Linea_Escalar].&[14]},[Measures].[Importe Real]),null)))))

When i write this if-else clause i have two options:

a. i can use the AGGREGATE function directly in the if clause: AGGREGATE({[Linea_Escalar].[Linea Escalar].&[17],[Linea_Escalar].[Linea Escalar].&[18]},[Measures].[Importe Real])

b. i can call the preciously created measure that contains the AGGREGATE funcion inside:

@Select(Vision1\BN\Real\BN - Importe Real)

I've tried with both options and neither work.

So, when i do the query with this if-else clause, the result is always null:

Do you have any idea of why this is happening and how could i make it work right?

Thanks again,

Florencia

[florencialimongi · Accepted solution]

I've made it work, with the following code:

CASE @Prompt('Concepto','A','Concepto_Cascada\Concepto Cascada',Mono,Free,Not_Persistent,,User:-1)

WHEN 19 THEN @Select(Vision1\BN\PEA\BN - Importe Pea)

WHEN 17 THEN @Select(Vision1\BAI\PEA\BAI - Importe Pea)

WHEN 15 THEN @Select(Vision1\MEN\PEA\MEN - Importe Pea)

WHEN 10 THEN @Select(Vision1\MON\PEA\MON - Importe Pea)

WHEN 8 THEN @Select(Vision1\MOB\PEA\MOB - Importe Pea)

WHEN 4 THEN @Select(Vision1\MIB\PEA\MIB - Importe Pea)

WHEN 18 THEN @Select(Vision1\Impuestos\PEA\Impuestos - Importe Pea)

WHEN 16 THEN @Select(Vision1\Otros Resultados\PEA\Otros Resultados - Importe Pea)

WHEN 11 THEN @Select(Vision1\Costes\PEA\Costes - Importe Pea)

WHEN 9 THEN @Select(Vision1\PI\PEA\PI - Importe Pea)

WHEN 5 THEN @Select(Vision1\Comisiones\PEA\Comisiones - Importe Pea)

WHEN 1 THEN @Select(Vision1\Depositos\PEA\Depositos - Importe Pea)

WHEN 2 THEN @Select(Vision1\Creditos\PEA\Creditos - Importe Pea)

WHEN -1 THEN AGGREGATE([Linea_Escalar].[Linea Escalar].&[5], [Measures].[Importe Pea])

WHEN 3 THEN @Select(Vision1\Otros aux\PEA\aux_Otros - Importe Pea) - @Select(Vision1\Depositos\PEA\Depositos - Importe Pea) - @Select(Vision1\Creditos\PEA\Creditos - Importe Pea)

WHEN 7 THEN @Select(Vision1\OREX\PEA\OREX - Importe Pea)

WHEN 6 THEN @Select(Vision1\ROF\PEA\ROF - Importe Pea)

WHEN 12 THEN @Select(Vision1\Personal\PEA\Personal - Importe Pea)

WHEN 13 THEN @Select(Vision1\OGA\PEA\OGA - Importe Pea)

WHEN 14 THEN @Select(Vision1\Amortizaciones\PEA\Amortizaciones - Importe Pea)

ELSE 0

END

Thanks for your help, Marc.

Regards,

Florencia