13-3 Report Writer

13-3 Report Writer provides tools that allow you to create custom reports to access information stored in the Sage 100 Contractor databases. With 13-3 Report Writer, you can modify existing reports or create entirely new reports for your company’s needs.

Before creating a report, you need to understand how 13-3 Report Writer and 13-5 Form/Report Page Design work together. In 13-5 Form/Report Page Design, you create the page layout for a single page form or a multi-page form that includes text, fields, and calculated fields. Most form designs also have a detail box, which indicates to Sage 100 Contractor where the details of the report will be placed.

In 13-3 Report Writer, you create the body of the report that appears in the detail box of the form design. In the design of the report details, you can include text, fields, and calculated fields. In addition, you can control when the report prints on a new page or an entirely new form.

13-3 Report Writer allows you to associate a specific form design with a report. When you select the report for printing, Sage 100 Contractor automatically selects the associated form design. In addition, you can determine the location of the report in Sage 100 Contractor. You can assign custom reports to 13-4 Report Printing, or to another window that contains reports similar to the custom report.

You can create new reports from scratch, or you can use existing reports as the basis for new reports. When you display a Sage 100 Contractor report, Sage 100 Contractor copies the report and displays the copy in the 13-3 Report Writer window. You can then edit, rename, and save the new report.

Creating new reports

Note that sometimes it is quicker to create a new report by editing an existing report if you only have to make a few changes or additions.

To create a new report:

Modifying existing reports to create new reports

To locate the report you want to open, you need to know the name of the report. The file name is a combination of:

• The menu option where the report is located
• The number of the report.

These two items are combined to form an eight-character file name, for example, 04010221.rpf, is the report that corresponds to 4-1-2 Payable Invoice, report 21.

To modify an existing report to create a new report

1

Locate the report, and remember its name.

2

Open 13-3 Report Writer.

3

From the New Report Launch wizard, select Browse for an existing report..., and click the [Display System Reports] button.

Note: Alternatively, you can open the report that you want to modify by locating the report on a Report Printing window, selecting it, and then clicking Modify Report button.

4

From the list, locate and double-click the report you want to open, for example, 04010221.rpf.

5

Type a new report title in the Report Title text field.

6

Select a report form for the Report Form text field.

7

To modify the grouping and sorting of field, select Edit > Grouping/Sorting....

8

On the Define Grouping and Sorting window, choose a table, double-click items in the Fields list to move them to the bottom panel.

In the bottom panel, you can do the following:

a

Remove groups by right-clicking a group field

b

Reorder groups by dragging them into the desired order

c

Change sorting by clicking on columns in the main row of the panel

9

Click OK.

10

To modify the selection criteria, select Edit > Selection Criteria... .

a

On the Define the Selection Criteria window, choose a table, double-click items in the Fields list to move them to the bottom panel.

b

In the bottom panel, you can choose to enter default criteria, or you can leave the criteria blank.

c

Click OK.

11

From the File menu, select File > Save.

12

To the message box confirming the modification, click OK.

13

Change the last two digits of the report name to a number between 1 and 20.

14

Select Save.

Using the New Report Launch window

With the New Report Launch window, you can do the following:

• Create a new report using the wizard...
• Browse for an existing report...
• Start with a blank report
• Open a recent report (last 5 saved reports are listed)

Creating a new report using the wizard

1

Accept the default to Create a new report using the wizard... and click OK.

This launches the Report Wizard.

2

Enter a report title to be displayed at the top of the report and click Next.

Note: You can include a subtitle by adding a '~' and then the subtitle text.

3

Choose the report form on which to print the report and click Next.

Note: You can use wildcards in the report form name, such as "*.Report" to be able to select .Report file when running the report.

4

Select the table that holds the primary data for the report by navigating to the menu where the records can be found.

Tip: You can also list tables by name to view a simple alphabetical list of all data tables. Check List Tables by Name.

5

Click on the primary table and then click Next.

6

Select fields in the primary table to add to the report.

a

Double click on fields from the Fields list on the top right. These fields can be sorted by name if you select the option to List Fields by Name. You can select fields from related tables by changing the selected table in the list on the top left.

b

In the bottom panel, you can do the following actions:

i

Add grouping to the report by dragging columns to the group area in the dark gray band. A maximum of three fields can be used for grouping.

ii

