Built-in Reports

The Report Generator allow you to tailor reports in AnyPO to your needs. You can reach it by selecting FILE >> EXPORT AND THE REPORT GENERATOR from the Menu Bar on top of the Main Purchase Order Screen.

To control which fields from the Main Purchase Order Screen show up in the report, AnyPO uses something called a profile. (More details on the report generator and profiles are found here.)

To help you get started creating reports, AnyPO comes with several built-in reports. You can use the profiles as is - or alter them to your needs.

There are two ways to access information on how to create the reports. One way is to select from the links immediately below. The other way, sort of a visual index method, is to view the spreadsheets created by the reports and then click on a link with more information. You'll find that here: visual index.

•	Sample PO Export (CSV File) - This creates a file which can be opened in any spreadsheet or database program. It consists of a list of purchase orders and includes the PO Number, PO Date, Vendor Name and Address, and the total of each order.

•	Sample - Send to Excel - This is the same report as above, but this profile automatically starts Excel or a spreadsheet of your choosing. The columns of the spreadsheet include PO Number, PO Date, Vendor Name, Address and PO's totals.

•	Sample - Products Ordered - This report also automatically starts Excel and it includes a list of products ordered on each PO. The columns of the spreadsheet include the vendor's name, quantity, item name, price and total.

•	PO Code Report - Groups purchase orders by a code that you have assigned to each, provides totals for each code and a grand total

•	Job or Account Code Report - Groups purchase orders by a job or an account code, provides totals for each job or account code and a grand total.

•	Vendor Report - Groups purchase orders by vendors, provides the total amount purchased from each vendor and a grand total

•	User Defined Report - Groups purchase orders by a category or code that you have used in the "Terms (or UDF)" field on the Main Purchase Order Screen. (The terms field is a flexible field that you can use for your own purposes.) Additionally, it provides subtotals of each and a grand total

•

Combination Report (First User Defined and then PO Code). This report allows you to categorized purchase orders in two levels. You can utilize a user defined field (using the flexible "Terms (or UDF)" field) as one category and then the PO code as a sub-category. It provides subtotals, totals and grand total.

•	Combination Report (First PO Code, then User Defined) This report is the same as above, but it changes the order. In this report the PO Code serves as the main category with the user defined field as a sub-category under it. It provides subtotals, totals and grand total.

•	Combination Report (PO Code followed by Job/Account Code). This report is similar to the previous two. The PO code serves as the main category with a job or an account code as a sub-category under it. It provides subtotals, totals and grand total.

The information below is primarily concerned about how to use the built-in reports that come with AnyPO. But you are not limited to these reports. With the Report Generator you can create dozens of your own customized reports. You'll find a nice summary of how to build your own reports here: Creating Your Own Reports

Start at the Export and Report Generator by selecting FILE >> EXPORT AND THE REPORT GENERATOR. From the list of profiles (Panel # 1 ) at the top of the screen, select "Sample PO Export (CSV File)" as shown below:

This profile creates a CSV file which can be opened by Excel or other spreadsheet programs. It's easier to have AnyPO start up your spreadsheet program for you (the next series of reports do that), but we wanted to include this one example in the event that it might be useful for your situation. The report consists of a list of purchase orders issued and includes the PO Number, PO Date, Vendor Name and Address, and the total of the order.

You can see how profile is constructed by clicking on "View or Update Existing Profile." When you do that, the following screen appears:

You'll see the name of the profile in Panel # 1 . The name should be something that helps you identify the report.

The type of file is indicated in Panel # 2 . In this case, we've chosen "Text - Comma Delimited." Usually, you select "Text - Tab Delimited since you can more completely automate the process, but, for this sample, we'll stick with comma delimited (illustrated below). When you eventually open the file, Excel will ask how you wish to divide the columns and since "comma delimited" is the choice, you'll divide by commas.

The bulk of the dialog box is taken up by Panel # 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.

After you've finished looking at the dialog box, click on "Close" and return to the first dialog box. The top of the first dialog box looks like the following:

Once back at the first dialog box, you can run the report by clicking on Panel # 7 "Start Export Process" as illustrated below

After the export process is finished, you'll need to start up Excel (or a spreadsheet or database program of your choice). Once Excel starts, open the file named PO_test.csv which will be found in the C:\AnyPO\My_Files directory. As mentioned above, since you created a comma delimited file, you'll need to tell Excel to divide the columns at the commas.

The spreadsheet will list the PO's in order of PO Number. You can re-sort the list or manipulate it any way desired, and when ready, print it. (The next sample report will demonstrate how you can automatically open Excel, but this one is a good one to start with to understand what's going on in the background.)

