Sample Export Profiles & Creating Your Own Reports

The Advanced Export feature is a tool designed to move data out of AnyOrder and into other programs and applications. One of the primary uses of Advanced Export is to create reports. Reports can be automatically moved to Excel or other spreadsheet programs for formatting and printing. This chapter deal with reports. It starts with general information on how to create a report and then examines some sample profiles that come with the program.

How to Create a Report from Scratch

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

To begin the process, you start at the Main Invoice Screen and select FILE >> ADVANCED EXPORT

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

Report1

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.

Report2

You'll notice near the bottom of the dialog box is the "Mode" (shown on the illustration below) For most reports, you'll use either the "Default" Mode or "Group Invoice Data by Product." If you are creating a report with just general invoice data just as the billing 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 invoice data and product data included on your report, use the "Group Invoice 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.

AdvExport5

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:

Report3

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 "Mailing.txt" for the file name:

Report4

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

Report5

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

Report6

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:\AnyOrder\My_Files directory to open the file: "Mailing.txt."

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 AnyOrder 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 AnyOrder 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, AnyOrder 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.

Sample Profiles

Several sample profiles have been included in AnyOrder which are helpful when learning how to use Advance Export. When you try the samples, you may wish to change the file name and/or the directory where the file is being placed. Once you've done that, click on "Start Export Process." Using a spreadsheet, you can open each of the files after they've been created and see the result of the export process.

Sample Profile: Mailing List

The Sample Mailing List is best place to start when learning how to use Advanced Export. It's quite simple. It creates a simple mailing list of the billing names and addresses in your invoices. (It's easiest, of course, to use AnyOrder's built-in mailing list module, but this profile is simple to construct and it is possible to manipulate it in ways that are not possible with the mailing list module.)

To take a look at the profile, go to the first Advanced Export dialog box (FILE >> ADVANCED EXPORT from the Menu Bar on top of the Main Invoice Screen). Select "Sample: Mailing List" from the list of profiles and then click on "View or Update Existing Profile."

You'll be taken to the second Advanced Export dialog box. This is where you create the profile which drives the export process. It's done by selecting fields in the first window ("Fields Found in AnyOrder"), clicking the big plus (+), which places the fields you want in the window on the right ("Export Profile".)

One of the concepts that this sample profile demonstrates is how you can separate the first name from the last name. It's not problem if you will be exporting the whole Billing Name which combines the first and last names just like on the Main Invoice Screen, but with some applications you may wish to separate name into two fields: first and last.

This profile shows how it down. Scroll down in the first window ("Fields Found in AnyOrder"). If you scroll down far enough you'll see "96) Bill Name: First Name" and "97 Bill Name: Last Name." By selecting these for the profile, AnyOrder will divide the name and export it as two fields. (AnyOrder uses a space to determine where to separate the Bill Name into first name and last name. If you sell to businesses with names such as "Baker and Taylor," then this function would not be useful, but if you sell primarily to individuals, then you may find it helpful.)

The rest of the fields are what you would expect on a mailing list.

Before leaving the profile, notice under "Type of File" that "Text - Comma Delimited" is selected. A Comma Delimited file a very simple database file that all databases and spreadsheets can understand. You'll probably end up using the second file type (Tab Delimited) more often. It's quite useful since it can automatically start Excel or a spreadsheet program without any intermediate steps.

Close out of the profile dialog box and return to the first dialog box. Click on "Start Export Process." AnyOrder will display a list of invoices. Select which invoices you want and export file is created.

You can look at the export file by starting Excel or a spreadsheet program and opening "Mailing.txt" found in C:\AnyOrder\My_files. (You can change the file name or the location. You'll see that on the first Export dialog box. For now, we'll just use the file name and location that comes with the program.)

When Excel starts, it will ask how to divide up the columns. Since we created a "Comma Delimited File" you want to indicate to Excel to divide the columns by commas. Excel will then display the mailing list.

Sample Profile: Email List

