Modifying Reports

Home » Get Help! » Tutorials » Modifying Reports

Modifying Reports

We get more calls about Mailware’s report writer than any other feature. So, this tutorial provides instructions on making your own reports and a list of tips and tricks you can use to enhance the reports included in Mailware.

This tutorial includes the following topics:

• Report Basics
• Editing a Report
• Report Tips and Tricks

Report Basics

First, let’s cover how to find the report you want and open it. Mailware’s reports are stored in 2 separate locations:

1. The Reports tab on the Company Database
2. The Reports Button on various screens

Whichever you choose finding reports is simple. Reports are sorted by various types. Reports in the Reports buttons of various screens are associated with the screen (e.g., the Order screen Reports button includes Invoices, Mailing Labels etc.). Reports are generally in one of the following categories:

• Labels
• Management Reports (reports that provide status information)
• Operation Reports (reports you use to complete tasks)
• Export Reports
• Process Orders Reports
• Custom (your own reports)

The Report Center also includes subcategories for the various screens in Mailware:

• Customers
• Orders
• Inventory
• Ads
• Profit
• System

To find the report you want look in its group. For example, to locate an invoice in the Reports Center:

Type “invoice” in the Search field and click Go.

Or,

Click the Filter by field and choose Orders, then Click the Go To field and select Operation Reports. Scroll to the invoices.

Some reports you preview will ask for a date range. Enter dates in the format mm/dd/yy. (Tip: Reports usually default to Today’s date so you can simply press Enter at each prompt to see report results for today’s date. For dates in the current year you can exclude the yy). Your report will appear in a preview window.

You can then print them by clicking the Print icon on the toolbar to open a standard Windows dialog. Click OK to print to the listed printer.

Tip: You can first change printers in the pulldown if you have more than one and for most reports you can also specify a page range (e.g., 1 to 1). In Preferences you can specify a default invoice and label printer.

That covers the basics of finding and printing reports. Don’t worry if it sounds like a lot of steps. We will show you several shortcuts in the Report Tips and Tricks portion the tutorial. But first let’s move on to Editing a Report so you can learn how to change a report to suit your business.

Editing a Report

We showed you how to find, open and print a report in the previous section – Report Basics. Now let’s move on to one of the most popular of Mailware’s features.

Note: For this tip we will use the R5110 Invoice for Date Range report which is located in the Company Database reports tab.

We are going to work with the most commonly modified report – Mailware’s pre-built Invoice/Date Range. First, locate it in the Company Database (see the Report Basics tip for help locating the report).

Click the edit icon next to the report to open the Shazam Report Writer to the Define Search page. Now, follow the steps below to modify the invoice.

