Summary

The QBE Builder reporting tool is used to create customized reports in the Code and Contact Management module. Reports are created in the QBE Builder by selecting columns, creating arguments to remove records (Transaction Date < 01/01/2021), selecting how the report will group and sort, and then defining the totals that will display on the report. After the report has been created, it can be printed out or exported into an MS Excel, MS Access or CSV format. Reports that have been created using the QBE Builder tool can be saved and generated at any point.

This document will cover how to create a specific example report that provides an explanation of primary tables, secondary tables, arguments and totals. When creating your own QBE reports, it is helpful to know the general structure of the database and how the information entered into the fields on a window will be stored in the database fields and tables.

The Table/Field help feature (Help> Table/Field) is designed to be used in conjunction with database security (SS> Security> DB Security), but it can be helpful when building QBE Reports to help you determine where the information entered into a field on a window is stored. This tool will not always be helpful because it will display the exact name of the table and field where the information is stored, but QBE Builder uses simplified and more intuitive field and table names.

The report we will create in this example is a report that displays issue steps that have been scheduled before a certain date but have not been completed yet. This report could be used to locate issue steps that should have been completed but have not. Every module with a QBE reporting tool offers a different example report, so if the report you want to create is different than the report created in this example, refer to the QBE examples in other modules for more help. The QBE Report feature works the same in all of the modules, but the information that is available to report on is different from module to module.

Step by Step

1     View the QBE Reports.

• Open the QBE Maintenance window (CM> Maintenance> Query by Example).
• The QBE Maintenance window will display all of the reports that have been created in the CM module. Select a report in the left section of the window and the customized report information will populate in the right section of the window just like many of the other maintenance windows in the application.
• Highlight a report and press DELETE to delete the highlighted report.
• Click the Create icon or press INSERT to create a new QBE report.
• Highlight a report and click the Print icon to print an existing QBE report. The QBE report will be generated as soon as the resources are available on the server. You can view the progress of the print job using the Jobs Viewer window (Jobs Viewer icon   on the main application window).
• Highlight a report and click the Export icon drop-down and select Export Report to export the highlighted report data.
• Highlight a report and click the Export icon drop-down and select Export Definition to export the report definitions of the highlighted report. This exported report definition file can then be imported using the Import icon. This allows organizations to share QBE reports.

2     Select the tables that contain the information you would like to include on the report. 

• The first step in creating a customized report is selecting the information you would like to display on the report. Information in a database is organized into tables and fields. Fields are used to store specific information, such as customer names. Fields are then grouped into tables. For example, a grouping could consist of a customer table that contains all of the general customer information, such as address and phone number. When information is entered into a window in the application, that information is stored in a specific field within a specific table. When creating a QBE report, select the tables that contain the information you would like to include on the report and then select the fields.
• Select a table from the Table Name drop-down menu in the Primary Table section. The Available Fields section will populate with the fields grouped into that table. If the table you select does not contain all of the fields you want to report on you will have to select a secondary table.
• Some tables in the database are linked together because they share a common field called a key. The key connects the data in the two tables together and defines how the data in one table is related to the data in another table. If the table you selected in the Table Name drop-down menu in the Primary Table section is linked to other tables, you can select a secondary table in the Secondary Table section. As you select a secondary table from the Table Name drop-down menu, new fields will be added at the bottom of the Available Fields section. If you need information from two different tables, but those tables are not linked together, you will not be able to create the report.
• From the Table Name drop-down menu in the Primary Table section, select Issue Master.
• From the Table Name drop-down menu in the Secondary Table section, select Issue Steps. This table contains the step information of each issue.

3     Select the fields you would like to include in the report.

• After you have selected the tables you can pull the fields from those tables onto the report. Check the toggles of the fields you would like to include on the report in the Available Fields section. Only fields with a check will display on the report.
• In the Available Fields section, check the Issue Number toggle for the Primary Table. This is the primary column that will display in the report. Check the Step, Description, Scheduled Date, and Completed Date toggles for the Secondary Table. Be sure the Description toggle you check is associated with the Secondary Table. Secondary Table fields will display the Secondary Table icon  .

4     Create a filter argument for the report.