The "Sample Email List" is another very simple export profile. It creates file with a list of email addresses.

Starting at the first Export dialog box, select "Sample: Email List" from the list of profile and then click on "View or Update Existing Profile."

You'll notice that like the mailing list (above), the profile also breaks the billing name into the first and last names. Since this is an email list, the profile also includes the email address.

Notice also, like the sample above, "Text - Comma Delimited" is selected.

That's it. It's quite simple. Close the second dialog box and return to the first.

Click on "Start Export Process." AnyOrder will display a list of invoices. Select which invoices you want and export file is created.

You can look at the export file by starting Excel or a spreadsheet program and opening "Email.txt" found in C:\AnyOrder\My_files.

Sample File: Fulfillment Service #1 and #2

"Fulfillment Service #1" and "Fulfillment Service #2" are examples of profiles that might be prepared for a fulfillment service that packages and ships orders for your business. In this case, the two profiles are used together. Fulfillment Service #1 contains basic information such as billing name and address, and Fulfillment Service #2 contains product information.

These files are an example of the so-called two-file system. When the fulfillment house receives the two files, they import the first one which brings in general invoice data first: the billing and shipping names, addresses and other general information. After the first file is imported, the invoices have been created but they lack products.

Then the second file is imported. The second file is related to the first by invoice number. As the second is imported, it looks for the matching invoice number and when found, the products are included on that invoice. (AnyOrder's Import feature is able to import data by using the two-file method.)

(See "Building the Profile: Products" in Export Profiles for more information on related profiles.)

Both of the Fulfillment profiles demonstrate the use of "Replacement Tables." Replacement tables allow you to change the values in AnyOrder to values compatible with the application using the export file.

Fulfillment Service #1 also includes one replacement table, "Carrier Codes," which converts the values found in the Ship Via blank on the Main Invoice Screen to shipping codes used by the fulfillment service.

Fulfillment Service #2 has two replacement tables: "Catalog Numbers" which converts the catalog numbers in AnyOrder to the catalog numbers used by the fulfillment service. The other table is "Back Orders" which exports a "Y" (for Yes) when a backorder is encountered among the products.

Note that the Catalog Number replacement table in the Fulfillment Service #2 sample is based on the sample invoice files that come with the program. If you've deleted the sample invoices, you won't be able to export product data. All other data, however, will be exported. To get product information, you can either substitute the catalog numbers on the replacement with those of some your own products, or you can delete the Catalog Number table.

Creating Your Own Reports

By examining the sample profiles, above, you'll be well on your way to creating your reports. Dozens of different types of reports can be created from data found on invoices.

One common technique that you may use to create your own report is 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. Let's say you want to create a report of all sales to California. We'll use the filter technique to build the profile. Here are the steps you would follow:

1. Start at the Main Invoice Screen. Select INVOICE TOOLS >> FILTER INVOICES. Click the plus (+) beside "Current State or Province."

2. You may end with something like STATE="AK". Change the "AK" to "CA" so that we are filtering for California sales. You want to end up with STATE="CA"

3. Click on "OK" and page through your invoices. All of the invoices that are displayed will have a billing state of CA.

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 >> ADVANCED EXPORT.

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 "CA_Sales.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 sales to customers in California.

Here's another example. In this example, let's say you want to create a report which lists the names of the customers who bought one your products. We'll say the product is The Complete Works of Shakespeare. Here are the steps you would follow:

1. Start at the Main Invoice Screen. Page through the invoices until you find The Complete Works of Shakespeare" listed as the first item in the Item Area.

2. Select INVOICE TOOLS >> FILTER INVOICES. Click the plus (+) beside "Cat # (First on Item List)"

3. Click on "OK" and page through your invoices. All of the invoices that are displayed will have The Complete Works in the Item Area. (Even though the filter was based on The Complete Works listed first in the Item Area, when the filter is applied, all invoices with the Complete Works will be filtered. It won't matter if the Complete Works is listed first, second, third or anywhere else. As long as it is listed, the invoice will be shown.)