Custom SSRS Report Builder Identifies Purchase Price Variances

Visit Website View Our Posts

A wholesale distributor with multiple locations and over 100,000 sales inventory items is able to close the gap on purchasing variances with a powerful custom SQL Server Reporting Services (SSRS) report.  The company’s purchases and sales are complicated by these factors:

  • They buy and sell both domestically and Internationally
  • Both purchases and sales orders frequently involve drop ship
  • Both inventory and non-inventory items are purchased and sold
  • Sales often require partial shipments
  • Orders may contain 200-300 line items
  • International sales are frequently invoiced to customers when loaded on the ship while the proof of shipment and the vendor invoice aren’t received for several weeks

These factors result in large numbers of purchase price variances so we assisted the client with reconciling inventory by writing a custom SSRS report utilizing a SQL View.  We were able to link the tables in the view by using the historically-saved SOP Order document link to Purchase Orders.  From the Purchase Order Open and History tables we’re reporting:

  •             PO Number
  •             Vendor ID
  •             PO Type
  •             PO Date
  •             PO Status
  •             PO Line Number
  •             PO Line Status
  •             Item Number
  •             PO Quantity
  •             PO Quantity Cancelled
  •             PO United Cost
  •             PO Extended Cost
  •             Receipt: Quantity Shipped
  •             Receipt: Quantity Invoiced
  •             Receipt: Extended Cost Shipped
  •             Receipt: Extended Cost Invoiced

We’re matching that with similar fields from Sales Order Processing Open and History Tables then testing for a variance.  This information is highly valuable to our client when drop-ship invoices are received from vendors several weeks after items have been invoiced to the customer.

After creating the view and testing the report, we’re deploying the result into an Export Solution so the client can generate their report at any time.


Are you a wholesale distributor with difficult to understand and labor intensive financial reports?  Do you want your own customized SSRS reports? Click SSRS Report Builder and simplify your work week by creating quick and easy to understand financial reports, so you can go home early for the weekend.

by Computeration

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons
24