1. First, let’s save the report under a different name so your original is left intact. At the top of the screen click the File text menu item, then select Save As from the menu that appears. In the dialog that opens type: Invoice Tutorial then click OK. The report will be saved under the new name.
2. The Define Search screen is used to add fields from Mailware’s tables to the report. We have already added the ORDERS, ITEMS, CUSTOMER and PRODUCTS tables for you (the boxes in the top half of the screen), and, from each table, several fields (the columns at the bottom). For example, you can see the order number field (OrderNo) in the first column.We will return to the Define Search tab later to add a field to the report. For now we have what we need so let’s move on to the Layout Page.Left click the Layout Page tab now.
3. The Layout Page shows us our Invoice as it will appear in a preview. Fields are placed on the page in square brackets (for example, under Bill To: the customer’s company name is shown as [Company]). Labels and other special fields are preceded by an @ symbol or appear exactly as they will print. You can move these fields around, change their appearance and content or delete them entirely. Let’s try a couple of quick exercises:Left click on the Ship To: field. Notice the black square squares that appear around the field? These indicate the field is selected. You can left click and drag any square to resize the field. Or, you can click in the center of a selected field and drag it around the page.Left click on the Bill To: field, then Right click it. A pop up menu appears with options for modifying the field. In this menu Left click the Customize Objects option.
4. The Page Designer opens. Options for changing the look and content of fields are available from this dialog. (Your Page Designer will contain different data then the following sample).The Page Designer is your main editing tool in the Layout Page tab. You can change the field it affects any time by simply clicking the desired field on the Layout while the Page Designer is open. For example,If necessary, move the Page Designer so you can see another field in the report. Then, Left click any currently unselected field.Did you notice the name of the field changed in the pulldown field at the top of the Page Designer’s Customize tab? Try left clicking other fields and watch the name change. This lets you switch quickly to another field and edit it without closing the Page Designer.You can leave the Page Designer open while you work on the Layout or close it with the X in its corner if it is in your way. You can also open it anytime by clicking its icon  on the toolbar.
   

   Changing Field Properties

   You can change the way fields look on your report by editing their properties on the Page Designer. To demonstrate, let’s try changing some fonts. If you closed the Page Designer open it now by clicking its icon . Then Left click the [ShipCompany] field under Ship To:.

   1. In the Page Designer left click the Customize tab at the top of the dialog, then left click the Properties tab at the bottom. Several rows of properties for the [ShipCompany] field are listed in the dialog. Scroll down to the Label section and left click in the white cell next to the Font field.
   2. A button containing an arrow will appear in the field. Left click the arrow button to open a standard Windows font dialog.
   3. In the Font dialog select a different font name from the list (for example, Times New Roman). You can jump quickly to a font by typing its name.
   4. Left click the OK button to accept the new font. The [ShipCompany] field on the Layout page will change to the new font format.
   5. You can continue selecting one field at a time and changing fonts the same way, however, there is a faster method. Left click the [FullName] field below [ShipCompany]. Then, hold Shift and left click the remaining address fields. This multi-selects fields. On the Page Designer change the font as described above and all fields are changed simultaneously.(Note: to use multi-select to change properties always left click a single field first, then Shift-click additional fields. To move fields you can multi-select the same way, or simply drag a box around the fields you wish to move).You can use the above steps to change other properties available on the Page Designer. For example, multi-select fields you wish to be left aligned and change the Left field under Dimensions. Or, change the background color and text color by selecting colors in the Background and Foreground fields under Color.

   Editing Field Contents

   Reports contain 2 types of fields: Data fields are created in the Define Search tab and Placed on the report from the Page Designer. Custom fields are labels, bitmap images, calculated fields and shapes you add from the Page Designer and edit directly on the Layout page.

   Let’s start with Custom fields.

   1. Open the Page Designer with its icon on the toolbar, then left click the Place tab.
   2. The Place tab contains your data fields in the center window and custom fields as icons on the left edge. To add a freeform label to your report left click the L icon . It will depress. Next, left click anywhere in the report to drop the field.
   3. The Label field will remain selected on the report so you can edit it easily. On the Page Designer left click the Customize tab at the top of the dialog, then left click the Caption tab at the bottom.
   4. You should see the name of the label field in the pulldown, and the contents of the field in the edit area. To change the caption left click in the edit area and delete the word Label. You can use the Del or Backspace key on your keyboard to delete the text.In the edit box type: Thank you for your orderNext, left click the label field you dropped. Its contents will change to the message you typed (Note: you can also close the Page Designer or click anywhere in the report to see the changes).
   5. The field is too small to hold the text so let’s enlarge it. Move your cursor over the middle black handle on the right edge of the field. The cursor will change to a double headed arrow. Left click and drag the handle to the right. When you release the left mouse button the text will update. Resize as needed until it fits in the box.
   6. Now, you can left click the middle of the field and drag it to another area of the report. Try placing it above the Ship To: field.You can use any of the icons on the Place tab of the Page Designer to add any custom field.Tip: You can add your company logo using the Image icon . You will need a bitmap (.bmp) of your logo in the appropriate size (about 250 pixels wide) already saved to your Mailware data directory. When you place the image into the report a dialog will open so you can browse to the bitmap.

Now, let’s add a data field.

