Summary

The QBE Builder reporting tool is used to create customized reports in the Utility Billing 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. For example, turn on the Table/ Field help and open the Account Master Maintenance window (UB> Maintenance> Account). Open the Account tab and move the mouse over the Billing Cycle field. A bubble will display the following: Table Name: UB_Master, Field Name: Billing Cycle. On the QBE Maintenance window (UB> Maintenance> QBE Builder) the UB_Master table has been renamed to Account Master, so the Billing Cycle field will display in the Account Master primary table.

The example report created in this document will create a list of UB customer account from meter read route “65” with consumption greater than “100” during meter read period “12” and meter read year “2020.” You will also add a consumption report total to the report.

Step by Step

1     View the QBE Reports.

• Open the Query by Example window (UB> Utilities> Query by Example).
• The left section of the Query by Example window will display all of the reports that have been created in the UB 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 click DELETE REPORT to delete the highlighted report.
• Click the Add a new record button   to create a new QBE report. Enter a name for the new QBE report in the Report Name field.
• Highlight a report and click the Print button   to print an existing QBE report. The Notifications icon   will display a new item when your report is ready.
• Highlight a report and click the Export button   drop-down and select Export Report to export the highlighted report data.
• Highlight a report and click the Export button   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. For example, if you select Account Master from the drop-down menu most of the fields on the Account Master Maintenance window will display in the Available fields section. 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. For example, the Account Master table is linked to the Financial table by the UB customer account number. When financial data is generated the UB customer account number is also included so the financial data can be linked to the customer information. 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 UB Meter Con.The Available Fields section will display the fields in the Meters table.
• From the Table Name drop-down in the Secondary Table section, select UB Meter History. The fields in the Meter History table will be added to the bottom of the Available Fields section, separated by a line.

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.
• Select the Route/Sequence, UB Account No, Reading Period, Reading Year and Consumption 1 fields.
  • There are six consumption fields: Consumption 1, consumption 2, consumption 3, etc. Each of the consumption fields represents a consumption usage period. Consumption usage periods are generally used to separate usage into peak and non-peak usage so you can charge different rates. If you are not using consumption usage periods the consumption on a meter will be stored in the Consumption 1 field.

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. For example, if you would only like to include meters with an install date greater than 06/01/2021.
  • These arguments are limited to 2048 characters.
• In this example we will filter the report by route number “65”, meter read period “12” and meter read year “2021.” Since the Route field is in the Primary table and the Read Period and Read Year fields are in the Meter History table, the two portions of the argument will have to be separated. The Route argument will be placed in the Primary Table section and the read period and read year arguments will be placed in the Secondary Table section.
• Click the Add button + to open an argument filter field below the Primary Table Name drop-down menu.
• In the Primary Table section, select Route No from the first drop-down menu below the Table Name field. You can also place the cursor in the field and begin typing to select the field. The two fields next to this field are used to enter the argument. Select Equals, and type 65 in the enabled fields. Click the Save button   to save the argument to the Primary Table field.
  • You can create a route number argument for any route number format. For example, if your meter routes are four digits, enter “0001” to include only meter route 0001 on the report.
  • Click the Test the current query button   to confirm that the query is valid.
• 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.
  • Click the Add button + to open an argument filter field below the Secondary Table Name drop-down menu.
  • Select Reading Period in the drop-down menu below the Table Name drop-down menu in the Secondary Table section. Select Equals and enter “12” in the enabled fields. Click the Save button   to save the argument to the Secondary Table field.
    • You can also create arguments using other operators. For example, if you select “<”, all meter periods greater than the entered period will display on the report. If you select “=>”, all read periods equal to or less than the entered read period will display on the report.
  • 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.
    • Click the AND button to add another condition to the argument already entered. For example, if you would like to filter the report by reading period and reading year, click the AND button between arguments. Both conditions will be applied when the report is generated and only records that match both the read period and read year will be included on the report.
    • Click the OR button if you would like a record that matches either condition to be included on the report. For example, if you would like to include customer accounts on a report if they have bank information entered on their account you should join the two conditions with the OR button.
    • 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.
  • Click the AND button since we want to filter by the read period and the read year. Select Read Year in the drop-down menu below the Table Name field, select Equals, and enter “2021”, and then click the Plus button to add the condition to the argument.

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

• The Results 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.
• Enter 100 in the Limit field to limit our report to 100 results.

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 button to open the Modify Report Layout screen.
• Drag and drop the columns 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.
• Click the Save button when complete to save the report layout. The QBE report will print in the saved format when the report is generated.
• If no changes were made on the Modify Report Layout screen, click SHOW QUERY to return to QBE screen.

7     Print or export the report.

• Once the required report settings have been specified, click the Print button to print your report.
• Click the Export button drop-down and select Export Report to export the report data in MS Excel or .CSV format.
• If your report appears to be missing information but your filters seem to be correct, make sure the Results 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.