List Estimates/Jobs - Export for Custom Reporting
In this document we will cover how to configure a list view to create a simple custom report and how to export to excel for reporting.
Please review our Knowledge Based article on creating a list view here to start creating a custom report - List Manager - List Views Create/Edit/Delete Views - Aleyant Systems
To create a custom report to analyze Profit, Unit Price and Unit Cost we will want to use the following columns - Organization, SalesRep, OrderNumber, Quantity, DueDateTime, ProductEngine Name, Price and Raw Cost. Once you have created your new list, select your new list and view. You will notice a blue export to excel box in the upper right:
Once you click on the blue export to excel button your current view will be exported to excel. From excel you can now set up formulas to calculate the Profit, Unit Price and Unit Cost. With the columns of information we have in our view we can calculate Profit (Price minus Raw Cost), Unit Price (Price divided by Quantity) and Unit Cost (Price divided by Quantity) for jobs for any Organization, for any Sales Rep, for any product (Product Engine Name) and for any due date range. See an example of an excel spreadsheet with calculations for Profit, Unit Price and Unit Cost:
The column titles in white with the grey background have been exported from PJM and the column titles in red with the yellow background are calculation columns that have been added after the export. The Profit column is calculated by taking the Price and subtracting the Raw Cost. The excel formula for that cell is (=G2-H2). The Unit Price column is calculated by taking the Price and divide it by the Quantity. The excel formula for that cell is (=G2/D2). The Unit Cost column is calculated by taking the Raw Cost and divide it by the Quantity. The excel formula for that cell is (=H2/D2).
If you have questions or need help, please open a support ticket by selecting this link Aleyant Support