Calculated members in cross tab

[Former Member]

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

[JWiseman · Accepted solution]

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