Creating Your Own Reports

The Export and Report Generator is an extensive and versatile tool designed to move data out of AnyPO and into a spreadsheet. The generator will automatically start up Excel (or a spreadsheet program of your choice) and from there, you can do a bit of formatting and then print the report. A number of reports have been built in the program but the beauty of this system is that it allows you to create your own reports.

How to Create a Report from Scratch

When you generate a report, it is based upon on something called a profile. A profile is a way of telling AnyPO what data you want in your report.

To begin the process, you start at the Main Purchase Order Screen and select FILE >> EXPORT & REPORT GENERATOR.

The Export and Report Generator Screen appears. From this screen, you begin by clicking on "Create New Profile" (shown below):

POReprt20

The Export Profile dialog box appears (as shown in the illustration below). You'll want to start by entering the name of the profile in # 1 . The name should be something that helps you identify the report.

The type of file is indicated in # 2 . In most cases, you'll select "Text - Tab Delimited." This will allow the program to automatically open Excel with data in place.

The bulk of the dialog box is taken up by # 3 . This is where you indicate what data you want in the report. It's done by selecting a field in the first window, then clicking the big plus + which places the field in the second large window. Each of the fields in this second window become columns on the spreadsheet.

POReprt21

You'll notice near the bottom of the dialog box is the "Mode" (see illustration below) For most reports, you'll use either the "Default" Mode or "Group PO Data by Product." If you are creating a report with just general PO data just as the vendor name and address, then the "Default" Mode works fine. You'll end up with one row on the spreadsheet per invoice exported.

If, however, you need general PO data and product data included on your report, use the "Group PO Data by Product." The "Default" Mode is limited to 3 products. But by using this mode, there is no limit to the number of products and you'll be able to export all product data. When using this mode and building your profile, you only need to use the first product: Product Item (1st), Product Price (1st), Product Quantity (1st) and so. It's not necessary to use the 2nd or 3rd product. More information: Export Modes.

POExprt8

After you've finished creating your profile, click on "Save" and then "Close" to return to the first dialog box. The top of the first dialog box looks like the following:

POReprt18

Once back at the first dialog box, in Panel # 3 , you'll need to indicate a file name and a directory for the file. The data for the report is placed into the file. Excel will use this file when it starts up. Below, we've typed in "PO_Test2.txt" for the file name:

POReprt22

You can control which PO's appear on the spreadsheet by choosing from options available in Panel # 4 ("Select PO's to be Exported." You can enter a range of dates, a range of PO numbers, among other choices (below):

POReprt5

In Panel # 5 you can indicate that you want Excel to start up as soon as AnyPO creates the export file. You almost always will put a checkmark in the first box as shown below:

POReprt16

By placing a checkmark in the first box, the process of creating a report becomes automated. As soon as the export file is created, Excel opens with your data in place.

Let's say you don't place a checkmark in the first box in Panel # 5 above. If so, you can still look at your data. In that case, you need to start Excel manually. Then once Excel starts, you'll open the file that you've created. Using the screen shot above as an example, you would start Excel and then look in the C:\AnyPO\My_Files directory to open the file: "PO_Test.csv."

The manual method works, but it's clearly much easier to automate the process by making sure you've checked the first box in # 5 .

The next to last step is to place a checkmark in Panel # 6 . This provides you with labels for each of the columns on the spreadsheet. That way you can identify exactly the contents of each column. Like the previous panel, you'll almost always place a checkmark here.

POReprt23

Finally, to actually get the export process underway, click on the "Start Export Process" button

POReprt2

After you do this, a dialog box will appear and AnyPO will ask for the path to Excel or a spreadsheet of your choice. (If you have already entered the path to Excel in an earlier process, then the program will skip this step and move on to opening Excel.)

If you haven't yet entered the path to Excel, follow the directions on the dialog box that appears. You only need to do this once. The program will remember it from here on out. (If you ever need to change the path, it can be done in Basic Program Set-up.) Now that AnyPO knows the path to Excel, you'll need to click on "Start Export Process" one more time.

