Actually, I think there is a little more to it than that if you want to get the correct cost based on the accounting date. I can think of a couple of ways to do this.
Using the tables
1. Create a formula to convert the Accounting Date to the same format as the Costing Date. For this example, I'll call it FormatAcctgDate. It will look something like this (note that the format string IS case sensitive!):
ToNumber(ToText({table1.AccountingDate}, 'yyyyMMdd'))
2. Go to the Select Expert. If you already have criteria, click on the Formula button and enter something like this:
If you don't have criteria, select a field, don't enter any criteria, click on the formula button and enter the same thing as above without the "and".
3. Group on Item Number and then on Accounting Date.
4. Sort on CostingDate descending.
5. Suppress the details section and put your data in the Accounting Date group header section. This will give you the record with the most recent Costing Date that is less than or equal to the Accounting Date.
You can use this solution if you don't need to do any summaries (sums, counts, etc.) Summaries won't work because the result set will include ALL of the costing data for a item that is prior to the Accounting Date, not just the most recent. Using your example data, if you needed to add all of the costs together, you'd get the cost from both the most recent record AND the earlier one added to your sum. There might be a way around this limitation using variables, but there's a less complicated way to handle it if your database will support it.
Use a command instead of the tables
For many types of database connections you can create a "Command" which is a SQL query to return the data you're looking for. This will be in the specific SQL syntax that your database uses and (using SQL Server syntax) might look something like this:
Select
<list of fields required for the report>
from Table1 as t1
join Table2 as t2
on t2.company = t1.company
and t2.itemnumber = t1.itemnumber
where
<selection criteria>
and t2.costingdate =
(select max(t2a.costingdate)
from table2 as t2a
where t2a.company = t1.company
and t2a.itemnumber = t1.itemnumber
and t2a.costingdate <= Cast(Convert(varchar, t1.accountingdate, 112) as integer))
I have used this same technique in similar situations and it can work quite well if you're used to writing SQL. I alway design and test the SQL directly on the database, usually in Toad but there are other tools that come with your database if you don't have Toad. Once I have it right and I've verified the data it's returning, I'll copy and paste it into the Command window in Crystal.
-Dell
Edited by hilfy - 29 Dec 2009 at 11:07am
IP Logged
