SSAS Grand Total based on the Last Dimension attribute from the Pivot. RRS feed

  • Question

  • Hello Team,

    Good Morning. With respect to the Our Business team, we do have requirement to show the EMPTY values as zero. With respect to that, we have applied below the MDX function & we were able to NULL value as zero.  

    But, We have come across an issue with Grand Total Calculation. As the Grand Total is still a LAST NON EMPTY metric and as It will be a NOT NULL value, The Grand Total still getting the Last Non Empty value.

    To resolve this issue, we have added a SCOPE to get the Grand Total calculation done separately. For Grand Total, We have taken the Last Child value of the respective Dimension member and we have tried to bring the value.

    With this Calculation, It is try to bring the LAST value of the Dimension data associated. For example, If the Dimension Reporting month is ending with 2017 December, It is trying to the bring the value of that and associating it as Grand Total, which is not expected.

    From the requirement perspective, If we selects the Reporting Year as 2013, The Grand Total should need to show the value as of December' 2013 not the 2017 Dec. Like wise, Depends on the Filter selection the respective Last Member to be choose and the calculation should be done accordingly.

    Could you please suggest if we do any option to take the Grand Total value based on the Filter Selection which we did?. If we are selecting the REporting Year as 2013, It has to take the value as December' 2013 and same for 2014, iT should be December' 2014 value.

    Please suggest your thoughts on the same.

    Thank you.



    Monday, August 1, 2016 5:29 PM


  • Hi Kumar,

    In this scenario, you can write SCOPE statement on all month members (or lower grain members), assign 0 for your measure. Please refer to my sample below:

    nonempty([Date].[Calendar].[Date].members,[Measures].[End of Day Rate])
    [Measures].[End of Day Rate]=0;
    end scope;


    Simon Hou
    TechNet Community Support

    Tuesday, August 2, 2016 7:52 AM