Add sorting by clicking on a column. The number that appears next to the column name is the next consecutive number after the last group. If no groups are defined, the sort column displays a 1. You can have multiple sorts in the main column row by holding the shift key when clicking additional columns.

iii

Reorder columns by dragging them to a different location.

iv

Remove columns from the report by right-clicking them.

7

Click Next.

8

Select the fields for report selection criteria.

a

Double click on fields from the Fields list.

b

In the bottom panel, you can do the following actions:

i

Select the comparison option to set as the default.

Note: For example, if you would like the default to be between or equal, you can set that as the default on the report printing window.

ii

Lock criteria values to fix those values permanently in the report.

Note: For example, in an invoice report that should exclude voided invoices , lock the invoice status criteria as 'Less than 5-Void.'

9

Click Finish.

Browse for an existing report...

1

Select Browse for an existing report... and click OK.

2

click the [Display System Reports] button and select the report, then click Open.

Start with a blank report

1

Select Start with a blank report and click OK.

This selection allows you to create a new report without using the Report Wizard.

Open a recent report

The last 5 saved reports are listed and can be selected.

Modifying reports and form designs

When the Modify Report button appears in the Report Printing window, you can edit the report design and the form design. If the Modify Report button does not appear, you cannot edit the report design, but you can still edit the form design. To open 13-5 Form/Report Page Design from a Report Printing window, from the Edit menu, click Form Design.

If the form name does not display in the Report Form field in the Report Printing window, click the Modify Report button. This opens the custom report in 13-3 Report Writer. The custom form name is then displayed in the Form field. Copy the form design to the \Report Forms folder.

Important! Some of the over 1100 system reports are “locked,” and you cannot modify the data portion of the report. In certain cases, some reports allow modification of the design.

Assigning reports to form designs

When you decide to print a report, Sage 100 Contractor selects the form design you assigned to the report design. You can assign a report design to a specific form or to a group of forms.

Form design uses file name extensions to group similar types of form designs together. To assign a report design to a form design, you need to determine which form design a specific report uses. When you select a report for preview or printing in the 13-4 Report Printing window, Sage 100 Contractor displays the file name and file name extension of the form design below the Report list.

For example, you create a new report for income statements titled My Income Statement and want the new report to use the same form design as the original report for income statements. Having created the report design, assign it a form design to use when Sage 100 Contractor generates the report. In the Form text box, you type report.rpt and save the new report. Later, when you print the My Income Statement report, Sage 100 Contractor automatically selects the Report.rpt form design.

Suppose, instead of limiting the report to the Report.rpt form design, you want to be able to select any form design using the .rpt file name extension when printing the My Income Statement report. In this situation, use a wildcard in place of the report name and indicate the file name extension.

In the Form text box, type *.rpt to indicate you want all files using the .rpt file name extension, and save the report. When you select the My Income Statement report for printing, you will be able to select from all the form designs using the .rpt file name extension.

When you save a report design, Sage 100 Contractor saves it as a file. The file name determines where you will find the report in Sage 100 Contractor. Where form designs use different file name extensions to organize the forms into meaningful groups, all report designs use the .rpf file name extension.

You can assign a custom report to:

• 13-4 Report Printing. Assign the file any name and save it to the \MB7\Custom Reports folder. You can save an unlimited number of reports to this location.
• A specific location where similar reports are found. For example, if you create a new income statement, you might want to make the report available with the other income statements. Each window location can contain up to 20 custom reports. To access a report in a specific window, save the file in the \Program Files (or Program Files (x86))\Sage\Sage 100 Contractor\System Reports folder.

The filename consists of eight numbers. The first six numbers specify the window location, and the last two numbers are user-defined and must fall between 01 and 20.

The following examples explain how to convert the window locations into the correct naming format:

• You created a new departmental income statement and want to access the report from 2-8-1 Departmental Income Statement. Convert each menu and submenu number used to arrive at the Departmental Income Statement into a 2-digit number, so the file name becomes 020801. As this is your first custom report in this location, add 01 to the end. The full name of the file becomes 02080101.rpf.
• Some windows in Sage 100 Contractor are not located under two submenus. To indicate the correct report path, add two zeros in the report name to represent the last submenu. For example, you create a new income statement and want to access it from 2-3 Income Statement. The numbers representing the location become 0203. Because you need six numbers to represent the menu location, add two zeros. The file name then becomes 020300. As this is your first custom report in this location, add 01 to the end. The full name of the file becomes 02030001.rpf.