RDLC report with Item table left join to purchase line

samantha73
samantha73 Member Posts: 118
in NAV Three Tier
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

Answers

  • vasilis6669
    vasilis6669 Member Posts: 109
    Dear Samantha,

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

    21snmjf3w2u7.jpg

    s81a5p7k6adz.jpg

    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
    samantha73 Member Posts: 118
    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

Categories