Reporting from log file where status has been 'active' some time.

[Paul12345671]

Hello,
I'm afraid that I've been struggling with this for ages. I'm using CR XI to report from an Oracle 9i database. I have an 'ORDERS' table and an 'ORDERSLOG' table. Once entered, an order will exist permanently in the ORDERS table, but it's status may change in the ORDERSLOG table. a simplified version is shown below. Order No. 53 was created, cancelled, then reactivated.

ORDERS
OrderNo OrderType
53 5

ORDERSLOG
OL_No OrderNo OrderStatus StatusStartDate StatusEndDate
56 53 1 23/10/2008 30/10/2008
57 53 2 30/10/3008 2/11/2008
58 53 1 2/11/2008 <NULL>

ORDERSTATUS
Status Description
1 Active
2 Cancelled

ORDERTYPES
OrderTypeCd OrderTypeDs
5 Blinds

I would like to generate a report showing the order types and number of orders that have been 'active' at least some time in the last quarter. For example, using the above data, the report would say:

Blinds 1
______________
Total Blinds 1

Note that even though the order has been active twice in the quarter, because it is only a single order it should only be counted once.

I've tried doing sub queries, but the computer just sits there (it may be that I'm generating a massive result set somehow.) I said (select max(OL_No) from orderslog where orderslog.orderno = orders.orderno). That worked until I tried to return the item descriptions using a LEFT JOIN. Any help would be greatly appreciated. Thank you.

 

[Madawc]

Group by Order Number. Do a running total that counts using a formula that checkes {Status} and Previous({status}). Show the result in the group footer.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Link Orders to OrdersLog with an equal join FROM Orders to OrdersLog; link OrdersStatus to OrdersLog with an equal join from OrdersLog to OrdersStatus, and link OrderTypes to Orders with an equal join from Order to OrderTypes.

Add a record selection formula like this:

{OrdersLog.StatusStartDate} < dateserial(year(currentdate),(datepart("q",currentdate)*3)+1,1) and
(
isnull({OrdersLog.StatusEndDate}) or
{OrdersLog.StatusEndDate} >= dateserial(year(currentdate),(datepart("q",currentdate)*3)-2,1)
)

Insert a group on OrderTypes.OrderTypeDs.

Then create a formula {@null} by opening and saving a formula without entering anything. Create a second formula:

//{@Active}:
if {OrdersLog.Status} = 1 then
{OrdersLog.OrderNo} else
tonumber({@null})

If orderNo is a string, remove the tonumber(). Then insert a distinctcount on {@Active} at the group and report levels.

This assumes that there is always a record in the OrdersLog if there is a record in Orders.

-LB

 

[Paul12345671]

Thank you for your advice. It's times like this that I realise how far out of my class that I am here. I worked my way through LB's suggestions, made some minor alterations to account for the deficiencies in my original explanation, and the whole thing works perfectly.

Thank you very much - it has been really helpful.