insert grand total from sub/subtotal 1

[ajdesigns]

I have a report with a sureport linked, Is there any way I can take the subtotals from the sub/report and display a grand total of these on the main report.The subtotals on the sub report are generated by a formula .I need to keep the sub report as it is with the subtotals intact as it shows details of lot nos, I am using CR 8.5

 

[lbass]

In the subreport, create a formula:

whileprintingrecords;
shared numbervar subrpttot := {@yourformula};

Place this on the subreport instead of your formula.

I'll assume that your subreport is in the group header_a section. In the main report, create three formulas:

//{@reset} to be placed in the group footer:
whileprintingrecords;
shared numbervar subrpttot := 0;

//{@accum} to be placed in the group header_b section (this must be in a section below the one in which your subreport is located:
whileprintingrecords;
shared numbervar subrpttot;
numbervar grtot := grtot + subrpttot;

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[ajdesigns]

Hi LB
My sub rep is in a grp2 footer,(I created an additional header so I could follow your ins to the detail but my sub started duplicating) all product totals etc are in the grp2 header with my sub breaking this down to lots used in the grp2 footer, So I created another section below this gf2b and placed the formula's @reset & @accum into this.The trouble is it only seems to be adding the last line of any lot qty's from each product.

 

[lbass]

Try placing {@accum} in gf2b, and place {@reset} in the group header. The reset formula must be in a section before the subreport and accum formula or after the accum formula, and the accum formula must be in a section after the subreport.

If this doesn't work, then I think we need more detail about your report structure. I see no reason why placement of the subreport in the group header would cause different behavior than in the group footer, and that suggests there might be some other issues.

-LB

 

[ajdesigns]

Hi LB
No change it seems if a line on the consolodated order consists of more than 1 lot no the accum formula is only picking up the last lot no qty.

 

[ajdesigns]

my rep consists of 2 groups
grp 1 sorted by original order no, includes original order no, customer ref
grp 2 sorted by product no, includes product, despatched qty, price.
I have sub reps in both footers
gf1 = sub showing delivery instructions for each product line.
gf2 = sub showing lot no's used to fullfill order line
everything seems to work great unless the sub bringing in the lot no's has more than 1 lot no per line
the sub bringing in the lot no's is linked to the main by the original order no field.
I hope this helps
ajd

 

[lbass]

I wonder if you have the section names reversed for the subreports. Isn't it:

gf2 = sub showing delivery instructions for each product line.
gf1 = sub showing lot no's used to fullfill order line

If there are multiple lot numbers within the subreport and you want to add their total to get the grand total in the main report, you need to add them within the subreport and set the shared variable to their sum.

Not sure I've really followed you correctly.

-LB

 

[ajdesigns]

Hi LB
First in the sub I need to convert to stock that
has been used from negative no to positive
@negate qty =
-{stkhstm.movement_quantity}

then I have to split the field despatch units
this is in the db as a string 1PK, 12PK etc
@split units =
split({stockm.despatch_units},"PK")[1]

I then need to convert this to a number
@to number =
if isnumeric({@split units}) then
tonumber({@split units})
Else 0

Then I have created a summary of the negate qty in the report
And used this to get the amount of each lot no that was used
For the line.
Then taken this summary and divided it by the box size to get no of boxes
@sum =
Sum ({@NEGATE QTY}, {stkhstm.lot_number}) / {@to_number}

This is then rounded up to get true no of boxes including part boxes
for each lot no used.
Local NumberVar RoundUp := {@sum};
If Int(RoundUp)/2 = RoundUp/2 then RoundUp else Truncate(Roundup) + 1

This rep consists of 2 groups
grp1 contains original order no
Grp 2 contains all other info

The problem is I cannot grand total the true no of boxes
I get a popup saying the summary / running total field could not be created

 

[lbass]

So you have the correct total per lot, i.e., the following is working correctly per lot?

Local NumberVar RoundUp := {@sum};
If Int(RoundUp)/2 = RoundUp/2 then RoundUp else Truncate(Roundup) + 1

I guess you mean that in your main report you have two groups:

grp1 contains original order no
Grp 2 contains all other info//<- you need to provide specific group field

And it looks like in your subreport you only have a group on lot number? Is the problem with getting the grand total on the subreport (You didn't indicate how you tried to create that subreport total)? If so, change the roundup formula to:

whileprintingrecords;
local numbervar roundup := {@sum};
shared numbervar subrpttot;

If Int(RoundUp)/2 = RoundUp/2 then RoundUp else Truncate(Roundup) + 1;
subrpttot := subrpttot + roundup;

Then display the result in the subreport report footer using:

//{@alllots}:
whileprintingrecords;
shared numbervar subrpttot;

Then follow my earlier suggestion to create the main report grand total.

-LB

 

[ajdesigns]

Thanks LB you have been a great help everything seems sorted now
Thanks again
AJD