Showing only Goal Route 1

[Ohioste]

I'm trying to create a report to show what route we need to hit in order to hit goal.

In my report, I've pulled in the route #, total units remaining to ship and cumulative total of units remaining to ship my route. I've also added a cumulative total to see after each route, how many units will be complete.

My report is grouped by route #, then order #(suppressed), then individual item on the order(suppressed).

In the group footer section for route #, my report will show Route 1 has 50 units left to ship and a cumulative of 50. Then show route 2 has 75 units left to shift and a cumulative of 125 units(route 1 + route 2). It will continue for all open routes.

Route # is pulled from the database, the number of units remaining to ship is a summary taking each order in the route and subtracting total units by units shipped and cumulative is a subtraction of the running total fields for total units by route subtracted by units shipped by route.

This report is going to end up on a snapshot report for management to show which route needs to be completed to hit the goal for today. So if goal today is 150, I only want to show the first route where the cumulative total is greater than 150.

I attempted to go into as much detail as possible, and hopefully didn't go into too much. Please feel free to ask any questions. I've been able to get all the date to show up, just don't know how to only show the one route we need to obtain in order to hit goal.

Is this even possible? I tried to mess with some formulas and put them in the report footer, but it always seems to return the last route #, even if it's not the one needed to hit goal.

 

[briangriffin]

Sounds interesting, but please provide some sample data, and also the expected results.

 

[Ohioste]

Ok, so I've uploaded a file below.

To explain what each column is:

Column A : Route # from the data source (warehouse management system)
Column B: Formula taking Total Units by Route and subtracting them by shipped units by route to get remaining units by route
Column C: Taking the running total of total units by route and subtracting them by shipped units by route to get a running total of remaining units.
Column D: Running total of routes, just a distinct count
Column E: Formula only returning Column D if Column C > goal(input parameter)
Column F: Same as above only returning route #
Column G: Ended up being same as above only returning 0 or 1

So for this example, the goal is 18,000 units.

The only route I want to show is 20121023010 because the cumulative units is the first route > 18,000

Hope that's what you were looking for.

Thanks!

 

[briangriffin]

My understanding is that the values in your spreadsheet are from the group footers, so in order to get the formula working I'll need to create some detail records, which I won't be able to do until tomorrow.

All the detail records will be hidden, and only the group footer for route 20121023010 will be displayed, right?

 

[Ohioste]

Yes, in order to get the running total to work properly, I had to place all the data in the group footers and all other sections are suppressed.

 

[Ohioste]

And since reading your comment, I realized you're going to try and suppress the group footer fields, so I'm now at the point where I'm only showing routes that have more than the goal. So I'm half way to the formula you were going to write.

Now my problem is, how to suppress all the routes that are greater than my goal, but not suppress the route that gets us to hit goal.

 

[briangriffin]

Put this in the group footer:

//{@Suppress}
whileprintingrecords;

booleanvar x;
numbervar y;

if Sum ({Sheet1_.Cumulative Units}, {Sheet1_.Route #}) < {?Goal}
then
x := false;

if Sum ({Sheet1_.Cumulative Units}, {Sheet1_.Route #}) >= {?Goal}
then y := y + 1;

y


I used a sum function, but you're probably keying on a running total of some sort.

Now you should see 0's up until the first GF that meets the goal, and then it increments by 1 after that. So in your GF suppression formula you would just need:

{@Suppress <> 1

 

[briangriffin]

Typo

{@Suppress} <> 1

 

[Ohioste]

Nicely done. Thank you for your help.