locked
Grand Total doesn't function properly RRS feed

  • Question

  • Hi.

     

    I have the attached MDX. When I run it, all values are shown properly. However, if I add Grand Total to my view, the Grand Total ignores one of the values and doesn't add it to the summation. I have removed the last line from the MDX and the Grand Total worked properly.

     

    iif([Hotels].[Hotels].currentmember is [Hotels].[Hotels].&[10],iif([PERIOD].[TRPYEAR].currentmember = [PERIOD].[TRPYEAR].&[2008], SUM({[Master Accounts].[Master Accounts].&[90-00-00-10-0353]},[MEASURES].[AMOUNT]),(440 * [numberofdays])),0)

     

    Please advice.

     

     

    “Total Available Rooms – Monthly” MDX:

     

    sum([Master Accounts].[Master Accounts].&[90-00-00-60-0001],[Measures].[Amount])

    +

     

     

    SUM({[Master Accounts].[Master Accounts].&[90-00-00-20-0035], [Master Accounts].[Master Accounts].&[90-00-00-20-0039], [Master Accounts].[Master Accounts].&[90-00-00-20-0041], [Master Accounts].[Master Accounts].&[90-00-00-20-0053]},[MEASURES].[AMOUNT])

    +

     

    SUM({[Master Accounts].[Master Accounts].&[90-00-00-30-0002]},[MEASURES].[AMOUNT])

     

    +

     

    SUM({[Master Accounts].[Master Accounts].&[90-00-00-40-0142]},[MEASURES].[AMOUNT])

     

    +

     

    SUM({[Master Accounts].[Master Accounts].&[90-00-00-50-0001]},[MEASURES].[AMOUNT])

     

     

     

    +

     

    iif([Hotels].[Hotels].currentmember is [Hotels].[Hotels].&[10],iif([PERIOD].[TRPYEAR].currentmember = [PERIOD].[TRPYEAR].&[2008], SUM({[Master Accounts].[Master Accounts].&[90-00-00-10-0353]},[MEASURES].[AMOUNT]),(440 * [numberofdays])),0)

     

    Monday, June 9, 2008 12:10 PM

Answers

  • As I was pondering why the iif statement would make a difference in the totals, one of our consultants happend to come by and I showed him your question.  He was kind enough to explain the problem and offer a solution.  Thanks, Steve!

     

    The iif statement relies on the currentmember.  However, when the query is resolved, the currentmember for [Hotels].[Hotels] is set to the [Grand Total] aggregate.  Since currentmember cannot be an aggregate (it has to be a single member), the statement resolves to "0".

     

    Instead of using the "[Hotels].[Hotels].currentmember" logic, try "iif(Intersect(hotels.hotels.&[10],Existing Hotels.Hotels.Members).Count = 1, iif([Period].....etc" instead.

     

    Tuesday, June 10, 2008 8:13 PM

All replies

  • Amin,

     

    Can you please post the entire MDX of the query from Professional?  Also, please point out the section of that MDX that you deleted to make the Grand Total appear as you desire.

     

    Thanks,

     

    -Joey

    Tuesday, June 10, 2008 3:18 PM
  • Hi Joey,

     

    The MDX from Proffesional is the below.

     

    WITH MEMBER [Hotels].[Hotels].[Hotels Consolidated].[ Grand Total] AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Hotels].[Hotels].[Hotels Consolidated].CHILDREN }, [Hotels].[Hotels] ) } }, { [Hotels].[Hotels].[Hotels Consolidated].CHILDREN } ) )', SOLVE_ORDER = 1000 SELECT { [Period].[Period].DEFAULTMEMBER } ON COLUMNS ,

    { { [Hotels].[Hotels].[Hotels Consolidated].CHILDREN }, ( [Hotels].[Hotels].[Hotels Consolidated].[ Grand Total] ) } ON ROWS

    FROM [Zara Cube]

    WHERE ( [Measures].[Total Available Rooms - Monthly] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

     

    The line that I have removed and everything worked properly is:

     

    +

     

    iif([Hotels].[Hotels].currentmember is [Hotels].[Hotels].&[10],iif([PERIOD].[TRPYEAR].currentmember = [PERIOD].[TRPYEAR].&[2008], SUM({[Master Accounts].[Master Accounts].&[90-00-00-10-0353]},[MEASURES].[AMOUNT]),(440 * [numberofdays])),0)

    Tuesday, June 10, 2008 3:44 PM
  • Does it work if you remove the EXISTING keyword from the MDX and rerun the query?

    Tuesday, June 10, 2008 7:27 PM
  • As I was pondering why the iif statement would make a difference in the totals, one of our consultants happend to come by and I showed him your question.  He was kind enough to explain the problem and offer a solution.  Thanks, Steve!

     

    The iif statement relies on the currentmember.  However, when the query is resolved, the currentmember for [Hotels].[Hotels] is set to the [Grand Total] aggregate.  Since currentmember cannot be an aggregate (it has to be a single member), the statement resolves to "0".

     

    Instead of using the "[Hotels].[Hotels].currentmember" logic, try "iif(Intersect(hotels.hotels.&[10],Existing Hotels.Hotels.Members).Count = 1, iif([Period].....etc" instead.

     

    Tuesday, June 10, 2008 8:13 PM