How To Create a Top Customers Report

Using Excel, we will be able to create the report to show customer sales and sort it to show the top customers for that time period. Using the directions below you can create a User Defined Spreadsheet Report that will allow you to sort the data in Excel.   TO CREATE REPORT:  

1. Select ACR > Report Manager
2. Select Invoice Reports > Report Writer Features > Click on your insert button
3. Choose a standard report > Monthly Invoice Register as the report to use for your default settings
4. Change the output type to 2=generate a text file
5. Leave the sequence number, AGRIS V9 generates this
6. Change the description on the report to TOP 50 CUSTOMERS SPREADSHEET
7. Leave the path field blank and it will create your report in the current dataset folder
8. Enter the file name as "OVER" and leave the extension field blank.
9. Answer Yes to "Create data only output file?"
10. Answer Yes to "Leading sign on numeric fields?"
11. Select OK to open "Data Only File Format" window
12. Select option 4, spreadsheet with headings/
13. Select OK through all following screens until you reach the Sort Field screen.The screen name is located in the upper right corner of the screen.
14. Select Name ID Number as sort level 1 and then "Do you want to sort by name description?" prompt will appear; answer No. Then blank out sort level 2 field.  Name ID Number is the only sort on this report.
15. Leave all screens as they appear until you to the Subtotal & Option Options screen.Remember to look for the screen name in the upper-right-hand corner.
16. Answer N to the question, "Print A/R Invoice Detail?"
17. On the next screen, the Additional Options screen, blank out the discount calculation date.Continue to next screen.
18. On the A/R Invoice Detail Screen, select the <Options> button at the bottom of the screen.Set "characters per line" 1000 and then click OK to exit
19. Use the CLEAR button at the bottom of the screen and answer Y to remove all of the lines in the detail section.
20. Now you have a clean slate to begin choosing the fields you want to print on the report.Scroll down until you find the field name & select to set to the column position.Name ID Number = Column 1Name ID Description = Column 100Invoice Amount = Column 200
21. 21.  Continue to the Payment Detail screen.Use the CLEAR button at bottom of screen to remove all fields defined to print.We will not be printing any of the payment detail information, so CONTINUE button to next screen.
22. Use the CLEAR button to remove all detail lines from the invoice line item screen.Select CONTINUE.
23. Use the CLEAR button to remove all detail lines from the GL/Distribution/Promo/Remarks screenSelect CONTINUE to finish.

  TO RUN REPORT:

1. ACR > Report Manager > Print A/R Invoice Report > Select user defined reports > Select the report you just created.
2. Adjust the report parameters to control the invoice date range.For example, if I want to see the top customers for 2008, my date range would be:010108 to 123108
3. Once the report is run, it will create a spreadsheet file named OVER in your current dataset directory.  The spreadsheet option with headers will cause the report to open in Excel to your screen, if Excel is loaded.If not loaded then the file is created in your dataset & you can open this file from another machine in Excel.
4. Use the Excel sort functions to sort on the invoice amount field (grand total of all invoices for that customer) to place the customers in order of their sales amounts.

  Created By: AMueller