order report by resource and requested shipment date

coentje

Dear experts,

I have to create a report on Resources used for Components ordered by the requested shipment date. Like:

Resource A:
Item ABC...requested shiopment date...additional info
Item DEF...requested shiopment date...additional info

Resource B:
Item DEF...requested shiopment date...additional info
Item XYZ...requested shiopment date...additional info

and so on.

In pure SQL the query looks like:

SELECT	Res."No_",
	Res."Name",
	BOMComp."Parent Item No_",
	BOMComp.Type,
	BOMComp."Quantity per",
	BOMComp."Unit of Measure Code",
	SalesL."Document No_",
	SalesL."No_",
	SalesL."Description",
	SalesL."Description 2",
	SalesL."Outstanding Quantity",
	SalesL."Requested Delivery Date",
	SalesH."Document Type",
	SalesH."Status",
	SalesH."Shipment Date"

FROM	dbo."Company XYZ$Resource" as Res 
		INNER JOIN dbo."Company XYZ$BOM Component" as BOMComp
		ON Res."No_" = BOMComp."No_"
			JOIN dbo."Company XYZ$Sales Line" as SalesL
			ON BOMComp."Parent Item No_" = SalesL."No_"
				JOIN dbo."Company XYZ$Sales Header" as SalesH
				ON SalesL."Document No_" = SalesH."No_"

WHERE 	SalesH."Document Type" = 1 AND
	SalesH."Shipment Date" = '1753-01-01 00:00:00.000'

ORDER BY Res."No_",SalesL."Requested Delivery Date"

I managed to create a report that contains all needed information. But I cann't figure out how to get it ordered by the resource and the requested shipment date. Could someone please point me in the right direction?

Thanks,
Coen

admoretutsie

If you are doing your report in navision, on your resource dataitem,goto properties and on DataItemTableView property, look up and set the key to "Resource No.". The key "No." exists.

That should get you going.

coentje

Thank you, i will try that immediatly. (Sorry for not mentioning, that indeed the report should be done in Navision)