Total is for the wrong line on the report 2

[ask4help]

I have a main report that links to a subreport using the part number to find the average selling price from all customer orders. This average is displayed on the subreport. I need to use this average to multiply by an order quantity for a total order price and then give a grand total at the end of the report.


subreport formula field aveprice

whileprintingrecords;
shared numbervar aveprice := average({CUST_ORDER_LINE.UNIT_PRICE});


main report formula field invtrantdol

WhilePrintingRecords;
shared numbervar aveprice;
aveprice * {WORK_ORDER.DESIRED_QTY}

the fields are displayed in the group footer section

The problem I have is the invtrantdol does not match with the line it is on. It is for the line above it.

 

[IanWaterman]

The value for {WORK_ORDER.DESIRED_QTY} will be the value held against the last record in that group.

If you want for the whole of that group change formual to

aveprice * sum({WORK_ORDER.DESIRED_QTY}, groupfield}

Ian

 

[ask4help]

Sorry but I do not need to sum the group for work_order.desired_qty. I think what is happening is the aveprice shared with the subreport is one record off. For example would be the result of my report:

Main report
Part Qty aveprice Total
A 1 100 0
B 1 200 100
C 1 300 200


As you can see the information is off by one record. aveprice is displaying the subreport and Total is WhilePrintingRecords;
shared numbervar aveprice;
aveprice * {WORK_ORDER.DESIRED_QTY}

 

[IanWaterman]

Where is the subreport placed? If it is not evaluating in time for the calculation you will need to move it.

What is the laout of you report, are you suppressing details?

Ian

 

[ask4help]

Group header and details are suppressed.
Group footer contains the fields, subreport and calculation

 

[IanWaterman]

Try moving the subreport into the group header. That way it should execute before the GF.

If you can not do that split the GF into two sections and place the SR in the upper section.

Ian

 

[scdmh]

Your subreport needs to be in an area above the area where you have the variable field. In this case it would be the group header.

You cannot suppress the group header or the subreort won't run. However you can make the font of the fields within the subreport white and well as the subreport object on your main report. Then you can re-size that object so it is very tiny.

You also might need to initialize the variable to zero. That way if there is a part that the subreport does not return a value for it will be zero. Otherwise your variable will hold the last value until it changes.

Dianne

 

[ask4help]

I split the GF into two sections and placed the SR in the upper section. In the upper section I turned on Underlay Following Sections so everything appears on the same line of the report.

One last thing.

I have a formula field INVTRANTDOL with the following:
whileprintingrecords;
shared numbervar aveprice;

if
aveprice = 0
then
{@ESTCOST} * 1.5 * {WORK_ORDER.DESIRED_QTY}
else
aveprice * {WORK_ORDER.DESIRED_QTY}

I can't do a running total on this field so how do I get a grand total for INVTRANTDOL in the report footer.

 

[IanWaterman]

Use a variable to act as an RT for you

change formula to

whileprintingrecords;
shared numbervar aveprice;
global numbervar GT;

if
aveprice = 0
then
GT:=GT+{@ESTCOST} * 1.5 * {WORK_ORDER.DESIRED_QTY}
else
GT:=GT+{@aveprice * {WORK_ORDER.DESIRED_QTY};

if
aveprice = 0
then
{@ESTCOST} * 1.5 * {WORK_ORDER.DESIRED_QTY}
else
aveprice * {WORK_ORDER.DESIRED_QTY};

 

[scdmh]

It may be the long way around but I (almost) always set up three variables:

xxx-01-init This is where I initialize my variable.
xxx-02-eval This is where I evaluate (aggregate, etc).
xxx-03-display This is where my final result displays.
---------------
zzz This is the field that holds the value from the subreport.

The formula for zzz would be:
Whileprintingrecords;
shared numbervar subtotal;
subtotal
------------------
For grand totals:

xxx-01-init is placed in the report header
Whileprintingrecords;
shared numbervar GT;
GT:=0

xxx-02-eval is place in the group area
Whileprintingrecords;
shared numbervar GT;
GT:= GT + {zzz}

xxx-03-display is placed in the report footer
Whileprintingrecords;
shared numbervar GT;
GT

Dianne