You can control which PO's appear on the list by choosing from options available in Panel # 4 ("Select PO's to be Exported"). For example, you can indicate a range of PO numbers that you desire in your report:

In the above sample, we created a file and then manually opened Excel. You can, however, make things much easier by having Excel automatically open with the data in place. That's what this sample will do.

Start at the Export and Report Generator by selecting FILE >> EXPORT AND THE REPORT GENERATOR. From the list of profiles (Panel # 1 ) at the top of the screen, select "Sample - Send to Excel"), shown below:

The report is essentially the same as the report above, but, with this report, AnyPO will automatically start Excel (or a spreadsheet program of your choice).

The report that will be produced consists of a list of purchase orders issued and includes columns for the PO Number, PO Date, Vendor Name and Address, and the total of each order.

Let's take a look at what it looks like for this sample profile. To do that, first make sure "Sample - Send to Excel" is selected on the profile list. Then click on the button "View or Update Existing Profile," shown below:

The Export Profile dialog box appears (below). Look at Panel # 2 "Type of File." It's here that you want to have "Text - Tab Delimited (.txt)" file selected. When this is selected, you can automatically start up Excel.

The rest of the profile looks the same as the previous sample. It was constructed the same way by selecting fields in the first window, then clicking the big plus + and placing the field in the second large window. Each of the fields in this second window become columns on the spreadsheet

After you've finished looking at the dialog box, click on "Close" and return to the first dialog box. The top of the first dialog box looks like the following:

Once back at the first dialog box, take a look at Panel # 5 . This is where you instruct AnyPO to automatically start Excel. You can design your own profiles and start Excel automatically by placing a checkmark in Panel # 5 . What's important when you use this feature is that make a "Tab Delimited" export file.

You can run the report by clicking on Panel # 7 "Start Export Process" as illustrated below

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.

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):

