UB> Maintenance> Query by Example

QBE Report Builder Tool

 

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

 

Click here for information on the Jobs Viewer window.

Click here for information on Table/Field help.

 

Step by Step

 

1     View the QBE reports. 

 

  • Open the QBE Maintenance window (UB> Maintenance> Query by example).
  • The QBE Maintenance 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 the Delete icon to delete the highlighted report.
  • Click the Create icon 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, for example a customer name. Fields are then grouped into tables, for example a customer table that contains all of the general customer information like address and phone number. When information is entered into a window in the application, that information is stored in a specific field, in 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.
  • Select Meter in the Primary Table section. The Available Fields section will display the fields in the Meters table. There will be a green star next to the fields in the primary table.
  • Select Meter History in the Secondary Table section. The fields in the Meter History table will be added to the bottom of the Available Fields section. There will be a child field icon next to the fields of the secondary table.
  • If you were to select the Meter History table as the Primary table, you would not be able to select the Meter table in the Secondary Table drop-down menu. These two tables share a common key, (UB Meter Con ID) but you cannot select the tables in that order because the Meter History table is a child table of the Meter table.

 

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 (Meter table), 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/2020.
    • 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 “2020.” 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.
  • In the Primary Table section, select Route No from the first drop-down menu below the Table Name 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 Plus icon in the Primary Table section to apply the argument to the report. The argument will populate in the text field at the bottom of the Primary Table section and will look like this: UB_Meter_Con.Route_No=”65.”
    • 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.
    • The format of the argument in the text field is: table name.field name. The table name will generally vary from the table name selected in the Table Name drop-down menu. The Table Name field generally displays a simplified and intuitive name rather than the actual table name in order to make the fields and tables easier to recognize and easier to use. The text field at the bottom of the Primary Table section will display the actual table, and field name of the selected tables and fields.
    • Click the Test Query icon 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.
    • 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 Plus button in the Secondary Table to add the argument to the report.
      • 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 “2020”, 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 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.

 

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. For example, if you modified the argument to include multiple read periods you can move the Read Period column to the upper section and group the records that display on the report by read period.
  • 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. For example, UB account number will not display in the Column Totals section because a total of the account number field wouldn’t have any meaning (000001-000+0000002-000).
  • 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.

 

8     Print or export the report. 

 

  • Click the Print icon to process the report immediately. You can view the progress of the report on the Job Viewer window (SS> Utilities> Show Scheduled Jobs). Click here for information on the Jobs Viewer window.
    • 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 here for information on the View Reports window.
  • Click the Export icon drop-down menu 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.