Need Help on summing data on different conditions. 1

[vega83]

Friends I have data this way

LPN SKU TYPE Date packs
123 231 PTS 10/7/2008 11:41:33 AM 1
123 141 CCS 10/9/2008 11:44:33 AM 2
123 221 BP 10/8/2008 11:44:33 AM 1

AS PTS type is having min date i wan tto sum the packs and show the count under it

like this way
PTS LPN COUNT PTS PACKS
1 4

Similarly if BP is having min date the count will go under it and so son

But in case dats is having type other those 3 types like this
LPN SKU TYPE Date packs
123 231 67 10/7/2008 11:41:33 AM 1
123 141 CCS 10/9/2008 11:44:33 AM 2
123 221 BP 10/8/2008 11:44:33 AM 1

I should not count this LPN or Packs in any of those, because 67 is having min date but if the data is like this

LPN SKU TYPE Date packs
123 231 BP 10/7/2008 11:41:33 AM 1
123 141 69 10/9/2008 11:44:33 AM 2
123 221 CCS 10/8/2008 11:44:33 AM 1

In this above case we should consider this count because BP has min dat, so basically if either of those 3 types(PTS,CCS,BP) have min date we should count or we should ignore it.

BP LPN COUNT BP PACKS
1 4


Any idea on this? how to achive this?. My optput will be like this

PTS LPNS PTS PACKS CCS LPNS CCSPACK BP LPNS BP PACKS
25 450 12 123 3 20


Thanks a ton.
V.

 

[IanWaterman]

YOu could try using vars

Order you data by date ascending below assumes no grouping, if grouped the min statement will be
min(datefield, groupfield)

@Min Type
Whileprintingrecords;

global stringvar mintype;

If datefield = min(datefield) then mintype:= {typefield)

Then in report footer

@Min Type Display
Whileprintingrecords;

global stringvar mintype;

Place your sum of packs alongside this formula.

Ian

 

[lbass]

First insert a group on {table.LPN} (assuming that you are looking for the first record in this group). Then create two formulas:

//{@accum} to be placed in a group section:
whileprintingrecords;
numbervar cntPTS;
numbervar cntCCS;
numbervar cntBP;
numbervar PTSsum;
numbervar CCSsum;
numbervar BPsum;

if {table.date} = minimum({table.date},{table.LPN}) then
(
if {table.type} ="PTS" then (
cntPTS := cntPTS + 1;
PTSsum := PTSsum + sum({table.packs},{table.LPN})
) else
if {table.type} ="CCS" then (
cntCCS := cntCCS + 1;
CCSsum := CCSsum + sum({table.packs},{table.LPN})
) else
if {table.type} ="BP" then (
cntBP := cntBP + 1;
BPsum := BPsum + sum({table.packs},{table.LPN})
)
)
;

Then in the report footer, add a formula like this to display the results and suppress the other sections:

whileprintingrecords;
numbervar cntPTS;
numbervar cntCCS;
numbervar cntBP;
numbervar PTSsum;
numbervar CCSsum;
numbervar BPsum;

totext(cntPTS,0,"") + " "+totext(PTSsum,0,"")+ " "+
totext(cntCCS,0,"") + " "+totext(CCSsum,0,"")+" "+
totext(cntBP,0,"") + " "+totext(BPsum,0,"")

Instead of this last formula you could use separate formulas to display each result, if you wish. Then you wouldn't have to convert the variables to text and you could position them however you wanted, as in:

//{@displaycntPTS}:
whileprintingrecords;
numbervar cntPTS;

-LB

 

[vega83]

LB,
THanks a lot!!!, it works like charm, thanks so much LB,
Ian thank to you too for your time.

LB, I have one more question, I have other group above LPN thats is stage, it has only two values either ST ot DT. Shall i use the same formulas and replace case number with STAFE field to get sub totals?

Thanks a lot again

 

[lbass]

You could use the same approach, but using different variable names. I can't comment on the fields, since you didn't identify a field in the original post as case#.

-LB

 

[vega83]

LB I have tried same approach, now my first group is on ZONE, then 2nd group on LPN. I have changed the variables like this
whileprintingrecords;
numbervar cntPTS1;
numbervar cntCCS1;
numbervar cntBP1;
numbervar PTSsum1;
numbervar CCSsum1;
numbervar BPsum1;

