Skip to Content
0

Wrong sub total value in cross tab report

May 26, 2017 at 11:24 AM

53

avatar image
Former Member

Dear Experts ,

I have created a cross tab report and i want to display a year wise average value in report . My Month wise value is correct , when comes to Sub total value , the value is not accurate .

Please suggest . 2.jpg1.jpg

2.jpg (323.6 kB)
1.jpg (92.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Mar 01 at 08:37 PM
0

Hi Sri,

Here's what you need to do:

1. Go to the Crosstab Expert > Customize Style tab > Uncheck "Row Totals on Left".

This will move the existing subtotal to the very end of each month group.

2. Right-click the subtotal column titled "Total" > Insert Child.

3. A blank column is inserted to the beginning of the crosstab. Right-click the blank header cell of this new column > Calculated Member > Edit Insertion formula. Use this code:

GetColumnGroupIndexOf(CurrentColumnIndex) = 1

4. Right-click the blank header cell of this new column > Calculated Member > Edit Header formula. Use this code:

"Total"

5. Right-click one of the blank summary cells of this column > Calculated Member > Edit Calculation formula. Use this code:

local numbervar i := CurrentColumnIndex;
local currencyvar summ;
local numbervar cnt;
while GridLabelAt("Field Used as the First Column in the Crosstab", CurrentColumnIndex) = GridLabelAt("Field Used as the First Column in the Crosstab", i+1) do
(
    summ := summ + gridvalueat(CurrentRowIndex, i, CurrentSummaryIndex);
    cnt := cnt + 1;
    i := i + 1;
);
summ/(cnt-1);

Note: In the Code above, replace "Field Used as the First Column in the Crosstab" with the correct database field that you've used in the crosstab's column. And it needs to be enclosed in double-quotes exactly like in the code above.

6. Go to the Crosstab Expert > Customize Style tab > Under Columns, highlight the second field > Check "Suppress Subtotal" (Under "Group Options").

I hope this helps.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded