Grand Total of Shared Variable

[kimrose]

I am trying to use a total from a subreport in CR v10 and am not sure if what I'm trying to do is possible...here's the details:

I've created a main report that links to a subreport by SKU and displays quantity on hand at our distribution center in the main report by creating the following:

//DETAIL-formula for shared variable used for detail section
WhilePrintingRecords;
Shared NumberVar DCQOH;

//ACCUM-formula used in suppressed section GF1a
WhilePrintingRecords;
Shared NumberVar DCQOH;
Shared NumberVar TotalDCQOH;
TotalDCQOH:= TotalDCQOH + DCQOH

//DISPLAY-formula used in GF1b
WhilePrintingRecords;
Shared NumberVar TotalDCQOH;

The problem is that I allow the user to choose how the report is grouped. When the user chooses to group by SKU, this all works great.

SKU DC QOH
abc 100
def 200
ghi 300

Total 600

However, when they choose By Store/By SKU then the QOH in the total uses the duplicated data when summing (as it should):

Store SKU DC QOH
1 abc 100
1 def 200
1 ghi 300

2 abc 100
2 def 200
3 ghi 300

What I get:
Total 1200

What I want:
Total 600

I also tried creating a whole new subreport that totals the SKUs before returning the data, however I run into issues with that too. If I link by SKU then I only see the last qty in the main report. If I don't link, then the subreport gives me a total for more than I originally displayed on the report (because I have additional parameters in the main report.)

Sorry so verbose, just wanted to make sure I gave enough info. Any help is much appreciated...thanks!

 

[lbass]

Are all SKUs represented at each store? If they are, you could change your {@accum} formula to:

//ACCUM-formula used in suppressed section GF1a
WhilePrintingRecords;
Shared NumberVar DCQOH;
Shared NumberVar TotalDCQOH;

if {?group} = "Store" then
(if {table.store} = 1 then
TotalDCQOH:= TotalDCQOH + DCQOH) else
if {?group} = "SKU" then
TotalDCQOH:= TotalDCQOH + DCQOH

-LB

 

[kimrose]

Clever!...but no, they will only display in the stores in which they are sold within the prompted date range.

Kim

 

[lbass]

Okay, then try this for {@accum}:

WhilePrintingRecords;
Shared NumberVar DCQOH;
NumberVar TotalDCQOH; //this doesn't have to be "shared"
stringvar x;

if instr(x,{table.SKU}) = 0 then
(
x := x + {table.SKU} + ", ";
TotalDCQOH:= TotalDCQOH + DCQOH
);

Then your display formula would be:

WhilePrintingRecords;
NumberVar TotalDCQOH;

-LB

 

[kimrose]

It's so close! However, it looks like the first qty is not being counted when the next store displays data:

(I un-supressed GF1a to see what the ACCUM was doing when the grand total wasn't quite right.)

Store SKU DC QOH
1 def 100
-->ACCUM=100

2 abc 50
2 def 100
2 ghi 25
-->ACCUM=125 --- but should be 175

I'm having difficulty understanding what exactly the new formula is doing, therefore I'm not sure how I can modify it.

 

[lbass]

You are just picking up the last value in each group because you are placing {@accum} in the wrong section. It belongs in the inner (SKU) group section, NOT in the store group section.

This formula just says that if the SKU is not already in an array x, then add it to the array and also add it to the grand total.

-LB

 

[kimrose]

It worked! Thank you LBASS...much appreciated!