Sales backorder report with available inventory from different locations

Business Central/NAV Developers Forum
1

I want to create a report that shows Sales Lines that have qty to ship and then just a couple of our locations with an inventory count e.g.

Document No. Sell-to Customer No. Line No Location Code No Qty Qty Shipped Location A Location B Location C
SO1234 AlphaCorp 1 Location A ABC123 10 5 0 10 0
SO5678 BetaCorp 3 Location B XYZ789 20 0 20 0 20

This report can be used to work out if we have stock in other locations that could close off the order.

This dataset seems a bit too complicated for a Report Dataset so I have tried to create a query which looks like the following pseudo code:

query xxxxx SalesBackorderQuery
{
    dataitem(SalesLine; "Sales Line")
    {
    
        column(DocumentNo; "Document No.") {}
        column(Line_No_;"Line No.") {}
        column(SelltoCustomerNo; "Sell-to Customer No.") {}
        column(LocationCode; "Location Code") {}
        column(No; "No.") {}
        column(Quantity; Quantity) {}
        column(QuantityShipped; "Quantity Shipped") {}
        
        dataitem(LOCA_Item_Ledger_Entry; "Item Ledger Entry")
        {
            DataItemLink = "Item No." = SalesLine."No.";
            DataItemTableFilter = "Location Code" = const("LOCA");
            
            column(LOCA_Remaining_Quantity; "Remaining Quantity")
            {
                Caption = 'LOCA Remaining Quantity';
                Method = Sum;
            }
            
            //dataitem(LOCB_Item_Ledger_Entry; "Item Ledger Entry")
            //{
                //DataItemLink = "Item No." = SalesLine."No.";
                //DataItemTableFilter = "Location Code" = const("LOCB");
            
                //column(LOCB_Remaining_Quantity; "Remaining Quantity")
                //{
                   //Caption = 'LOCB Remaining Quantity';
                   //Method = Sum;
                //}
            //}
            
        }
    }
}

It works as expected with the second dataset commented out but if I uncomment it the remaining quantity values are incorrect.

Can anyone help with how I can make this work.