Crystal Reports 2008 - cross tab totals

[Former Member]

I am hoping that someone can help with a Cross Tab issue in Crystal Reports 2008.  In the Crosstab below, I am attempting (if is possible) to get a grand total to print at the bottom of the Total column (910 on the left and to get the Total column that contains (61, 7, 5 and 14) to appear (be moved) to after column 3 under the DF section header and total that as well. 

In the long run, I would like it to look like this. the 2 columns on the right can be ignored (under the FR heading)

If you need additional screen images, such as the cross tab setup info, I will be happy to supply that.

Thank you,

Glen Rogers

[abhilash_kumar · Accepted solution]

Hi Glen,

Here's what you need to do:

1) Right-click the first "Total" Column (Click on the the header) > Select Column Grand Totals > uncheck "Totals on Left". You'll see that the Grand Totals and all the Subtables move to the right.

2) Now, right-click the same Total column again > Select Column Grand Totals > Check "Suppress Grand Totals". The Grand Total column is suppressed.

3) Right-click the 1st Column Header (In this case DF) and select Calculated Member > Insert Column.

A blank column with the Title "Total" is inserted to the right of DF.

4) Right-click the blank white space at the corner and select Advanced Calculations > Calculated Member. The Calculated Member GUI opens up.

5) On the right should be the "Properties" area. Look for the option "Insert Evaluation" and check "Before"

6) While in the same window click on the button "Edit Insertion Formula" and remove any code that's in there. Use this code instead:

CurrentColumnIndex = 1

7) If you've followed all the steps correctly, the Total column with zero values should be 1st column now.

😎 Right-click on the zero value cells in the Total Column and select Calculated Member > Edit Calculation Formula and use this code:

GetTotalValueFor("database_field",0)

Here 'database_field' should be replaced with the field that is used in the Crosstab as the 1st column. Let's say the field name is 'Product.Name' then the formula should be:

GetTotalValueFor("Product.Name",0)

That's it! If you've followed these instructions to the cue, you should have the crosstab just the way you wanted.

Let me know how this goes!

-Abhilash

[abhilash_kumar · Accepted solution]

Forgot to add the most important thing - make sure do all of the above in the Preview Mode.

-Abhilash

[Former Member · Accepted solution]

Abhilash

Thanks for the extremly helpful answer.  For the most part this worked like a charm.  A couple minor things that I found to be different. After performing step 3, I did not see a blank column, instead I saw this: The new Total column populated with zero's.

Once I got to step 6 and removed and entered in CurrentColumnIndex = 1, the results of my crosstab looked like this: I lost the newly added Total column.

If I left the defaulted code -

GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and GridRowColumnValue("@Type") = "DF", It worked perfectly.

On to step 8 - the only thing I needed to do here, was instead of using the database field, i had to use one of my formula's.

I only have one small item that I need to fix (if Crystal will let me do it). 

My new cross tab now looks like this:

On the last row (labeled Total), want to know if I can delete 15, 75 and the 3. leaving only the numbers that fall into the Total columns (93, 90 and 3)

[abhilash_kumar · Accepted solution]

Hi Glen,

I'm glad we got this working!

1) The reason I suggested NOT to use the existing "Insertion Formula" is because it then becomes dependent on the value "DF" to insert the Column.

If the database/crosstab does not contain this value, the Total column will never appear.

2) When you say "After performing step 3, I did not see a blank column, instead I saw this" - Well, that's exactly what I said will happen when you insert a new column. You should be worried if you don't see that column with zero values !

3) Using this code CurrentColumnIndex = 1 along with the Insertion Evaluation set to "Before" should move the Total Column to the 1st position.

Would you be able to share the report with saved data and I can see what's happening with this?

4) To remove those values from the Grand Total row, follow these steps:

• Right-click one of those values; as an instance let's right-click 15
• Select Format Field > Common tab > Check "Suppress"

That's it!

-Abhilash

[Former Member · Accepted solution]

I can share the report with you, yes.  Will you get it if I reply and attach it to the email I got from you.  Since they come from a noreply@sapnetworkmai.com address?

[abhilash_kumar · Accepted solution]

You can attach the report to the thread. First save the report with data > Then change its extension from .rpt to .txt. Then click on the "Use advanced editor" link in the reply window and there be should an attachment option in there.

-Abhilash

[Former Member · Accepted solution]

File is attached.  Thanks for taking a look at this.

[abhilash_kumar · Accepted solution]

Strange! For some reason the crosstab in your report in acting weird. It puts in the Total Column if I add this as the Insertion formula:

currentcolumnindex = 2 (which makes absolutely no sense to be honest).

Anyway, for now you can keep the default insertion logic.

I also see that you've suppressed the totals from the rows - nice!

Have a great day.

-Abhilash

[Former Member · Accepted solution]

Thanks for all your help !  Have a great day.

[JWiseman]

hi Glen,

cr2008 has what's called "calculated members" that you can use to insert different columns etc. into a cross-tab.  if you go to the online help in your crystal reports designer and look up calculated memeber in the Search tab, you'll see a "To add a calculated member" option that will help you get started.

there are also tons of new grid functions available in cr2008 that are for crystal reports that allow you to grab values from other rows or columns.

also, if you want to grab some samples of some financial reports that make use of this functionality, go here and follow the instructions on getting the 2008 sample reports. the rolling quarter income statement is one such example.

cheers,

jamie