if {CASE_DTL.RCV_DATE_TIME} = minimum({CASE_DTL.RCV_DATE_TIME},{LOCN_HDR.ZONE}) then
(
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="PTS" then (
cntPTS1 := cntPTS1 + 1;
PTSsum1 := PTSsum1 + sum({@pack},{LOCN_HDR.ZONE})
) else
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="CCS" then (
cntCCS1 := cntCCS1 + 1;
CCSsum1 := CCSsum1 + sum({@pack},{LOCN_HDR.ZONE})
) else
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="BP" then (
cntBP1:= cntBP1 + 1;
BPsum1 := BPsum1 + sum({@pack},{LOCN_HDR.ZONE})
)
)
;


But the I am getting total values , I placed the separate formulas in group footer 1(ZONE)

 

[lbass]

I guess I don't know what you are trying to do. What is the end result you want to achieve? Didn't the results of the first set of formulas go into the report footer? Please explain where each set of results is supposed to display.

-LB

 

[vega83]

LB,
But when I place the original formula in group footer 2 I am getting right values. I think I can use the same formula but needs to be placed in different footer.

 

[vega83]

I have to break down the total results by two zones ST and DR

ZONE PTS LPNS PTS PACKS CCS LPNS CCSPACK
ST 12 43 3 34
DR 3 10 10 651

 

[lbass]

Please answer my questions. I don't understand whether you still need the original results or where you expect the results to be. Please show sample data that shows both sets of results, and show in what sections they should appear.

-LB

 

[vega83]

LB,
I still need the original results that is total values, But i also want to subtotals for ST and DR like this
ZONE PTS LPNS PTS PACKS CCS LPNS CCSPACK
ST 12 43 3 34
DR 3 10 10 651

Total 15 53 13 685

Now with your formula I am getting total.

 

[lbass]

Okay, thanks. Use the following formula in the LPN group section (along with the original formula), but the summary should be at the LPN level, not zone (see the change to {table.LPN}:

whileprintingrecords;
numbervar cntPTS1;
numbervar cntCCS1;
numbervar cntBP1;
numbervar PTSsum1;
numbervar CCSsum1;
numbervar BPsum1;

if {CASE_DTL.RCV_DATE_TIME} = minimum({CASE_DTL.RCV_DATE_TIME},{table.LPN}) then
(
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="PTS" then (
cntPTS1 := cntPTS1 + 1;
PTSsum1 := PTSsum1 + sum({@pack},{table.LPN})
) else
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="CCS" then (
cntCCS1 := cntCCS1 + 1;
CCSsum1 := CCSsum1 + sum({@pack},{table.LPN})
) else
if {ITEM_WHSE_MASTER.PUTWY_TYPE} ="BP" then (
cntBP1:= cntBP1 + 1;
BPsum1 := BPsum1 + sum({@pack},{table.LPN})
)
)
;

Then add a reset formula in the Zone group header:

whileprintingrecords;
numbervar cntPTS1;
numbervar cntCCS1;
numbervar cntBP1;
numbervar PTSsum1;
numbervar CCSsum1;
numbervar BPsum1;
if not inrepeatedgroupheader then(
cntPTS1 := 0;
cntCCS1 := 0;
cntBP1 := 0;
PTSsum1 := 0;
CCSsum1 := 0;
BPsum1 := 0
);

Then in the Zone Group footer use this formula:
whileprintingrecords;
numbervar cntPTS1;
numbervar cntCCS1;
numbervar cntBP1;
numbervar PTSsum1;
numbervar CCSsum1;
numbervar BPsum1;

totext(cntPTS1,0,"") + " "+totext(PTSsum1,0,"")+ " "+
totext(cntCCS1,0,"") + " "+totext(CCSsum1,0,"")+" "+
totext(cntBP1,0,"") + " "+totext(BPsum1,0,"")

Leave the original formulas as they are. Suppress all report sections except the zone group footer and the report footer.

-LB

 

[vega83]

Thanks a lot LB, work perfect!!!

 

[vega83]

LB,
EVerything works fine but small problem, the toal packs is adding upon ZONE, when the ZONE changes the toal of DR zoner is beiong carried to ST ZONE Below is example

ST 12 43 3 34
DR 12 43 3 34

Total 12 43 3 34

 

[lbass]

The results for zone need to be in the zone group footer, with a reset formula in the zone group header.

-LB

 

[vega83]

Sorry I missed it LB, work like magic, thanks again for your valubale time, thanks a lot!!!

Vega