Search code examples
excelssasmdx

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

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.

Solution

  • 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.