MDX Query - How to replace null with 0 in crossjoin

• Cristiana Dias

  Grasshopper

  Points: 12

  More actions

  September 20, 2019 at 10:43 am

  #3681770

  Hi guys,

  I need to replace nulls with 0 in a crossjoin result.

  This is my query:

  --------------------------------------------------------------------------------------------------------------------------

  WITH

  MEMBER [Measures].[X] AS

  ([Indicador].[Indicador Descrição].&[X],[Measures].[MONTANTE])

  MEMBER [Measures].[Y] AS

  ([Indicador].[Indicador Descrição].&[Y],[Measures].[MONTANTE])

  SELECT

  NON EMPTY {[Measures].[X],[Measures].[Y]} ON COLUMNS,

  NON EMPTY{[Classificador Orgânico].[A].children*

  [Programas e Medidas].[A].children*

  [Classificador Económico Despesa].[A].children*

  [Classificador Económico Despesa]..children*

  [Classificador Económico Despesa].[C].children*

  [Classificador Económico Despesa Rubricas].[A].children*

  [Classificador Económico Despesa Rubricas]..children*

  [Fonte de Financiamento].[A].children} ON ROWS

  FROM [Cube]

  WHERE ([Calendário].[ANO].&[2018],[Calendário].[COD MES].&[04])

  -----------------------------------------------------------------------------------------------------------------

  I already try to use coalesceempty in the calculated members but replaces all the nulls before the NonEmptyCrossjoin (the query doesn't even answer).

  The ideal solution is to replace nulls only after the NonEmptyCrossjoin .What is the proper way to achieve that?

  Thanks!

  Regards, Cristiana Dias

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  September 21, 2019 at 11:10 am

  #3682238

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.