|
I have written a simple report for our supplier delivery performance that compares the due date on the purchase order to the received date on the GRN. An order can be in 1 of 4 conditions (Late, Over Due, Not Due, and On Time/Early). The delivery status is decided based on a 3 formula's.
Formula 1 (Late/on time Early)
//Formula determines if goods are late or on time allowing 2 days to to enter GRN
if {shipmentitems.createddate} >= ({orderitems.duedate} +2) then "Late" else "On Time/Early"
Formula 2 ( mailto:%7B@Not - Not Recieved)
//formula dermines existance of GRN
if isNull({shipmentitems.createddate}) = true then true else false
Formula 3 (Due/Not Due)
//if there is no GRN formula determines if order is Due or Not Due
if mailto:%7b@Not - {@Not Recieved ( date is null)} = true and CurrentDate > {orderitems.duedate} then 'Overdue' else
if mailto:%7b@Not - {@Not Recieved ( date is null)} = true and CurrentDate = {orderitems.duedate} then 'Overdue' else
if mailto:%7b@Not - {@Not Recieved ( date is null)} = true and CurrentDate < {orderitems.duedate} then 'Not Due'
What I have then done is created a formula for each of the delivery status' i.e. if Late = 'Late' then 1 else 0. This works perfectly for the formula due/not due the value is either 1.00 or 0.00 however the formula Late/on time Early only returns nothing or 1
The report is grouped by supplier id and i sum each of my 4 formulas to determine the total late, overdue, not due, on time/early. Again this works perfectly.
I have then added 2 more formulas 1 that adds late and overdue together to display total late/overdue and 1 that adds not due and on time/early together. - This is where I am having my problem.
I am looking at a supplier who has 1 order and the order is overdue. My values in the group footer are: Late =, Over Due = 1, Not Due = 0, On Time/Early =. Note where I have not inserted a number that is because the field is blank (which I guess is the heart of the problem) (These fields are blank in the description field too). My problem is the summary fields (i.e 1 + Nothing) are blank
|