How to reference a header title in a calculated member formula?

[Former Member]

Hi, I'm attempting to create the following crosstab report.

Currently there are two calculated member fields "On Hand" and "My Total"

"On Hand" is supposed to take the "My Total" value from the previous column as a brought forward amount.

I'm using this as the calculation formula for "On Hand"

if CurrentColumnIndex = 0 then

0

else

GridValueAt(6, CurrentColumnIndex -1, 0)

However it returns the values from the default "Total" field below.

Changing the row index to 5 uses the figures in the "Sold" row.

How can I reference the correct row?

Thanks in advance

Colin

[abhilash_kumar]

Hi Colin,

Try this please:

1) Right-click the 'My Total' header > Calculated Member > Edit RowValue formula > Type in 'My Total'

2) Modify the Calculation Formula as:

if CurrentColumnIndex = 0 then

     0

else

     GridValueAt(GetIndexOf("field_used_as_second_field_in_row","My Total"), CurrentColumnIndex -1, 0)

This: "field_used_as_second_field_in_row" should be in double-quotes and not curly braces.

Let me know if this works.

-Abhilash

[Former Member]

Hi Abhilash,

Thanks for your quick reply.

I'm afraid I get the following error:

[abhilash_kumar]

I can't say for sure what's happening without looking at the report.

Would you be able to send me a copy on my e-mail or attach it here?

-Abhilash

[Former Member]

Hi Abhilash,

That's very kind of you thanks. Here it is:

SLIPPAGE CROSS TAB - Copy.txt.zip

[abhilash_kumar]

It seems that the 'GetIndexOf' doesn't work inside 'Calculation Formula' - if it did we wouldn't need to hardcode an index. Anyway, please find attached the updated report.

I made the 'My Total' column always appears before 'Total' and the 'On Hand' calculation looks right as well.

-Abhilash

SLIPPAGE CROSS TAB_updated.txt.zip

[Former Member]

Hi Abhilash,

Thank you very much, that works.

My only remaining issue is being able to have those calculated fields work for each group.

I've been able to change the insertion formula so that they appear in each group.

But as the calculations are hard coded I'm having trouble adapting them to work per group.

Would you have a suggestion?

Thanks again

Colin

[abhilash_kumar]

Hi Colin,

I'm surprised there isn't a way to access the value of a 'calculated member' row in another calculated member row using the usual GridRowColumnValue() function (and other functions).

It works if we hard-code the rowindex of the calculated row however, that isn't obviously the best way to do this. While you can access values of other 'existing' rows using the following way:

GridRowColumnValue(GetIndexOf("Group Alias", "Value"), currentcolumnindex, currentsummaryindex)

this does not seem to work when you place the RowValue of a calculated row in the above code.

The GetIndexOf() for a calculated row works everywhere except the 'Calculation Formula'!

Could you share the updated report, if possible, and I'll see if there is any other workaround.

-Abhilash