RDLC report with Item table left join to purchase line

samantha73

Hi Guys
I thought when creating the dataset in AL I have the option to determine join type but couldn't find it. So my requirement is to show all items and for items with open purchase order lines, show the next delivery dates in columns. I used below link to link po line to item:

dataitem("Purchase Line"; "Purchase Line")
{
DataItemLink = "No." = FIELD("No."), "Variant Code" = FIELD("Variant Filter"), "Location Code" = FIELD("Location Filter"), "Shortcut Dimension 1 Code" = FIELD("Global Dimension 1 Filter"), "Shortcut Dimension 2 Code" = FIELD("Glob
DataItemTableView = SORTING("Document Type", Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Expected Receipt Date") WHERE(Type = CONST(Item), "Document Type" = CONST(Order), "Outstanding Quantity" = FILTER(<> 0));

However, using above link I dataset only provides item with a po line as where clause applies to all. Is there another way to get a dataset with a left join or is there a standard report with this kind of logic I can check?
Thanks

vasilis6669

Dear Samantha,

You should be looking at the "Inventory Purchase Order" Report





Join is feasible by using 2 datasets. RDLC reporting works by generated the dataset and then rdlc extracts data from the dataset for the presentation. A dataset is a multi dimension matrix; for example to display all items use the item dataset. inside the item dataset create another dataset

dataitem("Item"; "Item")
{
Field(Item_No; "No." )
{

}

dataitem("Purchase Line"; "Purchase Line")
{
DataItemLink = "No." = FIELD("No.");

Field(PurchaseLine_ERD; "Expected Receipt Date" )
{

}
}
}

Tip: use the OnAfterSubstituteReport after customising above report to display all items.

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-substituting-reports

Regards,
Vasilis

samantha73

Thanks Vasilis, But the issue is when you link two tables (data items) you cannot use something like example above
"WHERE(Type = CONST(Item), "Document Type" = CONST(Order), "Outstanding Quantity" = FILTER(<> 0));"
This filter filters out any item without an outstanding PO Line