Ordering of Ship Dates

[Bennie47250]

Using Crystal Reports version 14.0.4 reporting off an Access 2000 table

Hopefully the length of this question will not scare most folks off.

Working on a report that shows the items on an order and when the item ships. The items in question are highly specific and not used often so we frequently ship what we have and backorder the balance. Sometime it may take us several shipments before we have the order shipped complete.

Have a manager requesting a report to show shipments of these items and he is asking for the ship dates to be listed like I have below.
Order# | Product # | Qty Ordered | Ship Date 1 | Ship Date 2 | Ship date 3 | Ship date 4 | Ship date 5 |

Looking for suggestions of how to determine which Ship date bucket to populate.

As I see it, determining the Max or Min is easy but being able to determine the others is not as easy as the formula needs to know that a date has or has not been evaluated already.

Is this something Crystal can handle? If yes, how. If not I suppose it will need to be determined in the database which will create more questions.

Thanks

 

[hilfy]

Yes, this can be done. Try this:

1. Group by Order# and also by Product#. Sort by ship date within the Product# group.
2. Suppress the product group header and the details section.
3. Create a formula that will store the dates to an array - something like this:

WhilePrintingRecords;
DateVar Array shipDates;
If PreviousIsNull({Product#}) or {Product#} <> previous({Product#}) then
redim shipDates[1]; //Initialize the array to a single element
else redim preserve shipDates[UBound(shipDates) + 1];
shipDates[UBound(shipDates)] := {ship date value};

4. Put this formula in the suppressed details section (it will still evaluate)
5. Create a formula for each date column you need for the report. The first one will be this:

WhilePrintingRecords;
DateVar Array shipDates;
shipDates[1]

The others will look like this (increase the number by one to get the value, if it exists, for each column):

WhilePrintingRecords;
DateVar Array shipDates;
if UBound(shipDates) >= 2 then shipDates[2]

6. Put the data for the product in the Product# group footer, using the formulas for the date columns.

-Dell



DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com