ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Trouble with total
http://www.istechforum.com/YaBB.pl?num=1161113973

Message started by David Waldmann on 10/17/06 at 11:39:33

Title: Trouble with total
Post by David Waldmann on 10/17/06 at 11:39:33

I have a report that shows how much our SIMPLE IRA matching contribution is supposed to be, which we do quarterly. I thought it was working fine until last quarter I noticed that one employee's match was less than their contribution when it should have matched. I dicovered that it was because I was basing the formula that compared their contribution to the maximum match of 3% was based on each individual pay check, and one week they had worked low hours.

So, I re-did the report by subtotaling the total pay and contribution of each employee for the quarter and using those numbers. The problem I'm running into is that I can't total the resulting answer. The new field does not offer "Subtotal, etc" as a right-click option, and when I try to force one using the formula editor it says "The summary could not be created". I also tried a trick learned at the CR training class in Nashua of telling it to add those numbers together last, by using "WhilePrintingRecords", but got the same error.

I also tried using a custom Subtotal, but the created field is not in the list of available ones to choose from.

For the four times a year I run it I suppose it'd be quicker to just manually total it up, but it bugs me - I think it should be able to be done. Any ideas?

Title: Re: Trouble with total
Post by kevind on 10/17/06 at 12:07:24

David,
If you use a Group to group the information for each employee, you should
be able to use Insert->Summary Choose the field or formula
from the dropdown list that you want to subtotal. Specify that
this value be placed in the Group Footer.

For the Grand Total, use Insert->Summary and Choose the
same field as you did for the Group Summary. Specify that this
summary should be placed in the Report Footer.

I just tested this by using BKARCUST:
1. I Grouped by the first 2 characters of the customer code.
2. I created a formula that represents 1/2 of their outstanding invoices.
3. I then inserted a Summary Field in the footer of the Group that summarized the formula.
4. Then I inserted a Summary in the Report Footer that Summed the same formula.

Hope this helps.

Title: Re: Trouble with total
Post by David Waldmann on 10/17/06 at 13:12:46

The subtotal I'm trying to sum is not a CR Subtotal, but a conditional formula:

If Subtotal A > Subtotal B * .03
then Subtotal B * .03
else Subtotal A

This formula works fine by group, but it cannot be then summed, for sum reason :P

Maybe I need to think of this some other way.

Title: Re: Trouble with total
Post by dameng on 10/20/06 at 06:00:29

since you are using a conditional formula, then you will need to create and use a Manual Running Total.

Title: Re: Trouble with total
Post by David Waldmann on 10/20/06 at 06:16:37

dameng wrote:

since you are using a conditional formula, then you will need to create and use a Manual Running Total.

That's what I remembered from your class, but

David Waldmann wrote:

I also tried using a custom Subtotal, but the created field is not in the list of available ones to choose from.

???

Title: Re: Trouble with total
Post by kevind on 10/20/06 at 09:09:30

Generally speaking, when you create a manual subtotal, you create 3 different formulas:
One that clears a Global numbervar that represents the total.
One that accumulates the total.
One that you use to show the total.

All of these formulas should have whileprintingrecords; on the first line
so that they get processed while the report output is being generated.

Each formula has the global numbervar defined so that they all access
the same memory used to store the subtotal.

Here is a simple report that uses BKARCUST to subtotal and grandtotal
OUTINV by state.

http://spectronics.com/dba/Subtotal_example.rpt

(Right Click and Save File As .. to get a copy of the file)

Title: Re: Trouble with total
Post by David Waldmann on 10/20/06 at 10:14:23

Kevin,

Thanks for your help, but I can't open the report - I get an "Invalid TLV Record" error. I tried opening it directly and it apparently tried to use Java to display it

I've tried doing this about half a dozen different ways including using numbervar and reset. I always end up with the SUBtotal I want, but then I can do nothing with it.

This is my latest attempt, based on the CR Help page for running totals using a formula:

RUNNING_TOTAL
WhilePrintingRecords;
CurrencyVar Amount;
Amount := if {#Total_Pay} * .03 > {#Employee_Portion}
then {#Employee_Portion} else {#Total_Pay} * .03;

AMOUNT_RESET
WhilePrintingRecords;
CurrencyVar Amount := 0;

AMOUNT_DISPLAY
WhilePrintingRecords;
CurrencyVar Amount;

The AMOUNT_DISPLAY cannot be summed. I even tried writing a formula:

whileprintingrecords;
Sum ({@Amount_Display})

It says "The summary / running total could not be created"

Title: Re: Trouble with total
Post by David Waldmann on 10/20/06 at 11:02:14

What I meant was, first I downloaded it and got the TLV error, then I tried opening it directly and Java tried displaying it in my browser.

Title: Re: Trouble with total
Post by kevind on 10/20/06 at 13:16:53

Sorry about that ... I forgot I have Apache setup to serve up .RPT files.

Try this ... It is a Zip file with the Report inside.

http://spectronics.com/dba/Subtotal_example.zip

(Right Click and Save File As .. to get a copy of the file)

Title: Re: Trouble with total
Post by dameng on 10/23/06 at 04:34:01

RUNNING_TOTAL
WhilePrintingRecords;
CurrencyVar Amount;
Amount := if {#Total_Pay} * .03 > {#Employee_Portion}
then {#Employee_Portion} else {#Total_Pay} * .03;

keep your conditional formula in it's own formula name, so that way it is able to process and return a value.

use a seperate formula for the running total, and reference the conditional formula.

then in the runningtotal formula use
whileprintingrecords;
currencyvar amount;
amount := amount + condformula;
amount;

Title: Re: Trouble with total
Post by David Waldmann on 10/25/06 at 08:56:27

Sorry, Kevin, but now I get a "version error message".

I don't know if I'm just stupid or what. I could not get any of these ideas to work. I did eventually get the info I wanted, in a kind of comvoluted way. I set up two running subtotals - one that reset on every group and one that didn't.

Thanks for all the help.

Title: Re: Trouble with total
Post by kevind on 10/25/06 at 11:58:35

Duh :-/ ... that was a great idea!!!