Search code examples
casemdx

Using CASE statement in MDX for data from different hierarchy level

I have the following hierarchy created:

enter image description here

I have written this particular query to get my result:

WITH MEMBER [Ecotassa] AS 
CASE 
   WHEN [Tempo].[Anno].CurrentMember.MemberValue = '2011' THEN '9114646'
   WHEN [Tempo].[Anno].CurrentMember.MemberValue = '2012' THEN '8787551.65'
   WHEN [Tempo].[Anno].CurrentMember.MemberValue = '2013' THEN '8435651.26'
   WHEN [Tempo].[Anno].CurrentMember.MemberValue = '2014' THEN '8917336.39'
   WHEN [Tempo].[Anno].CurrentMember.MemberValue = '2015' THEN '9465533.37'
   ELSE  NULL
END

SELECT 
{ [Measures].[Dato], [Ecotassa] } ON COLUMNS,
{ ([Tempo].[Periodo].[Mese Anno].MEMBERS) } ON ROWS

FROM [AGRO]
WHERE ([TipoMisura].[Tipo Misura].&[1], [TipoAnno].[Tipo Anno].&[2],
[TipoPeriodo].[Tipo Periodo].&[2],
[Mercato].[Mercato].&[1],
[Aziende].[Descrizione Codice Azienda].&[100 - BASF]);

The result that I am getting is:

enter image description here

This is not my desired result, for Ecotassa, I need to get the result for that particular year. But I am getting null.

Where am I going wrong with the query?

Solution

  • This [Tempo].[Anno].CurrentMember is equal to All in your script hence it is choosing NULL in the CASE statement. I'd also be tempted to use [Tempo].[Periodo] for year and also for date: rather than [Tempo].[Anno]:

    I've also thrown [Measures].[y] into the select so we can see if it is working ok.

    So try this:

    WITH 
MEMBER [Measures].[y] AS 
  EXISTS(
    [Tempo].[Periodo].[Anno].members,
    [Tempo].[Periodo].CurrentMember
  ).ITEM(0).ITEM(0).Member_Caption
MEMBER [Measures].[Ecotassa] AS 
  CASE 
     WHEN [Measures].[y] = '2011' THEN '9114646'
     WHEN [Measures].[y] = '2012' THEN '8787551.65'
     WHEN [Measures].[y] = '2013' THEN '8435651.26'
     WHEN [Measures].[y] = '2014' THEN '8917336.39'
     WHEN [Measures].[y] = '2015' THEN '9465533.37'
   ELSE  NULL
  END
SELECT 
{ 
   [Measures].[Dato]
  ,[Measures].[Ecotassa]
  ,[Measures].[y] 
 } ON COLUMNS,
[Tempo].[Periodo].[Mese Anno].MEMBERS ON ROWS
FROM [AGRO]
WHERE (
   [TipoMisura].[Tipo Misura].&[1]
  ,[TipoAnno].[Tipo Anno].&[2]
  ,[TipoPeriodo].[Tipo Periodo].&[2]
  ,[Mercato].[Mercato].&[1]
  ,[Aziende].[Descrizione Codice Azienda].&[100 - BASF]
 );

    test script

    What does this return?

    WITH 
MEMBER [Measures].[EcotassaTEST] AS 
  [Tempo].[Anno].CurrentMember.MemberValue

SELECT 
{ [Measures].[Dato], [EcotassaTEST] } ON COLUMNS,
{ ([Tempo].[Periodo].[Mese Anno].MEMBERS) } ON ROWS

FROM [AGRO]
WHERE ([TipoMisura].[Tipo Misura].&[1], [TipoAnno].[Tipo Anno].&[2],
[TipoPeriodo].[Tipo Periodo].&[2],
[Mercato].[Mercato].&[1],
[Aziende].[Descrizione Codice Azienda].&[100 - BASF]);