1 Reply Latest reply: Dec 16, 2010 2:41 AM by Jamie Wiseman RSS

Calculated members in cross tab

alessandro cherchi
Currently Being Moderated

Hi all,

 

I have a profit and losses in a cross tab created by a group and I need to add some rows to have the totals.

 

For example

 

Revenue 1

Revenue 2

Revenue 3

Total Revenue

Cost 1

Cost 2

Total Costs

....

 

For total revenue I used a calculated members with that formula:

GridValueAt(GetRowPathIndexOf("Revenue 1"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Revenue 2"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Revenue 3"), CurrentColumnIndex, CurrentSummaryIndex)

 

but the problem is that if I haven't for example any data for revenue 2 an error occurs -->argument # 1of "gridvalueat" is out of range.

 

It's possible to check if the argument exist??

 

 

thanks

  • Re: Calculated members in cross tab
    Jamie Wiseman
    Currently Being Moderated

    hi alessandro,

     

    i just found your forum post in the unanswered list...hopefully you've already found the answer for this.

     

    GetRowPathIndexOf is looking for the text in a specific cell and not the actuall Row or Column Name.

    When it doesn't find it, its output is -1...so you can use that in your formula to ensure that it doesn't break.

     

    for example, if you have a cross-tab that looks like

     

    Canada        1006

    USA             5555

    Mexico         2331

     

    GetRowPathIndexOf("Mexico") = 2 (as the index starts at 0 for Canada)

     

    GetRowPathIndexOf("Brazil") = -1

     

    hope this helps,

    jamie