Excel will start with the data in place. If the report isn't quite what you need, close Excel and re-work the profile. Be sure to close Excel before running another export. If you run another export with Excel open, you'll end up with a file sharing error. You'll be trying to create a file that's already open in Excel and that can't be done. Just close Excel and you'll be able to run the process again.

Once you create the profile, AnyPO remembers all of the settings. Next time around it's just a matter of selecting the profile you want and clicking the "Start Export Process" and your report is automatically sent to a spreadsheet.

You can create as many profiles as you need. There is no limit on the number that can be created.

Review the Samples

Included with AnyPO are three sample profiles. We've included quite a bit of detail on how the samples were constructed and other helpful hints. You'll find it very instructive to review each: Sample Profiles

Creating Your Own Reports

By reading through the above material and examining the sample profiles, you'll be well on your way to creating your own reports. Dozens of different types of reports can be created from data found on purchase orders.

One common technique that you may use to create your own report is to construct a filter first and then run an export. This technique is described in a step-by-step procedure here: Using Filtering to Create Reports.

Let's take a look at a couple of specific examples.

Example 1: Report which Lists All Back Ordered Items

Let's say you want to create a report which lists all back ordered items. Here are the steps you would follow:

1. Start at the Main Purchase Order Screen. Select INVOICE TOOLS >> FILTER INVOICES. Click the plus (+) beside "All Items Coded With a 'B' " (The "B" code in the Item Area means back ordered.)

2. Click on "OK" and you'll be returned to the Main Purchase Order Screen.

3. Make sure that the Inventory Mode is turned on by clicking the "Inventory Mode" button. The Item Area should turn a light red color. Page through your PO's. All of the PO's that are displayed will have a "B" in the Item Area for at least one of the products.

4. Once you have tested the filter and you satisfied with the result, turn off the filter (INVOICE TOOLS >> TURN FILTER OFF)

5. Next select FILE >> EXPORT & REPORT GENERATOR.

6. Click on the "Create New Profile" button.

7. Give the Profile a name.

8. Select "Text - Tab Delimited"

9. Select what fields you'd like to have in your report by choosing a field from the first window and then clicking on the big plus (+). The field will be moved to the second window. Each field showing in the second window will be a column in the resulting spreadsheet. (More details on creating a profile is found here.)

10. When you have selected all of the fields that you want, save your work by clicking "Save Profile."

11. Click on the "Other Options" button.

12. The "Other Options" screen will appear. Look near the bottom and you'll see "Paste Filter" Click on it. This will paste the most recent filter (the same one that you created and tested in steps 1 and 2 above) into the Filter blank.

13. Click on the "Save" button.

14. You'll be returned to the profile page. We're all done here. Click "Close"

15. Now you'll be back at the first Export dialog box. Once back to this dialog box, in panel # 3, type in a file name. Let's call it "Back_Orders.txt" but you can give it any name but be sure to include the .txt extension.

16. In Panel #4, select "Use a filter to determine which invoices are exported"

17. In Panel #5, select "If exporting to a dbase or tab delimited file: immediately start-up spreadsheet program for editing and/or printing"

18. In Panel #6, put a checkmark beside: "First Record: List Field Names"

19. Then click on "Start Export Process"

20. If you haven't yet done this, you'll be asked to enter the path to Excel. Look for "C:\Program Files" on your computer and find Microsoft Office and then look for "Excel.exe." (You don't necessarily need to use Excel. You can use another spreadsheet program if desired.)

21. Click on "Start Export Process" once again.

22. Excel will open with a spreadsheet displaying of all PO's with back ordered items.

Example 2: Report Listing PO's Which Have Not Been Approved.

Here's another example. In this example, let's say you want to create a report which lists PO's which have not yet been approved. Here are the steps you would follow:

1. Start at the Purchase Order Screen. Select INVOICE TOOLS >> FILTER INVOICES. Click the plus (+) beside "All Un-approved PO's"