Most recent cost in cross-tab 1

[bv0719]

Hello,

Looks like a formula may be the only way to get the value I'm looking for. Have a cross tab report that lists the produced items for the past fiscal year in the rows and the average of the cost in the sum fields. However, we don't want the average, we want the "Last" produced cost.

example:
Oct-07 Nov-07 Dec-07 etc..
clam chowder 9.28 9.05 9.14

The results are queried from a table last lists the produced items on a daily basis.

i.e.
Date Name Qty Price
9/18/2008 Rec-Prod 1 9.18
9/17/2008 Rec-Prod 3 9.15
9/16/2008 Rec-Prod 6 9.22

The cost on 9/18 would be the most recent produced cost and is the cost that should display on the report. The average would be 9.1833 and is not what is required. (Qty is not important for this report)

How can I get the last cost on the cross tab. Maybe a cross tab isn't the best choice either since the field I'm trying to populate is a "summarized" field. Not really looking to summarize.

Any thoughts?

Thanks,
Bill V

 

[IanWaterman]

Do not use Cross tab

Group REport by Nmae
Sort report by Date Ascending
Suppress Group Header and Details

GroupName, Datefield and Pricefield in Group footer.

That will give you the list you want.

Ian

 

[lbass]

How many tables are you using? How are they linked? Can you post the SQL query?

-LB

 

[bv0719]

LB,

SQL Query:

SELECT itmldg.ilgiid, itmmas.itmdes, itmldg.ilgamt, itmldg.ilgdat, itmldg.ilgdno, itmldg.ilgwid, itmldg.ilgnam
FROM ais.itmldg itmldg, ais.itmmas itmmas
WHERE (itmldg.ilgiid=itmmas.itmiid) AND itmldg.ilgwid='10' AND itmldg.ilgdno='MF3050' AND itmldg.ilgnam LIKE 'REC%' AND (itmldg.ilgdat>={ts '2007-10-01 00:00:00'} AND itmldg.ilgdat<={ts '2008-09-30 00:00:00'})
ORDER BY itmldg.ilgiid

 

[lbass]

Are any of the where clause criteria based on parameters? Could you also paste in the record selection formula (report->selection formula->record). Thanks.

-LB

 

[bv0719]

LB,

No, not based on parameters.

Record selection:

{itmldg.ilgwid} = "10" and
{itmldg.ilgdno} = "MF3050" and
{itmldg.ilgdat} in Date (2007, 10, 01) to Date (2008, 09, 30) and
{itmldg.ilgnam} startswith "REC"

Thanks again for the help!

BV

 

[lbass]

Actually, there is a simpler method than I was originally considering. You should always identify your CR version, but from previous posts I see you are using CR 2008, so this will work.

This assumes that you have a group #1 on product and a group #2 on {table.date} on change of month in the main report (even if suppressed), just so you can create the following formula:

if {table.date} = maximum({table.date},{table.date},"Monthly") then
{table.price}

In your crosstab, add the product field as the row, add {table.date} as the column (group options->on change of month), and use a sum of the above formula as one of your summary fields.

-LB

 

[bv0719]

LB,

Correct, I'm using CR 2008. Forgot to include this in the first post.

I'll give that a shot and let you know the results. Thanks again for your support.

Best,
BV

 

[bv0719]

LB,

Worked like a charm! Your help is invaluable.

Best,
BV