Start at the Export and Report Generator by selecting FILE >> EXPORT AND THE REPORT GENERATOR. From the list of profiles (Panel # 1 ) at the top of the screen, select "Sample - Products Ordered").

The previous report provided a list of PO's, vendors and totals, but what if you need a list of items ordered including the name of items and the per-item cost? This sample report provides the answer to that question.

What's different in this profile, compared to the one above is the "Export Mode." That's found in the "Profile" dialog box where you determine what data you want to see on your spreadsheet. Let's take a look at what it looks like for this sample.

To do that, first make sure "Sample - Products Ordered" is selected on the profile list. Then click on the button "View or Update Existing Profile," shown below.

The Export Profile dialog box appears (below). Look for "Mode," located in the lower part of the dialog box. To create a report which include data on the items listed on the purchase order, you need to select the mode: "Group PO Data by Product," shown below:

If you take a close look at the "Export Profile" (shown below), you'll see the list of fields that will appear on the spreadsheet. Each field becomes a column on the spreadsheet. So the resulting spreadsheet will have the vendor's name in the first column, followed by quantity, item name, price and total in the next four columns.

The vendor name is repeated for each item ordered on the purchase order. If you also required the purchase order number on the report, you could add it to the list by selecting "01) PO Number" and clicking on the + button to add it to the profile list.

Notice, also, at the top of the Export Profile dialog box that "Text - Tab Delimited (.txt)" has been selected. This enables you to have Excel immediately start-up with the data in place.

After you've finished looking at the dialog box, click on "Close" and return to the first dialog box. The top of the first dialog box looks like the following:

Once back at the first dialog box, you can run this report by clicking on Panel # 7 "Start Export Process."

If haven't already, you may need to indicate the path to Excel or a spreadsheet of your choice. A dialog box will appear and will explain what is needed. 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.)

Once Excel starts, you'll see a list of items ordered along with the vendors from which they were ordered:

This report is done in two steps. First you click on "Start Export Process" to send the data to Excel. Once in Excel you run a macro which re-arranges the rows and columns, performs a series of calculations and creates a nicely formatted report ready to print. It's all done quickly and efficiently within a few seconds.

This report provides the total amount of orders for each code you've entered in PO Code field of the Main Purchase Order Screen. The macro which runs in Excel begins by sorting all of the PO's into their respective codes and provides subtotals of each code. Along with the subtotals, it provides a grand total of all activity, and it neatly formats the report for printing purposes.

The macro which handles all of the calculations and formatting in Excel comes in a package of macros which run all of the reports listed here. The macro package is available free to Level III users. For information on downloading the macros and adding the macros to Excel, see: Macros

To begin, you need export the data to Excel. From AnyPO's Main Purchase Order Screen, select FILE >> EXPORT AND REPORT GENERATOR. From the list of profiles at the top of the screen Panel # 1 , select "Excel Macro Reports."

The next step is to select which PO's you want in the report. That's done in Panel # 4 ("Select PO's to be Exported"). You can select "All" of the PO's. You can select from a list. You can enter a range of dates, etc.

After the selecting the range of PO's that you want, check to make sure there's a checkmark beside: "If exporting to a dBase or Tab delimited file, immediately start up spreadsheet program for editing and/or printing." Also make sure there's a checkmark beside: "First Record: List Field Names."

Click the button: "Start Export Process." Excel will open with the data in place. Next select "A_PO_Code_Report" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

This report provides the total amount of orders for each job or account code that you have entered on the Main Purchase Order Screen. The macro which runs in Excel begins by sorting all of the PO's into their respective codes and provides subtotals of each code. Along with the subtotals, it provides a grand total of all activity, and it neatly formats the report for printing purposes.

To begin, you need export the data to Excel. From AnyPO's Main Purchase Order Screen, select FILE >> EXPORT AND REPORT GENERATOR. From the list of profiles at the top of the screen (Panel # 1 ), select "Excel Macro Reports."

Click the button: "Start Export Process." Excel will open with the data in place. Next select "B_Job_OR_Account_Code" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

This is a handy report to find out how much you've ordered from each of your vendors. It begins by sorting all of the PO's by the Vendor, and provides subtotals for each vendor. Along with the subtotals, it provides a grand total of all activity. It also neatly formats the report for printing purposes.

This requires an Excel macro to create the report. The macros are available free to Level III users. For information on downloading the macros and adding the macros to Excel, see: Macros

The next step is to select which PO's you want in the report. That's done in # 4 ("Select PO's to be Exported"). You can select "All" of the PO's. You can select from a list. You can enter a range of dates, etc.

Click the button: "Start Export Process." Excel will open with the data in place. Next select "C_Vendor_Report" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

The terms field on the Main Purchase Order Screen is multi-purpose. It can be used for the terms of the order. But often times the vendor will already have established terms, and using this field repeats information that has already been standardized between you and the vendor. That makes the field available for your own purposes. Information on the Terms field and how to configure it for your purposes is found: here.

This field is useful since it has 36 characters of space and appears on the printed purchase order. It can be used for an additional PO code or other purposes. A common use of it is a sub-category that fits under the PO code. In fact, a macro is available to provide totals as a sub-category under PO code. There's also another macro available which provides totals of PO codes as categories under the terms code.

Click the button: "Start Export Process." Excel will open with the data in place. Next select "D_Terms_Report" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

As mentioned above, the terms field on the Main Purchase Order Screen can be used for a number of different purposes. One use of it is to utilize it as a overall category, and then use the PO Code as sub-categories that fit under it. That's what this report does. (Note that there is another report available - next - which uses it as a sub-category under the PO code.)

This report sorts all of the PO's by the Terms code. Then it sorts again by the PO Code. It continues by grouping each of the PO's with the same PO Code together and subtotaling each group. It also does a total for overall Terms code (including all the PO Codes that fall under it). Finally, it provides a grand total of all activity and neatly formats the report for printing purposes.

Click the button: "Start Export Process." Excel will open with the data in place. Next select "E_Combination_Report_Terms_AND_POCode" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

Here's a sample of a combination report. First it sorts by "Terms" field and then by PO Code:

As mentioned above, the terms field on the Main Purchase Order Screen can be used for a number of different purposes. One use of it is to utilize it as a subcategory to the PO Code. That's what this report does:

It begins by sorting all of the PO's by the PO Code. Then it sorts again by the Terms field. It continues by grouping each of the PO's with the same Terms code together and subtotaling each group. It also does a total for overall PO Code (including all the Terms codes that fall under it). Finally, it provides a grand total of all activity and neatly formats the report for printing purposes.

Click the button: "Start Export Process." Excel will open with the data in place. Next select "F_Combination_Report_POCode_AND_Terms" from the list of macros and run it. If you're not sure how to run a macro, here's more information:

Here's a sample of a combination report. It's very similar to the previous report, but In this case, it sorts by the PO Code first and then by the "Terms" field. The subtotals are the same (you can check that by comparing CMJ total at 6455 Main Street with the same from the above report), but the display of data is organized differently:

This combination report starts with the PO Code as an over-all category, and use the Job/Account Code as a sub-category which falls under the PO Code.

It begins by sorting all of the PO's by the PO Code. Then it sorts again by the Job or Account field. It continues by grouping each of the PO's with the same Job/Account codes together and subtotaling each group. It also does a total for overall PO Code (including all the Job codes that fall under it). Finally, it provides a grand total of all activity and neatly formats the report for printing purposes.

Click the button: "Start Export Process." Excel will open with the data in place. Next from the list of macros select: "Combination_Report_POCode_AND_JobORAccntCode"

Then run the macro. If you're not sure how to run a macro, here's more information:

We have prepared several Excel macros for use with the Level III program. The macros make the reporting process easier by formatting the report: adding headers, calculating subtotals and totals, sorting by various fields such as job or account codes, placing data into groups, and otherwise sprucing things up for printing. We have made the macros fully editable. That means, if desired, you can also customize the macros for your own special purposes.

Note that because of changes made by Microsoft, AnyPO's macros may not run on all versions of Excel. We are happy to provide the macros but we can make no guarantees on whether they will run or not on your version.

Note: if you are unable to download directly from this help documentation, go directly to our Macro Support Page and use the download link found there. Our macro page is located here: AnyPO Report Macros

The macros come in self-extracting zip file. Download the file. Remember the directory where you save the downloaded file. Double click on the name of the file to unzip the file. You'll need to indicate where you'd like to extract the files.

Next, you'll need to add the macros to Excel. You only to have to do this once. After adding them, they will always be available to you. Information on adding AnyPO's macros is found here: Adding Macros to Excel

The best procedure is to add the Macros to a worksheet in Excel called the Personal.xls. When macros are placed in this worksheet, they are available in all worksheets. In other words, as soon as you create a new spreadsheet in Excel, you'll be able to access AnyPO's Macros. If you don't add the macros to Personal.xls, then you'll need to import the macros each time you create a spreadsheet that requires the use of the macros .

(Note that the macros work in all versions Excel, but you never know what Microsoft will do with their newest and latest version of Excel. )

Excel 2007 (and later) require a different procedure. If you have Excel 2007 or newer, go here. Prior versions of Excel, use this procedure . . .

First, check to see if you have a personal.xls file. Start Excel. Click on the Window menu. Do you see it listed there. If not, click Window >>Unhide. Do you see it there? If you do see it, skip the next step..

2. Navigate to C:\Program Files\Microsoft Office\Office 10\XLStart. If you installed Excel or Microsoft Office in another location, navigate to the \XLStart folder in that location.

3. In the right pane of Windows Explorer, right-click. Select New >> Text Document.

8. When asked if to save your changes, click "Yes." The new Personal workbook will be available the next time you start Excel.

2. In the Unhide dialog box, make sure that "personal.xls" is selected. Click OK.

3. Select Tools >> Macro >> Visual Basic Editor. When the Visual Basic Editor has started, select File >> Import File.

4. Navigate to the folder where you have saved AnyPO's macros. The macros will have a .bas extension, ie Consign.bas, Inventory.bas. Click on the desired macro, and then click "Open."

5. Save the module, quit the Visual Basic Editor. Hide personal.xls by selecting Window >> Hide

1. Follow the special directions for each macro above. AnyPO will start Excel with the data in place.

2. Once Excel has started with the data from AnyPO, select Tools >> Macro >> Macros.

1. Start Excel. Click on the Office Button (the big button on the upper left of the Excel screen). Select OPEN.

2. Browse through the directories and find the XLSTART directory for Excel 2007. Here's how to find it:

Win XP Users: Look for the following if you have Win XP (or an earlier operating system). Note that "YourName" will often be your name, like Cindy, Justin, etc. It's the directory on the computer where your files are kept.

Vista or Win 7 Users: Look for the following if you have Vista or Windows 7. Note that "YourName" will often be your name, like Cindy, Justin, etc. It's the directory on the computer where your files are kept.

3. Once you find the directory, look and see if you have Personal.xlsb. Do you see it listed there? If you see it, skip the next step. If not, continue with the next step.

2. Click on the Office button (the big button on the upper left of the Excel screen) and select SAVE AS and choose "Excel Binary Workbook."

4. Save the empty worksheet as Personal.xlsb. (Excel will automatically add the .xlsb.) You're ready to move on.

1. If you've found or created Personal.xlsb, it will probably be showing on your Excel screen. If not, open it by doing the following:

•	Start Excel. Click on the Office Button (the big button on the upper left of the Excel screen). Select OPEN.

•	Browse through the directories and find the XLSTART directory for Excel 2007. (See "A" on how to find XLSTART above.

2. Now that you have Personal.xlsb showing on the Excel screen, we'll need to open up Visual Basic (built into Excel). To do that, you need to have a "Developer" tab on top of the Excel Screen. If you don't see the "Developer" tab (which is the usual situation), you'll need to add it. To add it, do the following:

•	Click on the Office Button (the big button on the upper left of the Excel screen). Look at the bottom right of the menu that appears. Right next to "Excel Exit" you'll see "Excel Options." Click on the "Excel Options" button.

•	On the left side of the dialog box that appears, make sure "Popular" is selected. Then look at the checkboxes. You'll see "Show Developer Tab in the Ribbon." Place a checkmark here.

•	Exit from the menu. When you exit from the menu, you'll see a new "Developer" tab.

3. Click on the "Developer" tab. On the far left, in the ribbon area, you'll see a "Visual Basic" button. Click on it.

4. The Visual Basic Screen will appear. Select File >> Import File from the menu.

5. Navigate to the folder where you have saved AnyPO's macros. The macros will have a .bas extension, ie Consign.bas, Inventory.bas. Click on the desired macro, and then click "Open."

6. Select File >> Save Personal.xlsb. this will save your macro(s) to the Personal workbook.

8. At this point, we want to hide Personal.xlsb. so that it doesn't appear every time you open Excel. To do so, select the View tab. You'll find an option: "Hide" Select this option and the sheet will be hidden.

1. Follow the special directions for each macro described in each of the reports above. AnyPO will start Excel with the data in place.

3. After selecting the "View" tab, click on the "Macros" button. Then click on the "View Macros" menu.

4. From the "View Macros" list, select the macro you want to run, and then click "Run."