Summary Formula or Sub report?

[simpelli]

Greetings,

I'm creating a report that displays current product requirements and a sum of five-day demand. I need to show product SKUs by type, the current demand, the ship date, and the TOTAL demand for each SKU with ship dates +1 up to +5 days out. In short, it needs to look something like this:

GROUP: Item Type
DETAIL:
SKU ON HAND ON ORDER SHIP DATE Day1 Day2 Day3 ...
XXX 55 24 06/29/2003 30 2 34 ...
YYY 35 12 06/29/2003 19 12 14 ...
ZZZ 41 30 06/29/2003 26 8 20 ...

But the ship date is part of the record, so what I'm getting when I try to sum is more like this:

GROUP: Item Type
DETAIL:
SKU ON HAND ON ORDER SHIP DATE Day1 Day2 Day3 ...
XXX 55 24 06/29/2003 0 2 0 ...
YYY 35 12 06/29/2003 0 0 14 ...
ZZZ 41 30 06/29/2003 0 0 0 ...

I know that this can probably be done easily enough with a sub report, but I don't really want to go that route because of the delays in processing the subreport after the main report is evaluated.

I'm using 8.5 (project will migrate to 9, so I can build either way) Hitting Informix database

Thanks,

SI

 

[synapsevampire]

Pretty difficult to analyze wiothout knowing how the data is stored, please post table layouts.

-k

 

[simpelli]

(Guess that WASN'T a very informative, sorry...)

I have ORDER_MASTER (om_f) joined to ORDER_DETAIL (od_f)
on ID and TYPE and to INBOUND_ORDER_MASTER (ibom_f)on ID and TYPE
INBOUND_ORDER_MASTER Joined to INBOUND_ORDER_DETAIL (ibod_f)on ID and TYPE

ORDER_DETAIL to PART_MASTER (pm_f) on SKU and PKG

PART_MASTER to INVENTORY (iv_f) on SKU and PKG
(FULL QUERY BELOW)

SELECT DISTINCT
om_f.ob_type, om_f.ship_date, od_f.sku, od_f.ord_qty, od_f.sched_qty, od_f.ship_qty, od_f.cmp_qty, od_f.hold, ibom_f.arrival_date, pm_f.sku, ibod_f.ord_qty, ibod_f.cmp_qty, iv_f.qty, iv_f.alloc_qty
FROM
om_f om_f,
od_f od_f,
pm_f pm_f,
iv_f iv_f,
OUTER ( ibom_f ibom_f,
ibod_f ibod_f)
WHERE
om_f.ob_oid = od_f.ob_oid AND
om_f.ob_type = od_f.ob_type AND
om_f.ob_oid = ibom_f.ib_oid AND
om_f.ob_type = ibom_f.ib_type AND
od_f.pkg = pm_f.pkg AND
od_f.sku = pm_f.sku AND
ibom_f.ib_oid = ibod_f.ib_oid AND
ibom_f.ib_type = ibod_f.ib_type AND
pm_f.pkg = iv_f.pkg AND
pm_f.sku = iv_f.sku AND
om_f.ob_type LIKE '%' AND
pm_f.sku LIKE '%'
ORDER BY
om_f.ob_type ASC,
od_f.sku ASC

I need the Inbound Order tables because they carry a date field that I need to evaluate along with the ship date in the earlier post.

I actually need two formula fields for the report
I'll call this one {@qtyIN}
Sum({ibod_f.ord_qty},{od_f.sku})-Sum({ibod_f.cmp_qty},{od_f.sku})

This One {@qtyOUT}
Sum({od_f.ord_qty},{od_f.sku})-Sum ({od_f.sched_qty},{od_f.sku})-Sum ({od_f.cmp_qty},{od_f.sku})

So, based on ship day, +1, +2 etc, I need the sum of {@qtyIN} where {ibom_f.arrival_date}={om_f.ship_date} for the SKU

I need the sum of {@qtyOUT} where {om_f.ship_date}=CurrentDate (and +1,+2 etc) for the SKU

I have created formula fields ({@IN1}, {@IN2}, etc and {@OUT1}, {@OUT2}, etc) to hold the values, but I am unsure how to get these totals. I'm using a conditional sum based on SKU, but I need SKU and Date

Does this help?

 

[synapsevampire]

I'm not sure what shipdate represents, is it always yesterdays date, or do you look historically at what was shipped on a given day and then what was actually shipped afterwards to determine a historical forecast?

This is a fairly complex bit of business, I'd address it through Views/SP's rather than within Crystal.

-k

 

[simpelli]

The ship date is historical or future (planned/scheduled) Ship date can be selected by parameter. The demand is for future - next five days.

Would a SQL Command (CR9) provide what I'm looking for?

-SI

 

[synapsevampire]

You could use SQL with subqueries to do this, and that's akin to the approach I would take for performance and maintenance reasons.

-k