Crystal Report 10

[PNorm]

Hello,
I have a report that works fine until I add in a column from another table that pulls in more data. The linking is not right. I am working with the IDE for crystal reports so I am limited in what can be done.

I only want the last shipdate. But when added in the QTYOPENTOSHIP is overstated by alot.

SQL:
SELECT "vdvSalesOrder"."CustID", "vdvSalesOrder"."CustName", "vdvSalesOrder"."TranID", "vdvSalesOrderLine"."ItemID", "vdvSalesOrderLine"."ItemShortDesc", "vdvSalesOrderLine"."QtyOpenToShip", "vdvSalesOrderLine"."RequestDate", "vdvItem"."ItemClassID", "vdvItem"."CompanyID", "vdvSalesOrderLine"."ShipDate", "tsoShipLine"."ShipDate"
FROM (("mas500_app"."dbo"."vdvSalesOrderLine" "vdvSalesOrderLine" INNER JOIN "mas500_app"."dbo"."vdvSalesOrder" "vdvSalesOrder" ON ("vdvSalesOrderLine"."SOKey"="vdvSalesOrder"."SOKey") AND ("vdvSalesOrderLine"."CompanyID"="vdvSalesOrder"."CompanyID")) LEFT OUTER JOIN "mas500_app"."dbo"."vdvItem" "vdvItem" ON ("vdvSalesOrderLine"."ItemKey"="vdvItem"."ItemKey") AND ("vdvSalesOrderLine"."CompanyID"="vdvItem"."CompanyID")) LEFT OUTER JOIN "mas500_app"."dbo"."tsoShipLine" "tsoShipLine" ON "vdvItem"."ItemKey"="tsoShipLine"."ItemKey"
WHERE "vdvSalesOrderLine"."QtyOpenToShip">0 AND "vdvItem"."CompanyID"='SMT' AND NOT ("vdvItem"."ItemClassID" LIKE 'Proto' OR "vdvItem"."ItemClassID" LIKE 'Raw Mtl' OR "vdvItem"."ItemClassID" LIKE 'Repair' OR "vdvItem"."ItemClassID" LIKE 'T')
ORDER BY "vdvSalesOrder"."CustID", "vdvSalesOrderLine"."ItemID"

Any advice would help.

Thank you.

[PNorm]

I am thinking that I have to get the max date for Shipdate but am not familiar with loading an sql in the IDE SQL expression field. So, how would I load in the SQL:
(
select max('ShipDate')
from tsoShipLine A
where A.'ItemKey' = 'tsoShipLine'.'ItemKey'
)

Then reference it to my original sql listed above.

Thank you,
PN.

[jggtz]

You could use adding a new "SQL Expression Field"
To do that, use the Field Explorer (as we do when we want to insert a new Formula Field)

JG

[PNorm]

Could you expand a little more on what needs to be done? What about Maximum(shipdate, itemkey)? Or using summary function in the IDE?

[PNorm]

So, I want to get the max date from the new table keeping the old selection. The new table is tsoshipline. Any advice?

[PNorm]

Ok. Lets back up....
I have a record that is accumulating data from different tables. I want to add in distinct values from yet another table so the accumulated data is not doubling or tripling up. What recommendation would one follow? It is almost like I need to grab the first occurrence of the price for the item and use that all while outside the accumulation.

[PNorm]

Ok. I think I have started down the path to a solution. The situation is that I have report that is grouped by Item, workorder then some summary data is aggregated. This creates the problem by doing a table join. So, I created a subreport that gets the necessary information. Now, I just need to take the two fields off the subreport and put them on the main. Oh, the main is parm driven so I have to pass the parm down to the subreport so it knows what item to go and accumulate.

How can I pass the parm to the subreport?
How can I put the subreport information on the main?

Any advice?

Thankx.