Cross Tab reports - Grand Total column

[Former Member]

Hi,

I have a cross tab report, which has a grand total column. In the columns before that, I use a formula for the summarized fields for each selected record. But I want to show the sum of each column in the Grand Total column, but because I'm using a formula in the columns, it doesn't appear to be showing the sum of all the columns in the Grand Total column. Instead, it shows the same formula, which isn't correct.

Any help appreciated.

Many thanks,

Cal

[abhilash_kumar]

Hi Callum,

Do you have a screenshot of the crosstab?

Also paste the formula you're using.

-Abhilash

[Former Member]

Hi Abhilash,

Here is the formula I'm using in the columns -

if {Tags.INT_MODE} <> 0 then
cstr (Round ({Tags.INT_AVG},1),1) & "*"
else
cstr (Round ({Tags.INT_AVG},1),1)

The field in the column is set to the summary 'MODE'.

Basically, I had wondered if there was a formula I could put in place in the Grand Total column under the 'Display String'?

Thanks,

Callum

[abhilash_kumar]

The Grand Total column also does the same calculation.

I'm guessing you want the Grand Total column to show 'Sum' of all the Modes instead?

What version of Crystal Reports are you using? The Solution involves inserting a Blank Column (with calculation) and suppressing the Grand Total. The solution works only if you're using CR 2008 or higher.

-Abhilash

[Former Member]

Ah ok, I'm using Crystal Report 11.

[abhilash_kumar]

CR 11 as in CR XI?

Go to Help > About Crystal Reports > Is this version 11.x.x.x?

If Yes, then unfortunately this can't be done in the version you have. Creating a Manual Crosstab is the only workaround.

-Abhilash

[Former Member]

Yes, it's CR XI.

Is there any information online on how to create a manual crosstab?

Many thanks,

Callum

[abhilash_kumar]

Yes there is.

Here's a sample report:

http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/10c4c9c3-a275-2b10-bfa6-87b265d5a0d9

-Abhilash

[Former Member]

Thanks Abhilash, could you also tell me how to fix this issue in the newer version of Crystal Reports please, as I may have access to Crystal Reports 2013 later.

Thank you,

Callum

[abhilash_kumar]

Sure! Assuming the Grand Total Column is displayed to the right (towards the end of all columns), here's what you would do:

1) Right-click the last column (the column before the Grand Total Column) and select Calculated Member > Insert Column.

A blank column with zero values is inserted.

2) Right-click one of the zero values in the new column and select Calculated Member > Edit Calculation Formula and use this code:

Local Numbervar i;

Local Numbervar summ;

For i := 0 to currentcolumnindex-1 do

(

     summ := summ + GridValueAt(currentrowindex, i, currentsummaryindex);

);

summ;

3) Next, right-click the blank header of this column and select Calculated Member > Edit header formula and type in:

"Summary" //You may use a name of your choice for the column

4) Right-click the Grand Total column Header > Select Column Grand Totals > Suppress Grand Totals.

Good Luck!

-Abhilash

[Former Member]

Hi Abhilash,

When I right-click on the column before the Grand Total, there isn't an Calculated Member option ... ?

[abhilash_kumar]

This will Not work on CR XI as I said. The solution only works on CR 2008 or higher.

-Abhilash

[Former Member]

I'm currently trying it on Crystal Reports 2013, but that option does not appear.

[abhilash_kumar]

I should have said "Right-click the Header cell of the last column (the column before the Grand Total Column) and select Calculated Member > Insert Column"

-Abhilash

[Former Member]

I'm right-clicking on the column header cell, but there is no 'Calculated Member' menu option ...

[abhilash_kumar]

That's strange!

What options do you have when you right-click? Could you share a screenshot please?

-Abhilash

[Former Member]

[abhilash_kumar]

I've never come across a CR installation with a missing 'Calculated Member' feature before.

You should see a similar option for the row - is it there?

If not, please re-install the product and make sure a Local Admin or a user with Local Admin rights does the installation.

-Abhilash

[abhilash_kumar]

You can also access/create a Calculated Member by:

1) Right-clicking the blank white space at the top left corner of the Crosstab > Advanced Calculations > Calculated Member

The only difference, you would need to specify exactly where you would like to place the column (done by the GridValue functions) and much more.

-Abhilash