Advanced Reports

This is a tool that is located under the Reports dropdown on the Main Menu. This interface is permissions based, so only users specified in the Config entry "SystemAdmins, AdvancedQueryList" will have this option available. Otherwise, it will be hidden.

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:

Query Title -- Specifies a name for a set of search criteria for future recall.
Output Column Data Type Controls -- Specifies a preset group of output columns.
Query Builder -- Specifies a combination of available data elements for creating a custom query and report.
Available Output Columns -- Specifies the available data columns that can be included in reports.
Output Field Controls -- Customizes the report output through the use of movement-based button controls.
Selected Output Options -- Specifies the data columns to be included in the output report.
Button Controls -- Removes, runs, saves, or stores the created query.

SC EGIS Viewer Advanced Reports (2).png

Controls

The Advanced Reports interface hosts these controls:

Control	Section	Used To...
Query Title Box	Query Title	Display a name that characterizes the search elements; this name will be retained when the Save Query button is selected.
Bridges and Projects Buttons	Output Column Data Type Controls	Automatically add a set of data columns to the Selected Output Columns list at the bottom of the form.
Category Column Dropdown	Query Builder	Select the Business data type.
Item Column Dropdown	Query Builder	Select an Item refined by the Category selection.
Logical Column Dropdown	Query Builder	Input query operator (equal, not equal, etc.).
Value Column Dropdown	Query Builder	Select a valid value for the data column or type in where set values do not exist (if applicable).
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.
Plus and Minus Buttons	Available Output Columns	Expand and collapse the lists of available data columns.
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.
Reorder the Report Column Sequence: Double Up/Down Arrows	Selected Output Options	Move a selected item to the top or bottom of the list.
Reorder the Report Column Sequence: Single Up/Down Arrow	Selected Output Options	Move a selected item up or down one row in the list.
Reset Criteria Button	Button Controls	Clear all criteria and reset the form.
Include Additional Items Link	Button Controls	Include attributes that are excluded from the query results by default.
Run Query Button	Button Controls	Execute the query based on the selected criteria.
Load Query Button	Button Controls	Reload a previously saved query from a pop-up list.
Save Query Button	Button Controls	Save the query to the database from a pop-up window.

Preset Columns

The Bridges and Projects buttons automatically add a set of data columns to the Selected Output Columns list at the bottom of the form. Each button produces a certain set of columns. These sets are:

Bridges: Asset ID, Is Culvert, Facility Carried, Crossing Name, Posting Status, Custodian, Location, Operating Tons, Year Built, Structure Length, Deck Rating, Superstructure Rating, Substructure Rating, Culvert Rating, Overall Condition, Flagged Priority, and Alt Operating Rating.
Projects: Project ID, Project Status, Project Name, Project Type, Project Manager, ContractDetailID, Actual Let Date, Award Date, ContractID, Initial Commission Approval Date, Proposed Start Date, Current Cost Estimate, and Obligated Funds.

Output Reports

The results of an executed query are displayed in a pop-up report. 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.
Export to Shapefile Link	Save the results locally in Shapefile format.
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.

Workflow

The Advanced Reports interface allows the user to build a report based on inputted criteria and column types. To generate an Advanced Report, the user must follow these steps:

Add a Query Title in the textbox.
Fill the search criteria table (Category, Item, Logical, and Value).
Add a criteria row if desired by clicking the plus sign on the next available table row.
Use the join and parentheses table fields to group criteria rows or create conditional queries.
Use the output columns buttons (Bridges or Projects) to add a preset grouping of columns to the report if desired.
Alternatively, use the plus button in the Available Columns box to find column types to include in the report; the user can then click the column names and click the "Add" button to place them in the Selected Output Columns box.
Click the "Run Query" button to view the created report in a pop-up window.
Click the "Save Query" button to save the built report; the user will have the ability to edit the Query Name, check/uncheck "Others Can View," and click save.

Users can also load a previous query into the interface to generate a report. To do this, the user can click the "Load Query" button, select the query name, and click "Load Selected Query." This will load the query information into the Advanced Reports interface, allowing the user to run the report. The user can also add/edit the displayed information for the loaded query before running the report.

Help Video

To view the help video for this functionality, visit the Reports video at the timestamp 0:50-6:29.

Search

Welcome to PMG Support