1. Mailware’s CUSTOMER table includes a Country field in the customer’s billing address. Let’s add it to the Bill To address on the invoice. Before it can be added to the Layout page, however, you need to add it to the Define Search page. To do so, Left click the Define Search tab.
2. On the Define Search tab find the CUSTOMER table in the top half of the screen. Use the scroll bar in the CUSTOMER table to locate the Country field (it’s about the 12th item from the bottom).
3. Double click the Country field to add it to the columns in the lower half of the Define Search screen.
4. Left click the Layout Page tab to return to Layout and open the Page Designer with its icon . Then, left click the Place tab on the Page Designer.
5. In the center of the Page Designer’s Place tab are the fields from the columns in the Define Search screen. Scroll to the bottom to locate the Country field you added.
6. Left click and drag the Country field into your report and drop it below the Bill To: address.
7. Two fields are added. The Country field is a header, and the [Country] field is the actual data. Left click on any blank area of the report to deselect the fields. Then left click the Country header field to select it. Press the Del key on your keyboard to remove the Country field.Tip: Before dragging a field to the report you can uncheck the  option on the Page Designer. This will let you drag and drop just the data field without its header.
8. Left click and drag the [Country] field to position it below the Bill To address. If you wish, you can preview your changes by clicking the Preview Report tab. Be sure to enter a date range that includes orders. Also, please note that the country will appear below the Bill To address only if the Country field in the customer’s account was entered.You can add any fields you need from the tables in a report using this method. You can also add new tables using the Add Tables icon on the toolbar in the Define Search screen. However, added tables must be linked by common field names (You can see the links in the invoice report now. They are connected by lines, for example, CustNo in the ORDERS table is connected to CustNo in the CUSTOMER table). Adding tables is a bit tricky since they affect the data that is displayed. In most cases you can simply work with the tables provided in existing reports. To learn more about adding tables refer to the Creating a Report from Scratch section of your Mailware Manual or .pdf file on your Mailware CD.You can edit any report in Mailware by simply opening it normally then using the steps above. In most cases it is a good idea to immediately save the newly opened report under a different name before making changes. When you finish your changes to a report in the Report Explorer you will be returned to the Explorer where you can locate your new report and add a memo to the lower half of the screen. It’s a good way to document the changes you made.Now that you know how to edit reports it’s time to move on to the Report Tips and Tricks section.

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
• Add Reports to the Reports Button
• Adding a Prompt for Input to Your Reports
• Adding a Date Range Prompt to Your Reports
• Labels That Prompt for a Start Position
• Automatically Inserting Your Company Information
• Using Filters to Select Records
• Sorting
• Tweaking Labels
• Combining fields in Layout
• Creating Custom Expressions
• Using SQL Statements
• Layout Sections
• Miscellaneous Tips

Quick Access to Commonly Used Reports

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

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

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

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

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

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.
2. Locate the OrderNo field (or CustNo field in a customer label report) in the columns in the lower half of the Define Search screen and double click it.
3. In the dialog that opens rename OrderNo to CurrentOrderNo. This will cause the report to retrieve the information only for the currently opened order (or likewise for the currently opened customer when you change CustNo to CurrentCustNo).
4. 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.
5. Create a shortcut (see Add Reports to the Report Button above) 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

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

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

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

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:

Enter Field as:	Resulting Address:
[Company]	Core Technologies
[FirstName] [LastName]
[Address1], [Address2]	1320 Pearl Street, #240
[City], [State] [Zip]	Boulder, CO 80302

Creating Custom Expressions

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

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:
6. (If (PrintOrder.Customer_LastPayMeth=’W',’Company 1′,’Company 2′))
7. Note: You can rename Company 1 and Company 2 if you wish.
8. Click the Field Properties tab and change the Header field to: Title
9. Click OK to save the new field.
10. Click the Layout Page tab at the top of the screen.
11. On the toolbar click the page designer icon.
12. On the Page Designer uncheck the Automatically caption fields box at the bottom.
13. In the list, locate Title and drag it into the top of your report.
14. 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).
15. 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.
16. 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.
4. With CASE you can type a shorter expression than would be required by the IF statement to achieve the same results.

Layout Sections

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

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.