This tutorial includes the following topics:

• Report Basics

• Editing a Report

• Report Tips and Tricks
   

Report Tips and Tricks
If you have already complete the Report Basics and Editing Reports tips, or if you are already comfortable with Mailware's report writer, you will appreciate the following list. It contains shortcuts, features and advanced techniques you can use to maximize your reports.

Quick Access to Commonly Used Reports                      back to top
You can have multiple instances of the report writer open simultaneously, and can keep them open all day. This is handy for reports you run repeatedly through the day. To open multiple Report Explorer reports:

1. Left click the Report Explorer icon on the toolbar to open the Explorer. (Note: You will need to add reports to the Report Explorer as described in the help file).
    

2. Double click the report you wish to open then position and resize the window.
    

3. Move the Report Explorer window out of your way or minimize it (do not close it or the report will close too).
    

4. Repeat steps 1-3 for each report you would like to have open.
    

5. As you need to update reports throughout the day left click the report window you need, then on the Preview Report tab left click the icon to refresh the report, and print normally.

Tip: You can edit reports in the C:\Mailw15\Reports folder by opening them from the Company Database Reports tab. The edited report will then be available from that item in the Reports tab. You can also export reports you created/edited in the Report Explorer and overwrite the .srw reports in the C:\Mailw15\Reports folder.

Add Reports to the Reports Button                                back to top

You can add report shortcuts to the Reports buttons on various screens. You can also move, copy or delete the included report shortcuts.

1. Create or Edit a report and save it to your Reports Folder (The Reports Folder is located beneath your Data Directory. The default is C:\Mailw15\Data\Reports).
    

2. Open Windows Explorer and locate your Reports Folder on your hard drive, or network drive.
    
   
    
   Tip: To open Explorer right click the Windows Start button and choose Explore from the menu that pops up. 
    

3. Expand the Reports Folder by clicking the + next to it in the left pane of Windows Explorer.
    
   
    

4. Left click the Reports Folder to select it. In the right pane all your reports will be listed (The pre-built reports are listed by report number and name e.g., R5100 This Invoice). Locate your report in the list.
    

5. Right click your report and drag it to the folder where you wish to add a shortcut (e.g., drag it to the Orders folder if you wish to add it to the Reports button on the Order screen).
    

6. Release the right mouse button to drop the report. From the menu that pops up select "Create Shortcut(s) Here."
    
   
     

7.  A shortcut will be created to your report. If you wish to rename the shortcut you can left click on the folder where you dropped it, then locate the shortcut in the right pane and right click it. Choose "Rename" from the menu that pops up. Type a new name and press the Enter key.
    

8. Repeat steps 5 through 7 to create shortcuts in different folders, or to add shortcuts to other reports you created. You can also locate then delete, copy or move shortcuts for the pre-built reports that came with Mailware.
    

9. Close Mailware then reopen it to refresh the Reports buttons.

Adding a Prompt for Input to Your Reports                                      back to top
You can have your reports prompt you for input when they are Previewed or Printed. For example, an invoice could prompt you for the order number.

In the Define Search screen double click a field (most fields will work with this tip, however, a few will not including Dates which can be set up to prompt as described below). An example would be the OrderNo field in the invoice. The following dialog will open:

In the Filter field on the right select one of the options in the pulldown (e.g., Is Equal To). In the example above we chose Is Greater Than or Equal To.

Enter your prompt text in square brackets [ ]. We entered: [Enter the beginning Order Number]

Click the OK button, then choose File>Save Report from the text menu.

Click the Preview Report tab to test your prompt.

A dialog will open with the text you entered as in our example below.

Enter an order number (We entered an order number of 2) and click OK. Your report will show results for all orders equal to that number or greater. In our example order number 1 would not be included.

You can enter multiple prompts in a single report. To include a date range selection see the next tip: Adding a Date Range Prompt to Your Reports.

Adding a Date Range Prompt to Your Reports                 back to top
You can have your reports request a Date Range when they are Previewed or Printed. This is a handy way to display only the records you wish to view without hard coding dates into your report. In the Define Search tab double click a date field and change its Header to read:

GetDateRange

Then change the Filter to Is Between, and in the 2 fields that open below it  enter a default range (entering the word TODAY in each field for example will cause the report to default to the current date if you do not enter dates when prompted).

The example below is from the Invoice report. We changed the Header which originally read: OrderDt to read: GetDateRange. When run, the report will ask for a date range, then display orders based on the date shown on the Order Entry screen.

Automatically Inserting Your Company Information         back to top
You can add fields from your Company Information settings in Mailware's Preferences by Placing labels in the Layout Page of the report writer then changing their Names to any of the following:

ClientCompany
ClientAddress1
ClientAddress2
ClientPhone1
ClientCity
ClientState
ClientZip

To make formatting easier the following can be used in a single field.

ClientCityState 
ClientCityStateZip

To change label Names select the label then open the Page Designer (click , then the Customize tab) and look at the Name field under the General heading. In the example below the Name field contains the text: ClientCompany.