• The fields below the Table Name drop-down menu are used to create an argument that will filter the information that will display on the report.
• While many reports will be filtered by the Primary Table, our example will only be filtered by the Secondary Table. The process for entering arguments is the same for both tables. These arguments are limited to 2048 characters.
• Enter the arguments in the Secondary Table section.
  • When you enter an argument in the Primary Table and the Secondary Table section the arguments will be joined with an AND statement, meaning records must meet the conditions in both section before they will display on the report. This is the part of the argument that will filter to report to only include issues steps that have a “0” in the Completion Time field.
    • Select Completed Time from the first drop-down menu directly below the Table Name field. Selecting a value for this field activates the two fields to the right.
    • The next field contains the operators that will give the argument meaning. Select Equals from this drop-down menu.
    • Enter 0 in the third argument field.
    • Click the Add button to save the argument to the Secondary Table field.
    • Click the Test Query icon to confirm that the query is valid.
  • The AND and OR buttons will be enabled after you add the argument to the report. The AND and OR buttons are used to link the conditions of an argument together so you can build more complicated filtering. The second part of our argument will filter the report to only include issue steps with a value in the Scheduled Date field that is before a certain date. If you are creating a report to display all issue steps that should have been completed the previous day, then we can enter that date into the filter.
    • Select Scheduled Date in the first drop-down menu directly below the Table Name field. Selecting a value for this field activates the two fields to the right.
    • The next field contains the operators that will give the argument meaning. Select Less Than from this drop-down menu.
    • Enter the previous day's date the third argument field.
    • Click the Add button to save the second argument to the Secondary Table field.
    • Click the Test Query icon to confirm that the expanded query is valid.
    • Our query ensures that all steps with a scheduled date before the entered date that have a completed time of zero will display in the report.
  • The brackets are used to define the order in which the AND and OR statements will be applied. Arguments within brackets will be calculated before arguments outside of brackets. For example, you can create an OR statement inside an AND statement using the following format: (statement 1 OR statement 2) AND (statement 3 OR statement 4). The OR statements inside the brackets will be processed first, and then the AND statement will be applied. We will not be using the brackets in our example.

5     Set the number of records that will display on the report.

• The Limit field in the Primary Table section is used to define the maximum number of records that will display on the report. If you set this value to a very large number (99,999,999,999) you run the risk of creating a report that will take a long time for your server to process. If you set this value too low, you risk excluding results that meet the filter argument requirements.
• Enter 100 in the Limit field to limit our incomplete issue steps report to 100 results. If this produces a report with 100 results, you may want to adjust this number up in order to be sure that all results are included.

6     Modify the report layout.

• After the fields have been selected and the arguments have been entered on the report you are ready to set up the report layout. The report layout allows you to define how the report will be grouped and totaled, and also allows you to select which fields will total.
• Click the Modify Report Layout icon to open the QBE Layout window.
• Move the columns that display in the QBE Report Layout section to change the order in which the information will display on the report.
• Move column headings to the section above the column headings to change how the information is grouped on the report.
• The Column Totals section is used to select which columns should be totaled on the report. Check the toggle of the totals you would like to include on the report.
  • Only columns that would provide a meaningful total will be available in the Column Totals section.
  • Our example does not include any columns that would provide a meaningful total.
• Click the Save icon when complete to save the report layout. The QBE report will print in the saved format when the report is generated.

7     Save the report.

• Enter a name for the report in the Report Name field.
• Click the Save icon when complete.

8     Print or export the report.

• Click the Print icon to generate the report. You can view the progress of the report on the Job Viewer window (SS> Utilities> Show Scheduled Jobs).
  • Click the Print icon drop-down menu and select Print Preview to preview the report before printing.
  • Click the Print icon drop-down menu and select Excel to export the report data to an Excel spreadsheet as unformatted data.
  • Click the Print icon drop-down menu and select Excel (Formatted) to export the report data to an Excel spreadsheet that includes much of the Springbrook formatting found on the printed version of the report.
  • Once the report is generated, you can also display the report using the View Reports window (SS> Utilities> View Report).
• Click the drop-down arrow next to the Export icon to select the export format. A window will open allowing you to select the path where the exported file will be created.
• If your report appears to be missing information but your filters seem to be correct, make sure the Limit field in the Primary Table section contains a large enough number to contain all of the records you want to include in the report.