Summary after suppressing 1

[Cpreston]

Hi

I have a report that lists Journeys. It is grouped Journey line drop number.
However some drops are going to the same Post Code so to take out duplicates I have used this supress statement.

IF ({@Pcode}) = previous ({@Pcode}) then True Else fALSE

This does take out duplicates, however I also have a formula which as this code
Sum ({OrderHeader.TotalVolume}) which displya each row with a volume. Using the supress statement takes out some of the totl volume figures, so the gran total at the bottom of the report seems incorrect, but is correct.

So how can I use the suppress but still do a sub total by @pcode so all the figures look correct.

I have tried all sorts of summary totals and different groupings but cant solve it.

Please help.

 

[lbass]

This is a little hard to follow. Could you please show a mockup of how the data is currently displaying (with column labels) and then show how you want them to display?

-LB

 

[Cpreston]

Hi

Sorry. Attached are 2 screen shots

The first one shows the result with the supress of IF ({@Pcode}) = previous ({@Pcode}) then True Else fALSE
This shows 5 lines with the unique post code but only the first. The volume is the figures that make up the Total Volume and the deliveries is correct as 5.

However if I take out the IF ({@Pcode}) = previous ({@Pcode}) then True Else fALSE supress I get 8 rows due to duplicate post codes

So for example for TFN GL3 2LS it shows twice with Volumne of 7.31 and 9.92, but when I supress I only get the first one of 7.93. I want it to show the total of both = 17.23
The same with CRS BA5 1HU in figure 2 it shows as 1.03, 3.74 and 0.02 but in figure 1 it shows as the first one of 1.03, I want it to show = 4.79

The total volume and Total deliveries are ok, however when suprssed the total volume looks strange as if you add up the totals of figure one they would not add up to the correct Total Volume of 31.96.

So the only way I can think of making it right is somehow having a sub total in some way of the duplicate post codes and display this when supressed and this is what I cannot find a way to achieve.

Hope that explains it all better

Thanks

 

[IanWaterman]

Replace your summary with a Running Total.
In the evaluate section check change of Group or change of Field Postcode

Ian

 

[Cpreston]

Hi Ian

No that just adds each one to each other. So I get

7.31 + 0.03 = 7.33 etc... with the total being 18.28. What I am after is showing the sum of each group not a running total.

Thanks anyway

 

[lbass]

You haven’t labeled anything, so it is still hard to tell what’s going on. Looks like you just need to group on the vendor, place fields in the group header, right click on the volume field in the detail section, and insert a sum at the vendor group level. Then suppress the detail section.

-LB

 

[Cpreston]

Hi

Thanks for the reply. Sorry what do you need labelling, I don't understand.

I have tried grouping on the Vendor but this does not work at all, I need to keep the main grouping as the Journeyline.Dropnumber as I need it to be in drop order.
As soon as I add another grouping and try and move the details, I loose the drop number order and also the sub totals no longer work.

Thanks

 

[Cpreston]

Hi

I have tried group on the vendor several time. I need to keep the main group as Journeyline.dropnumber.
If I add another group in then I loose the drop number order and also the sub totals do not add up correctly.

Also not sure what you mean by labelled anything, what should be labelled to make it easier to explain?

Thanks

 

[lbass]

I can’t tell what fields relate to the display. Maybe show a screen shot of the design view of the report.

-LB

 

[lbass]

Group Number 1 = Journey Line Drop Number. Add a second group on post code, and move all fields to the post code group header. Suppress group header #1. Then place the volume field in the suppressed detail section and right click on it->insert summary->sum at the post code group level. Move the result from the group footer to the post code group header. You don’t need a suppression formula at all.

If this still doesn’t return the results you want, please show the results you do get and explain in what way it isn’t what you are looking for.

-LB

 

[lbass]

I don’t know what a Journey Line Drop Number refers to, but you would only get the above results if the postal codes appeared in different Journey Line Drop Number groups. Can you please put the JLDN in the Group #1 header and then unsuppress it and show the results for the same data?

-LB

 

[Cpreston]

Hi

Yes it does this, the system think s there is 8 drops, but in reality there are only 5 deliveries as some drops have the same post code.

This is because each drop as a different Sales Order Number, so this is why it shows there are 8 and not 5. Hope this makes more sense.

 

[lbass]

Ok, assuming that drops with the same postal codes are clustered together, you could just move all fields from the postal code group header (GH2) to the postal code group footer (GF2). Then insert a running total in GF2 that does a sum of volume, evaluate for each record, reset on change of group->postal code. Use this instead of the volume field summary in your GF2. You will need to use a suppression formula on the GF2 section as you did before.

-LB

 

[Cpreston]

Hi

I have tried what you suggest but the running total is not working and also I have now lost the 2 last rows.

The running total is set as this

The code for the suppress is

(
if (Previous({@Pcode}) = {@Pcode}) then
true
else
false
)

Thanks for your replays, I appreciate the help, I will keep trying. Thanks

 

[lbass]

Change the suppression formula for the group footer section to:

Not onlastrecord and
{@Pcode}=next({@Pcode})

-LB

 

[Cpreston]

Hi

Ok changed the suppression formula as you suggested and now have the report as below, the Totalvolume are now showing the last figure per post code but not the sum of the postcode.

 

[Cpreston]

Sorry forgot the other screen shot showing the report

 

[Cpreston]

Hi

Another update, I believe I have it working now, I changed the Reset to do it on the change of field. And it appears ok now. I will test with other Journey Numbers but should be ok. Thanks for your patience and advice.