Total Groups that has a certian field populated in the group 1

[Jell0b0y]

Total certian Groups

I am trying to only total a group that has atleast one field that is populated with a sting of VA. I have it Grouped by Carton ID and the VA and TX is a Code and type of the carton. I need to know the total units in a carton if a VA is in the group. I have tried OnfirstRecord but i can only pick the lines with the VA in them. Any help would be appricated.

(Carton ID) (CC) (CT) (Qty)
00000111515
00000111515 VA TX 2.00
00000111515 VA TX 3.00
00000111515 4.00
00000111515 2.00

00000111616
00000111616 3.00
00000111616 5.00

 

[lbass]

Create a formula {@VA}:

if {table.CC} = "VA" then 1

Then use a second formula to display a summary:

if sum({@VA},{table.cartonID}) > 0 then
sum({table.qty},{table.cartonID})

If you need to then summarize this summary, you would have to use a variable.

-LB

 

[Jell0b0y]

That is exactly what i needed lbass thank you very much!

 

[Jell0b0y]

I found and issue with my logic and it causing my totals to be doubled. The forumla that lbass gave me does exactly what i wanted by not counting the groups that doesn't contain VA. I found out that the data is on my report twice and i can only count it once. The duplicate records are the one with the same line number in the group and I can only count that QTY once.

any help would be appricated



(Carton ID) (CC) (CT) (Qty) (line#)
00000111515
00000111515 VA LB 2.00 1.00
00000111515 VA TX 2.00 1.00
00000111515 VA LB 2.00 2.00
00000111515 VA TX 3.00 2.00
00000111515 4.00 3.00
00000111515 2.00 5.00

00000111616
00000111616 3.00 1.00
00000111616 5.00 2.00

 

[lbass]

Should I assume that line 4 contains a typo and that the 3 should be a 2? Do the groups that don't contain a VA need to be displayed? If not, you could go to report->selection formula->GROUP and enter:

sum({@VA},{table.cartonID}) > 0

Then you can insert a running total that does a sum of qty, evaluate on change of {table.line#} (assuming that line# is an actual field), reset on change of carton ID. Put the running total in the carton ID group footer. If you need a grand total, create a second running total like the first except use "reset never".

-LB

 

[Jell0b0y]

Your correct lbass it should have been a 2.


For some reason it is not adding the first line of every other group simmilar to below

(Carton ID) (CC) (CT) (Qty) (line#)
00000111515
00000111515 VA LB 2.00 1.00
00000111515 VA TX 2.00 1.00
00000111515 VA LB 2.00 2.00
00000111515 VA TX 2.00 2.00
00000111515 4.00 3.00
00000111515 2.00 5.00
Total 10.00

00000111520
00000111520 VA LB 2.00 1.00
00000111520 VA TX 2.00 1.00
00000111520 VA LB 2.00 2.00
00000111520 VA TX 2.00 2.00
00000111520 4.00 3.00
00000111520 2.00 5.00
Total 8.00

I want to thank you for the time to helping me on this

 

[lbass]

Is the first row you are showing the group field?

Please explain exactly how you set up the running total.

-LB

 

[Jell0b0y]

I got it, When i setup the running total i set it to evaluate at @line# and for some odd reason it would not count the first line of random groups. I set a formula on evalute to @line# > 0.00 and it worked not sure why but so far it looks like its working.

Thanks lbass!