Frustrated Running Total

[fredp1]

I'm trying to produce a Customer On Time Delivery (OTD) report which shows the % of Orders delivered on time within a date range, by Company, Division, Customer.

The report has hidden groups, allowing you to drill down to the lower levels.
The problem is, when using running totals, the 'Totals' get summarised to the level you are looking at.
(unfortunatly you can not sum a summary variable I think?)

The hierarchy is Company id, Division id, Customer id, Order id, Del id.
An Order can have multiple deliveries.

E.g If a Division has 10 on time delivered orders, comprising of 2 customers, then when you drill down to a customer which has 6 on time delivered orderes, the Division total changes to 6.
1) How do you avoid the totals been filtered when driling down and using Running totals?
Will the 3 variable running total technique avoid this?

The report is based on a single table,
CompID,DivID,CustID,OrderID,OrderQty,OrderDueDate,DelID,DelDate,DelQty

Multiple deliveries can occur on the same day.
I want to be able to measure if the total qty delivered = Order qty and the last delivery date <= Order due date.
(I can add any columns to the table if needed.)
I've already added a delivered running total, and a flag to indicate if the order is on time. The problem with the flag is that, if last 2 deliveries which make the order on time, there will be 2 records with a On time flag set. So using a sum function to count the number of orders are on time will be wrong.
At the Order level I use the maximum of on time,
but i cannot then sum this summry.

Does this makes sense?

Any help will be appreciated.

 

[SmpTraining]

Try solving this problem with a different approach.

Instead of using Running Totals (which evaluates at the While Printing Records stage) use a formula counter.

Create a formula to be placed at the detail level that returns 1 or 0 depending on whether the order was delivered on time. The sum the formula field for each of your groups. Problem solved? Steve Phillips, Crystal Consultant

 

[fredp1]

Hi Steve,

Thats sounds fine, but the tricjy thing is that the detail is level is delievry and the flag needs to be at the Order level (1 level up).

This is what my problem is. I can have a flag at the delivery level , then use a summary function at the order level, but I get can get more than 1 deliveries on the day the order is due.

Ideally if I could only get a formula which will flag the last delivery record of the order.

Any clues?

The current data looks like this.

OrderId, Delid, Del qty, Order Qty, Order Date, Del Date, Ontime
1,1,10,20,4/4/03,4/4/03,Yes
1,2,10,20,4/4/03,4/4/03,Yes

The last 2 deliverys happend on the same day and the on time flag is for both deliveries.

How do you flag the last record?

Thanks

 

[SmpTraining]

I won't have much time to help today but you will need to find a way of getting the summary value you want at the group footer level without using While Printing Records formulas (or Running Totals).

I'll try to look at this later today...

In the mean time, what if you create a formula converting the Yes/No field into a numeric 1 or 0, then show the minimum of this formula at the order level? This would show a 0 if there's ANY late delivery and a 1 if all deliveries were on time. Can you work with that field?

Sorry if this isn't a good suggestion...need to leave in a minute. Good luck. Steve Phillips, Crystal Consultant

 

[lyanch]

create a global variable formula intialized in your report header

then create a formula

global numbervar totalOntime;

totalOnTime := totalOnTime + (iif(max({@ontime},{Order}),1,0);

and place it in your group footer (you don't have to display it.. it just needs to be there)

Then in the report footer:

global numbervar totalOntime;
totalOntime

to diplay it.

Lisa

 

[fredp1]

Thanks for your suggestion Lisa, but I still must be missing something.
The variable that is created will show the &quot;On time&quot; delivery for a Order, but I then what to sum those On time orders at a cutomer level, then sum that total again to get a division level etc.

What I found is that you cannot create a summary expression on that formula.

Any ideas?

Just summarising what the report needs to do.
The report is to show a OTD for a Division, then you drilldown to the customer, then drill down to the order.
A Order is made up of more that one delivery which more than one order may be flaged on time.

 

[fredp1]

I ended up creating a flag in the SQL Server table as i beleive that you cannt do it in Crystal.

Thanks for everyones help.