I am working on a custom report based on the sales invoice tables that shows the lot number, the cost from the lot and the vendor from which the items were purchased. I am having trouble pulling the vendor information in to the dataset. Has anyone done this before or can anyone help me with the code? My code is below. Side note: the result in report builder returns multiple rows for each line on the sales invoice, and i am curious to know if there is a better way to write this so that I get 1 row in my result for every line on the sales invoice.
report 50111 "Bulk Invoice Report_dev"
{
UsageCategory = Administration;
ApplicationArea = All;
DefaultLayout = RDLC;
RDLCLayout = 'rdl_layouts\Bulk Invoice Report_DEV.rdl';
dataset
{
dataitem("Sales Invoice Header"; "Sales Invoice Header")
{
RequestFilterFields = "No.", "Document Date";
column(External_Document_No_; "External Document No.")
{
}
column(New_Basis; "New Basis")
{
}
column(No_; "No.")
{
}
column(Payment_Terms_Code; "Payment Terms Code")
{
}
column(Sell_to_Customer_Name; "Sell-to Customer Name")
{
}
column(Document_Date; "Document Date")
{
}
dataitem("Sales Invoice Line"; "Sales Invoice Line")
{
DataItemLinkReference = "Sales Invoice Header";
DataItemLink = "Document No." = field ("No.");
column(Document_No_; "Document No.")
{
}
column(OriginState; OriginState)
{
}
column(Bill_to_Customer_No_; "Bill-to Customer No.")
{
}
column(Location_Code; "Location Code")
{
}
column(Item_No_; "No.")
{
}
column(Quantity; Quantity)
{
}
column(Package_Tracking_No_; "Package Tracking No.")
{
}
column(Unit_Cost; "Unit Cost")
{
DecimalPlaces = 4 : 4;
}
column(Unit_Price; "Unit Price")
{
DecimalPlaces = 4 : 4;
}
dataitem(Location; Location)
{
DataItemLinkReference = "Sales Invoice Line";
DataItemLink = "Code" = field ("Location Code");
column(LocationName_; "Name")
{
}
column(Code; Code)
{
}
}
dataitem("Sales Shipment Line"; "Sales Shipment Line")
{
DataItemLinkReference = "Sales Invoice Line";
DataItemLink = "Order No." = field ("Order No."), "No." = field ("No.");
column(shipment_Document_No_; "Document No.")
{
}
dataitem("Item Ledger Entry"; "Item Ledger Entry")
{
DataItemLinkReference = "Sales Shipment Line";
DataItemLink = "Document No." = field ("Document No.");
column(Lot_No_; "Lot No.")
{
}
column(Item_No_Ledger; "Item No.")
{
}
column(Document_Line_No_; "Document Line No.")
{
}
column(Document_No_Ledger; "Document No.")
{
}
dataitem("Item Ledger Entry_rct"; "Item Ledger Entry")
{
DataItemLinkReference = "Item Ledger Entry";
DataItemLink = "Lot No." = field ("Lot No.");//, "Item No." = field ("Item No.");
DataItemTableView = where ("Document Type" = const ("Purchase Receipt"));
dataitem("Purch. Rcpt. Line"; "Purch. Rcpt. Line")
{
DataItemLinkReference = "Item Ledger Entry_rct";
dataitemlink = "Document No." = field ("Document No."),
"Line No." = field ("Order Line No.");
column(Item_Rcpt__Entry_No_; "Item Rcpt. Entry No.")
{
}
column(Rcpt_Line_No_; "Line No.")
{
}
column(Buy_from_Vendor_No_; "Buy-from Vendor No.")
{
}
dataitem("Purch. Rcpt. Header"; "Purch. Rcpt. Header")
{
DataItemLinkReference = "Purch. Rcpt. Line";
dataitemlink = "No." = field ("Document No.");
column(Buy_from_Vendor_Name; "Buy-from Vendor Name")
{
}
}
}
}
}
}
}
}
}
}
