How to retrieve only one row from a table

[SteveBell]

I'm writing a report to calculate margin on our parts. The costs for each part (materials, labor, overhead, subcontracting) are rolled up periodically and stored in a different table.

My problem is how to retrieve only the data I want. I have a set of tables that look like this:

[tt]Invoice Table
Invoice # Item # Invoice Date
12321 1 2001-02-10

Cost Rollup Table
Part # Cost Rollup Date
1 2001-01-15
1 2001-02-01
1 2001-02-14
1 2001-03-30[/tt]

My delimma is how to get the row from the Cost Rollup Table that is just prior to the date in the Invoice Table. By default I get a row in my details section for every entry in the Cost Rollup Table:

[tt]Invoice # Item # Invoice Date Cost Rollup Date
12321 1 2001-02-10 2001-01-15
12321 1 2001-02-10 2001-02-01
12321 1 2001-02-10 2001-02-14
12321 1 2001-02-10 2001-02-30[/tt]

I really want to retrive only the row with the largest Cost Rollup Date that is less than or equal to the Invoice Date:

[tt]Invoice # Item # Invoice Date Cost Rollup Date
12321 1 2001-02-10 2001-02-01[/tt]

I talked to our Baan/Oracle programmer, and she recommended that I (1) select all rows with a Cost Rollup Date less than or equal to the Invoice Date from the Cost Rollup Table into a temporary table, (2) sort the temporary table descending, and (3) retrieve the first row from the temporary table. That's how she would do it using the native Baan report writer.

Any suggestions how to do this in CR?

I'm using native Crystal Reports v7.0 to retrieve item costing data from Baan using the free direct-connect DLL. The reports are compiled to an EXE file.

 

[clydejones]

Steve,
I haven't used Crystal Reports v.7.0. I'm using v.8. But
I would try using the "Record Selection Formula Editor" and write something like:
maximum({CostRollUp.CostRollUp}) and {CostRollUp.CostRollUP}<= {Invoice.InvoiceDate}

PS look up maximum on the online help.

Hope this helps,
Clyde

 

[SteveBell]

Thanks for the rapid response!

This sounds promising, but I can't figure out how to make it work. I tried entering variations of your formula, but I can't figure out how to use the Maximum function in the Selection Editor. The condition
{CostRollUp.CostRollUp} <= {Invoice.InvoiceDate}
works fine. When I try to add
Maximum ({CostRollUp.CostRollUp})
it complains that I don't have a boolean expression. When I try
Maximum ({CostRollUp.CostRollUp} <= {Invoice.InvoiceDate})
it complains that it can't create a summary/running total field.

Any more suggestions?

Thanks,
Steve

 

[balves]

You can't use aggregate functions like Max() without groups.

Leave the selection formula alone, without the Max(). Create groups for Invoice # and Item #. Move your detail fields up to the second group. Create a formula to get the max(cost rollup) there. Hide the detail section. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[MalcolmW]

Brian, I think you meant this, but to be explicit.
Leave in the selection formula
{CostRollUp.CostRollUp} <= {Invoice.InvoiceDate}
This will eliminate all the CostRollUps that happen for items after the Invoice date, which is part of the solution.
The other part, Max({CostRollUpDate},{ItemNumber}) will then give you the latest date prior or equal to the invoice date for the cost rollup for the item. Malcolm