Advanced Reports

The Advanced Reports option is accessible under Reports.

Function

The Advanced menu option allows the user to build and execute ad-hoc queries against all data residing in the STIP Manager database.

Sections

When the Advanced menu option is selected, a pop-up window will open containing a query builder interface to assist the user in constructing a custom search and report. There are eight sections on this interface:

Report Title -- Specifies a name for a set of search criteria for future recall.
Category -- Filters the Item column dropdown by category.
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 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 Columns -- Specifies the data columns to be included in the output report.
Button Controls -- Removes, runs, saves, or stores the created query.

AK STIP Manager Advanced Reports.png

Controls

The Advanced Reports interface hosts these controls:

Control	Section	Used To...
Report Title	Report Title	Display a name that characterizes the query elements; this name will be retained when the Save Query button is selected.
Category	Category	Choose from a list of categories to filter the Item dropdown box.
STIP Items & STIP Funding Buttons	Output Column Data Type Controls	Automatically add a set of data columns to the Selected Columns list at the bottom of the form.
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.
Parenthesis Textboxes	Query Builder	Group or create conditional queries; used in conjunction with the join control.
Category Column Dropdown	Query Builder	Select a category to enable and filter the Item dropdown.
Item Column Dropdown	Query Builder	Select an Item refined by the Category selection.
Logical Column Dropdown	Query Builder	Input a query operator (equals, does not equal, etc.).
Value Column Dropdown	Query Builder	Enter (textbox), select (picklist), or choose (date selection) a valid value that will be searched for.
Reset Criteria Button	Button Controls	Clear all criteria and reset the form.
Run Query Button	Button Controls	Execute the query based on the selected criteria.
Plus and Minus Buttons	Available Columns	Expand and collapse the lists of available columns.
Add (>) Button	Output Field Controls	Add fields from the Available Columns list to the Selected Columns list.
Remove (<) Button	Output Field Controls	Remove a field from the Selected Columns list.
Remove All (<<) Button	Output Field Controls	Remove all fields from the Selected Columns list.
Sort Order Up/Down Arrows	Selected Columns	Indicate that the currently selected item is in ascending (up arrow) or descending (down arrow) sort mode and change the current sort direction by clicking the arrow.
Reorder the Report Column Sequence: Double Up/Down Arrows	Selected Columns	Move a selected item to the top or bottom of the list.
Reorder the Report Column Sequence: Single Up/Down Arrow	Selected Columns	Move a selected item up or down one row in the list.
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 STIP Items and STIP Funding buttons automatically add a set of data columns to the Selected Columns list at the bottom of the form. Each button produces a certain set of columns. These sets are:

STIP Items: Need ID, Project Title, Total Project Estimate
STIP Funding: Fund Amount, Funding Year, Funding Category

Generating a Report

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 Report Title in the textbox.
Select a Category in the picklist (STIP/Budget).
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 (STIP Items or STIP Funding) 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 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.

Output

If the user selects the Run Report button, an Advanced Report will appear in a new window. The previously entered report title is shown at the top of the page, alongside the entire search string that was used. All reports will list the columns that were stored in the Selected Columns table. The user will also have the ability to Print, View in Excel, Map Projects, or Export to Shapefile on the report. These controls are described below.

Control	Used To...
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.
Export to Shapefile Link	Create shapefiles of the mappable project data and allow the user to download and save the zipped-up data.
Map Projects Link	Toggle to the Map Frame and highlight the records in the selected map symbol color.

AK STIP Manager Advanced Report Output.png

Search

Welcome to PMG Support