QuickReport Items on Invoice
This Tech Notes explains creating a "QuickReport" to give information on the Items you have sold. This is useful for reporting information on how many and to whom am I selling product. To create this report we will be working with the Line Items for Invoices during a month period of time.
You are familiar with basic navigation in CommerceExpert/WebClerk.
You are familiar with the "Search Editor".
Transfer Line Items to a Direct File
Line Items in each "Invoice" record are a Subfile, therefore we need to transfer them to a "Direct" file.
1. Using the "Search Editor" find all Invoices for a given month. Open them in the Output screen.
2. Click on the "Trans" button. This creates a "Direct" file from all Items on the Orders in the selection. A dialog box opens at the beginning of the procedure. Click the "OK" button to initiate the procedure.
The procedure is run in the background and the length of time it takes is directly related to the number of records involved.
3. When the procedure is complete an Output screen of the created "InvLine" records is displayed.
Create your QuickReport
1. File Menu > Print QuickReport
As the "QuickReport Editor" opens the current file's fields (InvLines file) are displayed along the left hand side of the screen.
2. Below is a list of the fields and formula included in this report:
Item # ItemNumber
Qty Shipped Quantity Shipped
Ext Price Extended Price
Ext Cost Extended Cost
Comm Paid Formula : CommRep + CommSales
Profit Formula : Ext Price - Ext Cost - C1
Margin Formula : (C2/Ext Price)X 100
Drag each field across to create a column for each one. See the section titles "Adding Formulas to a Quick Report" if you are unfamiliar with that feature.
3. Drag the <<Add Sort>> symbol to create a Sort on the field Item Number.
4. Highlight the "Total" row and then choose: Edit menu > Insert Break. This add a break between each change in the first field that you Sorted by. In this case it would be the" ItemNumber" field. It will group all like Item numbers.
5. Highlight the following columns one at a time and click on the "Sum" box:
Qty Shipped Comm Pd
Ext Price Profit
Ext Cost Highlight the Margin Column and Click on the Avg box.
6. You can format type and numbers as well as set up titles for the report. See the chapter on "Quick Reports" in the manual for details on doing this.
7. When you have the report set up as desired choose: File menu > Save . Name the report "QR[InvLns] Items on Invoices". Save it to the "jitReports" folder.
8. Click on the "Print" button.
9. The "Inv Line" records we created for the report can then be deleted. You delete the records by choosing: Edit menu > Select All,and then choosing: Actions menu > Delete/Void.
This is just an example of a report using the "Invline" file. There are many other option for "QuickReports" using this file. You can pull up reports that will tell you which Customer's bought a certain product for example.