Note: The Name fields will display your company information the next time you open the report. 

Labels That Prompt for a Start Position                               back to top
Your label reports can prompt you for the location you wish to print an individual label. To create a report that prompts you:

1. Create or edit a label report. Add the CustNo (for customer labels) or OrderNo (for order labels) table from the mailpriv database. To do this:

• Click the Add Table icon .
   

• Select the Mailpriv Database and double-click the CustNo or OrderNo table in the list.
   
  
   

• Close the Add Tables dialog to return to the report. Then link the selected table to the Customer or Orders table by the field that matches (e.g., CustNo to CustNo). To do this click on the field name in one table and drag to the same field name on the other table. A line will connect the tables.
   
  
   

• Next, double-click the line connecting the two tables. The Link Tables dialog will open. Double click the "Table Name" and only matching records from "Table Name" option to select it with the large red check mark. Click OK to close the dialog.
   
  
   

• Save the report using the text menu item File>Save As option. In the dialog that opens specify a file name of SXXXX Name of report (where XXXX is a4 digit number). The SXXXX prefix tells Mailware the report prompts for a starting position.
   

• Create a shortcut to the report in the appropriate Reports button in Mailware. When you select the report a dialog will prompt you for the starting label position number. 

Using Filters to Select Records                                       back to top
Sometime you will want reports to pull only records that match certain criteria. The Define Search tab of the report writer contains the data fields that are included in the report. Any field can have filters applied to it. In the following example the report has been filtered for records with an order number greater than 500.

To set a filter first left click any white cell below the field HEADER you wish to use. Then double click to open the following dialog.

On the right side of the dialog select a filter type. In the example above we used Is Greater Than. Then, in the field(s) below enter your criteria.

Click the OK button (not pictured) to close the dialog. You may enter up to 5 filters for each field (Filter1, Filter2, Filter3, Filter4 and Filter5) depending on the type used (for example Is Equal To provides 5 fields for each filter so you may have as many as 25 criteria entered).

Note: Filters work in conjunction with each other. If you add a filter for OrderNo as above and use a Date Range under Order Date, only orders matching BOTH criteria will be selected. Also, the filters must occupy the same line to work together (e.g., Filter1 for the OrderNo field works in conjunction with Filter1 for the Order Date field and ignores Filter2 through Filter3). Experiment with filters to understand how the process works. You can use various combinations of them to create complex selections. 

Sorting                                                                                back to top
Reports automatically sort results based on field order in the Define Search screen and the SORT option that is set. If SORT is set to None results are sorted in ascending order. In the following example records will be sorted on Key first, then OrderNo.

You can change sort order by selecting the white cell in the SORT row and double clicking to open an edit dialog. Select a different SORT method from the pulldown in the dialog. The first field that contains a SORT method will determine the first sort, the second field will determine the second sort etc. You can also reposition columns in the Define Search screen by left clicking and dragging their field HEADERS (the grey button at the top of the column).

Tweaking Labels                                                                back to top
Labels may not print properly when using the default values in in the report writer. You can adjust these values by editing a Label report and changing the vertical and horizontal gaps, label size etc. This information is available in the Labels tab in the Report Setup dialog (click , then the Labels tab).

You can change the default format by clicking the Choose Standard Label Format button and selecting an Avery label type. You can then change Margins, heights etc in the fields on the dialog. Fields can also be used to create an entirely custom label.

We have found the following values work well for most printers for the standard Avery 5160/8160 and 5163/8163 formats:

Combining fields in Layout                                                 back to top
When creating a Layout for a report you can combine fields in a single label in the Customize tab of the Page Designer (Add a Label to the report then on the Page Designer click Customize tab at the top and the Caption tab at the bottom). Enter fields in the edit area replacing the word Label. 

This tip is especially useful for addresses. Try entering the following into a single field on the Layout page to create a tight address label:

Creating Custom Expressions                                             back to top
Sometimes you need calculated fields on reports that do not exist in Mailware's tables. For example, Amount Due on orders is a calculated field you won't find in the ORDERS table. You can, however, calculate these fields in the report writer's Define Search.

First, double click a blank column or add a new one by clicking the Add Field icon on the toolbar. In the dialog that opens you can build a custom expression by double clicking fields from tables on the left and mathematical expressions on the right. For example, to create the Amount Due field we double clicked TotalAmt, then -, then PaidAmt as in the example below:

Click the Field Properties tab to rename the field (it is otherwise named Custom by default). The new field may then be added to the report like any other.

Fields can be calculated in Layout too. When you Place a field from the Page Designer you can specify a mathematical argument (e.g., @Sum). Use the Calculate Field pulldown on the Page Designer before placing the field.

Tip: Use a custom expression of (999999999 - ORDERS.OrderNo ) to preview the latest record on the first page (e.g., the last invoice entered in the Order screen).

Using SQL Statements                                                       back to top
In Mailware 2005 and later you can use SQL commands in custom expressions.

