The Advanced Reports interface () provides a set of tools to create user defined queries and reports. This tool can be accessed by selecting the Reports tab, then the Advanced icon.

Create

To create an Advanced Report, follow these steps:

1. Select a data table from the "Data" picklist.
2. Enter a Report Title to specify a name for the set of search criteria for future recall.
3. Enter a numerical value in the Decimal Places textbox to set the maximum number of decimal places displayed for from and/or to measures (if applicable) in the output report.
4. Specify a combination of available data elements to create a custom query and report.
   
   • As the user enters information in the Query Builder, more fields will become available. For example, if data is inputted in the Category field, the Item field will become available.
   • The fields listed below can be filled to specify criteria for the report. At least one criteria row (Category, Item, Logical, and Value) must be entered before running the report.
     • Category - Select the Business data types.
     • Item - Select an Item refined by the Category selection.
     • Logical - Provide query operators (equal, not equal, greater than, less than, etc.).
     • Value - Select valid values for the data column or type in where set values do not exist.
     • Add/Remove - Add additional criteria rows to the query (+) or remove criteria from a query (X). The remove option is not presented in the first row since the interface requires at least one search criteria.
     • Join - Group or create conditional queries; used in conjunction with the parentheses controls.
     • Parentheses - Group or create conditional queries; used in conjunction with the join control.
5. Alternatively, enter criteria using the "Enter SQL" option.
   • To do this, click the "Enter SQL" button to change the Query Builder area to support SQL entries.
   • Once filled, the "Verify SQL" button can be used to assess if the entered criteria is valid.
6. Select output columns for the report.
   • There are two methods of specifying the output columns for the report:
     • Identity Columns: This button automatically adds a preconfigured set of columns to the Selected Output Columns list (Route ID, From Measure, and To Measure).
     • Available Output Columns List: The plus signs on this list can be clicked to show the available column types. The user can click one or more of these types, then click the "Add >" button.
   • Once columns are chosen, they will appear in the Selected Output Columns list.
7. Click the "Run Report" button to open the report in a pop-up window.

Other Functions

The steps above outline how to generate an Advanced Report. To learn more about the other tools on this interface-- such as Load Query and Save Query-- navigate to the Advanced Reports article in the overall HPMS Help Guide (click here).