How to calculate total column value from the gridlabels in cross tab

[former_member619433]

Hi

Need help if calculating the total budget value which is the grid label.

I have two columns Region, budget and displaying the Budget as grid label. I would like to display the total of budget in grand column total.

Is there anyway of calculating the sum of budgets highlighted in yellow and display the totals, please?

Thanks

Kavitha

cross-tab-total-budget.png

[abhilash_kumar · Accepted solution]

Hi Kavitha,

You cannot update the "Total" label to display the sum of Budget.

You could insert a "child" Calculated member column, however, this will require that you also display the column subtotals.

Here's another workaround that involves adding "Budget" as a new Row:

1. Right-click any Row cell and select Calculated Member > Insert Row.

A blank row should be inserted.

2. Right-click the blank row cell > Calculated member > Edit Insertion formula > Modify the code to:

GetRowGroupIndexOf(CurrentRowIndex) = 0

3. Right-click the blank white space to the top left of the crosstab > Advanced Calculations > Calculated Member > Select "Before" for "Insert Evaluation"

4. Right-click the blank cell again > Calculated Member > Edit Header formula > Type:

"Budget"

5. Right-click one of the zero values in this row > Calculated Member > Edit Calculation formula > use this code:

local numbervar i;
local currencyvar tot;
If CurrentColumnIndex = GetNumColumns - 1 Then
(
    for i := 0 to GetNumColumns - 3 step 2 do
    (
        tot := tot + GridlabelAt("Budget Column", i); //column should be surrounded by double quotes
    );
tot;
)
Else
GridlabelAt("Budget Column", CurrentColumnIndex);

6. Right-click one of the Budget column cells on the top > Format Field > Suppress. We suppress it as we no longer need the Budget to print as a column

7. Reduce the size of this column to its smallest possible size.

8. Go to the Crosstab Expert > customize style > Format Gridlines > Highlight "Column labels horizontal lines" and change its color to white

Hope this helps.

-Abhilash

[former_member619433]

Hi Abhilash,

Thanks very much for the response.

your solution worked perfect, you are a star

Thanks

kavitha