Why does Excel Grand Total returns error (#VALUE!) when getting data from SSAS OLAP Cube (MDX)?

Question

I am having a problem with Grand Total in Excel connected to a OLAP cube. I have a measure that are correctly calculated in row level, but returns error in Grand Total. Its returning me an error #VALUE! .

EDIT: I have doing some experiences and verified that the error only appears when I have more then one year selected. If I have only one the data returns correct.

I try several alterations to the measure with no success.

The origin measure:

IIF([Tipo de Stock].[Tipo de Stock].CurrentMember IS [Tipo de Stock].[Tipo de Stock].&[7] OR, NULL,
SUM({NULL:[Data].[Por Mês].PrevMember},[Measures].[Movimento Valor])
)

Some alterations trying to solve the problem:

IIF([Tipo de Stock].[Tipo de Stock].CurrentMember IS [Tipo de Stock].[Tipo de Stock].&[7] OR IsEmpty([Measures].[Movimento Valor]), NULL,
SUM({NULL:[Data].[Por Mês].PrevMember},[Measures].[Movimento Valor])
)

IIF([Tipo de Stock].[Tipo de Stock].CurrentMember IS [Tipo de Stock].[Tipo de Stock].&[7], NULL,
SUM({NULL:[Data].[Por Mês].PrevMember},IsEmpty([Measures].[Movimento Valor],0))
)

IIF([Tipo de Stock].[Tipo de Stock].CurrentMember IS [Tipo de Stock].[Tipo de Stock].&[7], NULL,
IsEmpty(SUM({NULL:[Data].[Por Mês].PrevMember},[Measures].[Movimento Valor]),0)
)

I have try also with CoalesceEmpty instead IsEmpty.

Edit2 :I try to implement a SCOPE and it returns values in Grand Total, but the Values are the SUM of all rows. The idea is that the grand total has the first value.

SCOPE([Measures].[Stock Inicial Valor]); 
    SCOPE([Data].[Por Mês].[Mês].members);  
        THIS = SUM({NULL:[Data].[Por Mês].PrevMember},[Measures].[Movimento Valor]); 
    END SCOPE;      
END SCOPE;

Any one can help get to the right track.

Thanks for all your help.

If the grand total is the total at the bottom, what is on rows? Is it the Por Mês attribute such that the grand total is the All member on that attribute? — GregGalloway
Can you install this free Excel extension and run this feature to get an error message? olappivottableextensions.github.io/View-Error-Message.html — GregGalloway

GregGalloway GregGalloway · Accepted Answer · 2021-01-23T13:21:43

Please try the following:


IIF(
  [Tipo de Stock].[Tipo de Stock].CurrentMember IS [Tipo de Stock].[Tipo de Stock].&[7] 
  OR [Data].[Por Mês].PrevMember IS NULL,
  NULL,
  SUM({NULL:[Data].[Por Mês].PrevMember},[Measures].[Movimento Valor])
)

I suspect the problem is that {Null:Null} isn’t allowed so we are protecting against that in the new IIF statement.

If that’s not the problem then please install this free Excel extension and use this feature to get a proper error message to troubleshoot further.

Why does Excel Grand Total returns error (#VALUE!) when getting data from SSAS OLAP Cube (MDX)?

1 Answers