I have a requirement that includes a crosstab that displays some expenses(rows) that are manually entered by the user for 4 items(columns). I have exactly 10 rows of expenses that are available in the table but i need to insert about 10-15 others as calculated members and some of those calculated rows include values from other calculated rows so i'd need to use a calculated member in another calculated member and i haven't found a way to do this so far.
I thought about doing it from the query but the items are basically 4 tables so i'd need around 20 unions for each table in which some will include sub queries to include already calculated formulas in other formulas, technically it's doable but i am not sure that's the best way.
Any help would be appreciated.
Thanks,
Ashraf
Hi Ashraf,
You can refer to other Calculated Columns, ss long as the Calculated Members are in the "same" crosstab.
Do you have an example (sample data) of what you want in the crosstab?
-Abhilash
ItemCode is the columns
U_Name is the rows
U_Value is the summarized field
Say i have the names as Cost1,Cost2,Cost3,Cost4 and i need inserted a calculated member after Cost2 which is the sum of 1 and 2, so now it looks like this Cost1,Cost2,CalcMemb1,Cost3,Cost4. Now say that i want to add another calculated member after Cost 3 which is the sum of CalcMemb1+Cost3? how do i refer to the calculated member in the formula?
That's what i tried in the calculation formula:
GridValueAt(GetRowPathIndexOf("CalcMemb1"), CurrentColumnIndex, CurrentSummaryIndex) + GridValueAt(GetRowPathIndexOf("Cost3"), CurrentColumnIndex, CurrentSummaryIndex)
Hi Ashraf,
1. Right-click the header cell of the first Calculated Row after Cost2 > Calculated Member > Edit Row Value formula > Type in a Name to uniquely identify that column:
"CalcCol1"
2. Right-click the summary cell for the second Calculated Row that you inserted after Cost 3 > Calculated Member > Edit Calculation Formula > use this code:
GridValueAt(GetRowPathIndexOf("CalcCol1"), currentColumnIndex, currentSummaryIndex) +
GridValueAt(currentRowIndex-1, currentColumnIndex, currentSummaryIndex)
Repeat Steps 1 and 2 for every subsequent Calculated Row you add. Simply change the "Row Value" for each Row.
-Abhilash
That solved my problem!
Thank you very much.