Running Totals in a Crosstab

[jamimo]

I am creating a fairly simple report but need to demonstrate the data by date. Clearly, a crosstab is the cleanest way to do this.

To properly calculate the data I need, I created a formula which subtracts a subtotal from a running total. However, the formula is not available to select from within the CT editor, leading me to believe Crystal does not allow this.

Can anyone help with me determine how to make this work? Please & Thanks!

 

[lbass]

Do you need a horizontal display of dates? If not, then grouping on the date would be the simplest method, with no crosstab. Otherwise, you might need to create a manual crosstab.

You should also identify the date interval, row (group) fields, and show the summary you want to use, including explaining the setup of the running total. Also, you should always identify your CR version.

-LB

 

[jamimo]

Hey, thanks for your response!

Yes, I need to demonstrate the months horizontally rather than vertically. The number of months may fluctuate, so a slew of manual calculations wouldn't work for this effort. In addition, I'm using OLEDB for security purposes, so I don't have the option of manipulating the data on the DB side.

I am using Crystal XIR2; the column headers are the payroll month, rows are department number, values are rolling PTO balances, which are a calculated field with one piece of the calc being a running total.

Any advice?

 

[lbass]

...show the summary you want to use, including explaining the setup of the running total.

Please show the contents of the calculation and explain the rt setup.

-LB

 

[chelseatech]

We covered using running totals in a crosstab a while ago in our newsletter. There are a couple of tricks to make it work properly.

You must sort your data by the column field. Use the Record Sort Expert and make sure the sorting matches the crosstab row.

Then you are ready to create the running total. Use the "Reset on change of field" and select your column field here as well. Can't use "Change of Group" as the Crosstab column isn't a group in your report.

The running total can now work as a summarised field in your crosstab. You need version 8.5 or later to support this.

As you data is grouped by month, it might be better to use a manual crosstab and calculate a formula for each column/month. While a little more work, you have a lot more control about missing months.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm