Advanced Reports

This is a tool that is located on the Reports section of the Reports tab.

Function

The Advanced Reports interface () provides a set of tools to create user defined queries and reports.

Sections

The Advanced Reports interface is divided into 7 sections:

Report Title -- Specifies a name for a set of search criteria for future recall.
Data Dropdown -- Specifies which HPMS Data table the interface will use when running the report.
Precision and Identity Column Controls -- Applies general settings to the output report.
Query Builder -- Specifies a combination of available data elements for creating a custom query and report.
Available Output Columns -- Specifies the data items to include in reports.
Output Field Controls -- Customizes the report output through the combined use of the add, remove, and remove all buttons with the Available Output Columns/Selected Output Columns lists.
Selected Output Columns -- Specifies the data columns to be included in the output report.

Controls

This interface hosts the Data Table dropdown control, alongside these controls:

Control	Section	Used To...
Report Title Textbox	Report Title	Display a name that characterizes the selected search elements.
Decimal Places Textbox	Precision and Identity Column Controls	Set the maximum number of decimal places displayed for numeric values in the output report. Data with higher precision will be rounded to the specified decimal place.
Identity Columns Button	Precision and Identity Column Controls	Add a preconfigured set of columns to the Selected Output Columns list at the bottom of the form. County, Route ID, From Measure, and To Measure are added when the Identity Columns button is pressed.
Category Column Dropdown	Query Builder	Select the Business data types.
Item Column Dropdown	Query Builder	Select an Item refined by the Category selection.
Logical Column Dropdown	Query Builder	Provide query operators (equal, not equal, greater than, less than, etc.).
Value Column Dropdown	Query Builder	Select valid values for the data column or type in where set values do not exist.
Add/Remove Item (+ and X) Buttons	Query Builder	Add additional criteria 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 Dropdown	Query Builder	Group or create conditional queries; used in conjunction with the parentheses controls.
Parentheses Textboxes	Query Builder	Group or create conditional queries; used in conjunction with the join control.
Reset Criteria Button	Query Builder	Clear all criteria and reset the form.
Enter SQL Button	Query Builder	Switch to a SQL entry window. In this mode, the Enter SQL button will change to “Enter Criteria” which can be used to switch back to the traditional query builder mode.
Verify SQL Button	Query Builder	Validate the entered SQL statement when in SQL entry mode.
Run Report Button	Query Builder	Execute the query based on the selected criteria. At least one output column must be selected, using the Identity Column control or Output Field Controls, to enable the Run Report button.
Plus and Minus Buttons	Available Output Columns	Expand and Collapse data items.
Add Button	Output Field Controls	Add fields from the Available Output Columns list to the Selected Output Columns list.
Remove Button	Output Field Controls	Remove a field from the Selected Output Columns list.
Remove All Button	Output Field Controls	Remove all fields from the Selected Output Columns list.
Sort Order Up/Down Arrows	Selected Output Columns	Indicates that the currently selected item is in ascending (up arrow) or descending (down arrow) sort mode. This arrow is located in the actual table, and its current direction can be changed by clicking the arrow.
Reorder the Report Column Sequence: Double Up/Down Arrows	Selected Output Columns	Move a selected item to the top/bottom of the list.
Reorder the Report Column Sequence: Single Up/Down Arrow	Selected Output Columns	Move a selected item up/down one row in the list.
Save Query Button	Selected Output Columns	Open a pop-up form used to save the query to the database.
Load Query Button	Selected Output Columns	Reload previously saved queries from a pickable, pop-up list.

Output Reports

The results of an executed query are displayed in a separate pop-up window. The Report Name, Search Criteria, Run Date, and Number of Records will be displayed at the top of the report. There are also some controls offered at the top of the output report:

Control	Used To...
Map Symbol Color Dropdown	Select the color for displaying the report results on a map.
Map Link	Toggle to the Map Frame and highlight the records in the selected map symbol color.
Print Link	Open the Windows Print Dialog to customize and print the results.
View in Excel Link	Open the file in Excel or save the results locally in Excel format.

Load Query

In order to load a previous query onto the interface for editing/viewing purposes, the user can follow these steps:

Navigate to the Selected Output Columns section in the lower right corner.
Click the Load Query Button.
In the resulting pop-up, click a row on the table which shows the desired query name and creator.
Click the Load Selected Query button after the row is selected in yellow.

Save Query

To save the current query to the database, the user can follow these steps:

Navigate to the Selected Output Columns section in the lower right corner.
Click the Save Query Button.
In the resulting pop-up, add a Query Name in the textbox.
Click the “Others Can View” checkbox to make the query available to other HPMS Manager users; if the user wishes for the query to only be viewable to them, they can leave this checkbox unchecked.
Click the Save button.

Search

Welcome to PMG Support