I need to modify my inventory valua

[JeffMachine]

I need to modify my inventory valuation report to show the newest and oldest date an item has been in inventory.
I've added a subreport that selects all receipts for each item. I've passed the qtyonhand to the subreport for comparison.
I've ordered the subreport by transaction date descending. This gives me the newest date, no problem there.

Where I am struggling is getting the oldest date.

Example
Main report
Item 123 has a quantity of 3

Subreport - Transactions
(date, trxqty, qtyonhand(passed from main report))
10/31/12,1,2
9/15/12,1,2
8/12/12,2,2
8/1/12,1,2
7/15/12,5,2

So here my newest date would be 10/31/12, I've got that. The oldest date would be 8/12/12 since I have 3 on hand and backing down to that date gives me the last 4 (running total of trxqty) that were received.

How do I determine the last date in the subreport?

Once I can determine this info I'll pass it back to the main report.

I'm using version 2008.

Thanks ....

 

[JeffMachine]

Couldn't figure out how to edit the original post, but the last column of the subreport should read 3 not 2 for the qtyonhand.

Thanks ...

 

[Charliy]

add a formula in the Report Footer of the subreport that is basically Minimum({table.date}) and pass it back to the main report.

 

[RTag]

Instead of retrieving all records, download them to the local workstation and search for MIN and MAX values consider using a SQL like this:

SELECT ItemID, MIN(DateColumn) as MinDate, MAX(DateColumn) as MaxDate
FROM YourTable
Group By ItemID

This will retrieve all the items in one pass without using subreports

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[pmax9999]

If I understand the challenge correctly, you are not looking for the earliest transaction date (which the previous suggestions would have provided), but rather the date of the oldest stock.

Based on the sample data you provided, if there was 3 on hand on 31 Oct, there would have been 2 as at 15 Sep, and 0 as at 12 Aug. Wouldn't this mean that the oldest stock was from 15 Sep rather than 12 Aug?

In any event, I would create the following 2 formulas in the Sub Report:

1. In Report Header Section

WhilePrintingRecords;
Shared NumberVar QoH_Now := {Table.QtyOnHand}

1. In Details section

WhilePrintingRecords;
Shared NumberVar QoH_Now;
Shared NumberVar QoH;
Shared DateVar EARLIEST;
 
If      OnFirstRecord
Then    QoH := QoH_Now
Else    QoH := QoH - {Table.TrxQty};

If      QoH >= 0
Then    EARLIEST := Date({Table.Date})
Else    EARLIEST := EARLIEST

Back in the main report, add the following Formula to return the date of the earliest stock:

WhilePrintingRecords;
Shared DateVar EARLIEST;

This code assumes 12 Aug is correct. If you agree that 15 Sep is the correct answer, amend the QoH >= 0, to QoH > 0.

Cheers
Pete