Custom Margin Report in NetSuite

Requirement

Our client required a Margin Report with the item name and transaction details including Quantity, Date, Document Number, Document Type, Document Status, Amount per item, Ship Date, Sales Rep, Vendor, and Manufacturer. The transactions include Sales order, cash sale, invoice, purchase order, vendor bill, vendor payment, customer deposit, Customer refund, item fulfillment. The connection between each type of record is centered on sales order. 

Solution

Adding Purchase order related Records

Our developers approached the requirement by running a script to find out related sales orders of vendor bill and bill payment. These details are further attached to the ‘SO Related Records’ tab in the sales order. Importantly, this is exclusive to dropship Purchase Orders only.

Generating Margin Report

We began by creating a custom page for the ‘Margin Report’. From this page, users can generate the margin report based on filters and download the generated report. 

The margin report custom page will have the following elements:

• Filters:Sales Order Date, Tran date, Ship Date (start date and end date)

• Button:‘Generate report’ 

On the Margin Report page, users can add the necessary filters and click on the ‘Generate Report’ button on the page. On the button click, we will create a custom record to store the request, and based on that another backend scheduled script will be initiated which will generate the report in the background.

Further, the report will be sent to the logged user along with the file link and by clicking the link the user can download the report. Our developers made use of a scheduled script to implement this.  

Result

Our client was satisfied with the Margin Report as they can now seamlessly look into reports by tinkering with the various filters and make sense of data easily.