Reset variable problem 1

[Wayne79]

Using CR8.5

I have a sub report on the detail line. It calculates the sum of Other_Charges based on a Bill_Number (Link from main report to sub report)

On the sub report I get the total via this formula:
//Placed in Detail B
WhilePrintingRecords;
shared numberVar OtherChargesTotal := OtherChargesTotal + {ACHARGE_TLORDER.FUNCTIONAL_AMT}

On the main report I have a couple of formulas:
//Resets the OtherChargesTotal variable which is populated via the sub report SR_OtherCharges
//Placed in G3 header
shared numbervar OtherChargesTotal := 0;

//Gets the Other Charges value from the sub report
//Placed in Detail B
WhilePrintingRecords;
shared numberVar OtherChargesTotal;

//Resets the Group 3 (TLORDER.BILL_NUMBER) Other Charges Total formula field
//Placed in G3 Header
numbervar G3_OCTotal := 0;

//Calculates the total of Other Charges for each Bill Number
//Placed in the G3 Footer.
numbervar G3_OCTotal := G3_OCTotal + {@OtherCharges}

//Resets the Group 2 (DRIVER.DRIVER_TYPE) Other Charges Total formula field
//Placed in G2 Header
numbervar G2_OCTotal := 0;

//Calculates the total of Other Charges by Driver Type
//Placed in G2 Footer
numbervar G2_OCTotal := G2_OCTotal + {@G3_OtherChargesTotal}

Data looks like this:
Sub Report On Detail Line: 52.00
@OtherCharges on Detail B: 52.00
@G3_OC_Total on G3 Footer: 52.00

Sub Report On Detail Line: 52.00
@OtherCharges on Detail B: 52.00
@G3_OC_Total on G3 Footer: 104.00

Sub Report On Detail Line: 78.00
@OtherCharges on Detail B: 78.00
@G3_OC_Total on G3 Footer: 182.00

Sub Report On Detail Line: 130.00
@OtherCharges on Detail B: 130.00
@G3_OC_Total on G3 Footer: 312.00

Sub Report On Detail Line: 83.00
@OtherCharges on Detail B: 83.00
@G3_OC_Total on G3 Footer: 395.00

To this point all seems correct.

@G2_OC_Total on G2 Footer: 478.00

This line sould be 395.00 instead of 395.00 + 83.00.

Either my formulas are in the wrong places or the reset values are. @G3_OC_Total does not appear to be reseting.

Wayne

 

[synapsevampire]

I would guess that you are adding the last one twice somewhere, the formulas seem OK to me.

-k

 

[lbass]

First, if the subreport is in detail_a, then I would insert another detail section in the main report, and place the reset formula that you currently have in GH#3 ( shared numbervar OtherChargesTotal := 0 in detail_a, with the subreport in detail_b and the shared variable in detail_c. Detail_a can be suppressed.

Instead of your formula:

numbervar G2_OCTotal := G2_OCTotal + {@G3_OtherChargesTotal}

...just use the following in the G2 footer:

whileprintingrecords;
numbervar G3_OCTotal;

-LB

 

[Wayne79]

Why would @G2_OC_Total be adding @OtherCharges again?

Or is it that @G2_OC_Total is adding @OtherCharges again because a reset is either wrong or in the wrong place.

I've tried moving them around in the various group headers to no avail.

 

[Wayne79]

That did it.

Thanks again LBass.

 

[Wayne79]

Spoke too soon.

Fixed the 395.00 problem but it is not reseting for the next group. I've moved the reset formula field around to various places but it never resets.

Reset Formula is:
numbervar G3_OCTotal := 0;

Which is currently placed in the G3 header.

 

[lbass]

Sorry. I think I see the problem. First, it looks like there is always only one detail line, so if you want to have the G3 reset in the GH#3 header, that's okay.

What you should do is add a line to the following formula:

//Calculates the total of Other Charges for each Bill Number
//Placed in the G3 Footer.
whileprintingrecords;
numbervar G3_OCTotal := G3_OCTotal + {@OtherCharges};
numbervar G2_OCTotal := G2_OCTotal + {@OtherCharges};

Do not use an accumulation formula in the G2 Footer. Instead, use the display formula:

whileprintingrecords;
numbervar G2_OCTotal;

Leave the G2_OCTotal reset formula in GH#2.

-LB

 

[Wayne79]

Added your changes above. However the GF#3 is still not reseting. It goes from 395 to 499, adding the 104 from the next group.

There are now 3 detail lines, after the first round of changes.

A is supressed and contains the formula:
//Resets the OtherChargesTotal variable which is populated via the sub report SR_OtherCharges
//Placed in Detail A which is supressed
shared numbervar OtherChargesTotal := 0;

B is the main line with the fields for display.

C is suppressed and contains the formula
//Gets the Other Charges value from the sub report
//Placed in Detail C
WhilePrintingRecords;
shared numberVar OtherChargesTotal;

 

[lbass]

In the subreport, try placing the following formula in the subreport footer:

whileprintingrecords;
shared numberVar OtherChargesTotal;

It would help in future posts if you name your formulas. Your report should now look like this:

