There may be some of you out there who are wondering how many items your suppliers have short supplied you, and what is the value of the goods they have Short Supplied you? The following is a report that can be made up in Report Generator which will give you this information.
- Create a New Report by clicking the “New” button on the Report Generator Maintenance screen.
- Give the Report a Name. For example, “Short Supplied Goods from Suppliers”
- Select the following fields from their appropriate tables (which are listed in the drop down list immediately under the report name).
Print List
- Order Number (Orders table)
- Order Reference (Orders table)
- Product ID (Orders table)
- Order Qty Buy / Units (Orders table)
- Total Received Quantity (Orders table)
- Function: “Order Qty Buy / Units”-“Total Received Quantity – Cancelled Quantity”
- Transaction Date (Orders table)
- Cost (Product table)
- Function: “Cost”*(“Order Qty Buy / Units”-“Total Received Quantity” – “Cancelled Quantity”)
- Supplier Number (Supplier table)
- Supplier Name (Supplier table)
Sort List
- Supplier Number (Supplier table)
Qualify List
- Supplier Number is from XXXX to XXXX PROMPTED AND
- Transaction Date is from XXXXXX to XXXXXX PROMPTED AND
- “Order Qty Buy / Units”-“Total Received Quantity” – “Cancelled Quantity” is NOT from 0 to 0 AND
- Order Status is from 10 to 20
For the purposes of this example, the Supplier Name and Number have been added to the Print List, and also added to the sort list, so you can see all Suppliers, but in order of Supplier Number. You don’t have to do this if you are qualifying by the Supplier Number, however you might want to if you are looking at all Suppliers at once.
Renaming columns in Report Generator
Don’t forget, if you are adding functions to your report and putting them on your Print List, you probably don’t want the Formula to be listed as the column heading. To change this, click on the box with 3 dots 
to the right of the Function, after you have added it to the Print List. This will bring up another screen with information about the field that you can change. It will by default highlight the name of the column first. Simply type over that text with what you want your column name to be. For example, the first function in the print List above is showing the quantity of short supplied goods. You can simply type in “Short Qty” (without adding the quotes) as the column heading, and this is what will be displayed as the column heading when you print the report.
Functions:
Functions are ways of adding calculated fields into the system. It takes fields that already exist, and depending on what the function says, can output different results. For example:
1. The first function, “Order Qty Buy / Units”-“Total Received Quantity” – “Cancelled Quantity” will show the amount short supplied. This takes the Ordered Unit, subtracts the Total Received Qty, and then subtracts the Cancelled Quantity from the Stock Receipt.
2. The second function, “Cost”*(“Order Qty Buy / Units”-“Total Received Quantity” – “Cancelled Quantity”) shows the value of the goods short supplied. This is exactly the same as the above function, except that it takes the result, and multiples this value with the Cost of the product, resulting in the value of the goods having been short supplied.
Once added to the Print List, you can “Total” these fields. This means you can see the total at the bottom of the report on this column. To do this, click on the box with 3 dots to the right of the field name once added to the Print List, and click in the “Totals” drop down. Select “Total” from this list, and it will total the column.
If you are qualifying the report by one Supplier Number, when you run the report, the total will be the total for the Supplier in question.
Setting up Wrappers to run the Report Automatically
You might want to run this report automatically at set intervals. For example you may want the previous weeks’ data to be sent via email to a certain email address.
Let’s assume you want this report to run at 3am every Sunday night to get the previous weeks data. You would set the Supplier Number qualifier to incorporate all Supplier Numbers in your system, and the Transaction Date range would need to get last week’s data.
Use the following as a guide for setting this up:
Qualify List
- Supplier Number is from XXXX to XXXX AND
- Transaction Date is FROM REAL1WEEKAGO TO REALYESTERDAY AND
- “Order Qty Buy / Units”-“Total Received Quantity” – “Cancelled Quantity” is NOT from 0 to 0 AND
- Order Status is from 10 to 20
Note: Wrappers cannot run automatically if you have qualifiers set to prompt.
For more help with Wrappers, please contact our trainers.