These commands add features to your reports that were previously unavailable. For example, using an IF statement you can change the text that appears on any report. To try this for yourself use the following steps to change the Mailware invoice. The IF statement looks for the customer type (R=Retail, W=Wholesale) and changes the company name on your invoice accordingly.

1. Open an order in Mailware and press the F10 key to preview the invoice.
2. Click the Define Search tab.
3. On the toolbar click the Insert Blank Field icon.
4. Double click the blank column that appears in the lower half of the screen.
5. In the dialog that opens paste the following into the Custom Expression:
   
   (If (PrintOrder.Customer_LastPayMeth='W','Company 1','Company 2'))
   
   Note: You can rename Company 1 and Company 2 if you wish.
    
6. Click the Field Properties tab and change the Header field to: Title
7. Click OK to save the new field.
8. Click the Layout Page tab at the top of the screen.
9. On the toolbar click the page designer icon.
10. On the Page Designer uncheck the Automatically caption fields box at the bottom.
11. In the list, locate Title and drag it into the top of your report.
12. Delete the old company name field off the invoice (or you can drag it outside the red lines so it does not print, but is available later to move back).
13. Drag the new Title into place. You can enlarge the field by clicking the little black square handles if you have a long company name. You can also adjust the font by using the pulldowns at the top of the screen for the font name and size as well as the Bold, Italics etc. buttons.
14. Click the Preview Report tab to see the results (Note: if prompted regarding sort order answer Yes). If the customer for which you opened the order in step 1 has a type of Wholesale, the first company name will be used. Otherwise, company 2 will appear. Note: You can find the customer type on the Customer screen under the Additional Info tab.
    
    Remember to save your report if you wish to keep these changes.

Using the above IF statement you can change the company address, alternate short order messages, hide or display sale notices etc. The only limitation is the expression cannot exceed 255 characters.
 

Additional commands can be used as well. For example, CASE can display alternate verbiage too. The format for the CASE command is as follows:

CASE expression
WHEN expression THEN value
[WHEN expression THEN value]
[ELSE] value
END

1. To add the CASE command to our report, follow the steps above and paste in the following expression:

(CASE PrintOrder.Orders_PayMethod WHEN 'Credit' THEN 'Credit Card' ELSE 'Other      ' END)

2. Name the field PayType (instead of Title as above) and drag it into the report to replace the field under Pay Method in the first grey line. Now the words "Credit Card" will appear when you preview the invoice. However, we need to show other methods too. Using CASE you can specify different verbiage for each one. Try replacing the expression again with the following:

(CASE PrintOrder.Orders_PayMethod WHEN 'Credit' THEN 'Credit Card' WHEN 'Check' THEN 'Check' WHEN 'PO' THEN 'Purchase Order' WHEN 'Money Ordr' THEN 'Money Order' ELSE 'Other      ' END)

3. Save the report and close the report writer. Open an order and add a payment type of PO or Money Ordr. When you press the F10 key to preview the report you will see the new verbiage in the Payment Method field.

With CASE you can type a shorter expression than would be required by the IF statement to achieve the same results.

Additional SQL commands and more information about using them can be found in the online documentation at:

http://www.elevatesoft.com/dbisam4d5_sql_reference.htm

Layout Sections                                                              back to top
Layout reports are divided into sections. The most basic layout reports contain a Header and Detail section. More complex reports contain:

• Report Header - appears only on the top of the first page

• Report Footer - appears only at the bottom of the last page

• Page Header - appears at the top of every page

• Page Footer - appears at the bottom of every page

• Detail section - appears in the center of each page and contains the unique repeating data

Layout reports may also contain Group Headers and Group Footers. These are special sections that are each associated with a specific field. You can add and edit Group Headers and Footers in the Page Designer under the Arrange tab.

Group Headers are useful for breaking reports into pages and displaying information specific to a field. For example, Mailware's Invoice/Date Range report uses an OrderNo Group Header to create a new page for each order number. The same header displays the unique order number, order date and ship method information for each order. 

Group Footers can also be used for data specific to a field. For example, Mailware's Invoice/Date Range report uses an OrderNo Group Footer to total each order's sale.

MISCELLANEOUS TIPS                                            back to top

Report Settings
You can change various report settings using the Report Settings icon on the toolbar.

Exporting
Any report can be exported in ASCII Delimited, ASCII Fixed, format. Preview the report normally then choose File from the text menu and select Export. In the dialog that opens select a format and enter a file name. Files are saved by default in your C:\Mailw15 folder, however, you can change the location with the ... button on the dialog (e.g., save to a: to place a file on a floppy disk).

Several of Mailware's reports are designed to be exported for use with other programs including: UPS Online, FedEx and several credit card software packages. These reports are located in the Report Explorer under Operation Reports in the System folder.

Print Invoices Button
The Print Invoice option in the Reports button on the Order screen opens the R5100 This Invoice.srw report. This report recognizes the selected order through a link to the OrderNo.db table that is created in your mailpriv folder when Print Invoice is selected.

Previous

If you need assistance please feel free to ask us for help. You can do so quickly and easily by completing our Technical Support form.