GH#1
GH#2 - G2_OCTotal Reset
GH#3 - G3_OCTotal Reset
detaila - sharedvar reset
detailb - subreport
detailc -shared var display
GF#3 - Accumulate and Display G3_OCTotal and
Accumulate G2_OCTotal
GF#2 - Display G2_OcTotal
GF#1

But when I look at your original example, I am confused, since it does NOT show G3_OCTotal resetting--instead it shows it accumulating. If this is in fact what you want, then remove references to G3_OCTotal altogether, and just accumulate G2_OCTotal in GF#3 and then use a display formula in GF#2:

whileprintingrecords;
numbervar G2_OCTotal;

If there is only one record per group, then the subreport or the shared variable formula in detail_c already displays that total and would be the same as a reset G3_OCTotal.

-LB

 

[Wayne79]

GH#1 - {Driver.Home_Zone}
GH#2 - {Driver.Driver_Type}
@Reset_G2_OC
numbervar G2_OCTotal := 0;
GH#3 - {TLORDER.Bill_Number}
@Reset_G3_OC
numbervar G3_OCTotal := 0;
Detail A - Suppressed
@Reset_OtherCharges
shared numbervar OtherChargesTotal := 0;
Detail B
Sub Report and other fields
SR_OtherCharges - Subreport
GH#1 - {TLORDER.BILL_NUMBER} Suppressed
Detail A Suppressed {ACHARGE_TLORDER.FUNCTIONAL_AMT}
Detail B - Suppressed
@OtherCharges
WhilePrintingRecords;
shared numberVar OtherChargesTotal := sum({ACHARGE_TLORDER.FUNCTIONAL_AMT})
GF#1 Suppressed - Field: sum({ACHARGE_TLORDER.FUNCTIONAL_AMT})
ReportFooter - sum({ACHARGE_TLORDER.FUNCTIONAL_AMT})
Detail C
@OtherCharges
WhilePrintingRecords;
shared numberVar OtherChargesTotal;
GF#3 - Suppressed
@G3_OtherChargesTotal
whileprintingrecords;
numbervar G2_OCTotal := G2_OCTotal + {@OtherCharges};
numbervar G3_OCTotal := G3_OCTotal + {@OtherCharges};
GF#2
@G2_OtherCharges
whileprintingrecords;
numbervar G2_OCTotal;
GF#1
Haven't started formula yet

I like this format. Hope it helps.

I really appreciate the help you've given so far. As you can tell Crystal Reports is kind of new for me. I'm more used to creating queries or a series of queries and reporting the final results. Even stored procs would be better imo. But they are unavailable to me. So I get to learn CR in all its glory. I like it but it is frustrating.

 

[GJParker]

Just to throw my tuppence in,

make sure you have the WhilePrintingRecords; statement in all of your reset formulas.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[GJParker]

I'm not too sure about the layout of your subreport ? How many details lines will a subreport have ? you are assigning the report total for {ACHARGE_TLORDER.FUNCTIONAL_AMT} on each detail line. if it's only the report total you want to return to the main report then you need only assign this once in the report header or footer.

Similarly if you are only returning the report total then there is no need for a group in the subreport

RH (Suppress)
@OtherCharges
WhilePrintingRecords;
shared numberVar OtherChargesTotal := sum({ACHARGE_TLORDER.FUNCTIONAL_AMT});
Details (suppress)
RF (Suppress)




Gary Parker
MIS Data Analyst
Manchester, England

 

[Wayne79]

That solved it. Thanks Gary

Also, Thanks to LBass too.

Wayne

 

[Wayne79]

What I'd really like to do is be able to suppress the details line. But I don't seem to be able to do that and still recieve the calculations from the sub report.

I'll experiment with the next sub report on your ideas Gary. Looks like they should work. Added the group to help me figure out what is goin on. All I really need from the sub report is the sum of all the records for a particular bill_number.

 

[GJParker]

if you are using Version 9 or later you can suppress the subreport and detaisl section on the main report by following a few simple steps

1. Suppress all the sections in the subreport

2. in the main report, format subreport -> subreport tab -> check 'suppress blank subreport'.

3. open section expert and check 'suppress blank section'

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Wayne79]

Un fortunately I am using CR 8.5

 

[Wayne79]

Using your idea in the sub reports worked like a charm. All sections suppressed, formula in the report header. The formula could probably go anywhere though I'd imagine.

On the main form I left the sub report in the detail B line but moved all the fields to the C line. Unsuppressed the C line. I resized the sub report as small as possible in an attempt to hide it.

I am now able to suppress the C line giving the user the option of showing the details or just viewing the summary.

However, the line with the subreport is not totally compressed. So for every detail line there is still a small line visible. It doesn't look to bad on the detail view but it is noticible on the summary view.

I was wondering if there was a way to make the subreports even smaller. Or if there was a way to hide them and still have them run.

 

[lbass]

You could try formatting the section to "underlay following sections".

-LB

 

[Wayne79]

Underlay Following Sections didn't work.

 

[GJParker]

Underlaying the section should work,

but I would personally would be looking to run the subreport in the Group 3 Header section as that is the level at which the subreport total is required.



Gary Parker
MIS Data Analyst
Manchester, England