The Advanced menu option allows the user to build and execute ad-hoc queries against all data residing in the STIP Manager database. 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.

Generating the Report

When constructing a search, the user will move from top to bottom, left to right through the options on this screen. To begin, a Report Title can be entered to specify the type of report being created.

Next, the user can choose to add a preconfigured set of columns to the report (chosen columns will also be displayed in the "Selected Columns" box). The three buttons in the "Add to Report" section will trigger this action. The list below outlines the columns that result from each button.

1. STIP Items: STIP ID and Name
2. STIP Funding: Funding Year, Funding Amount, and Funding Category.
3. Project Scroll: Project Scroll STIP ID, Project Scroll Program, Project Scroll Funding Agency, Project Scroll Phase, Project Scroll Funding Type, Project Scroll Funding Category, Project Scroll Funding Year, Project Scroll Fund Amount, Project Scroll Project Name, Project Scroll Project Description, and Project Scroll Municipality.

If the user does not wish to specify the columns using these buttons, the columns can be individually specified later. The next tool the user will come to, the Query Builder, allows them to specify a combination of available data elements for creating a custom query and report. At first, only the parentheses and "Category" fields will be editable. Once a category is selected in the picklist, this logic will apply:

• A "Category" selection makes the "Item" picklist editable.
• An "Item" selection makes the "Logical" picklist editable.
• A "Logical" selection makes the "Value" textbox editable.
• Once a row is filled, a new row can be added through the "+" button.

At least one line must be entered in the Query Builder to run the report. Similarly, at least one column must be specified to run the report. To select columns for the report, the user can utilize the "Available Columns" box, Add/Remove/Remove All buttons, and "Selected Columns" box. The function of these tools is described in the table below.

The order shown in the "Selected Columns" box will be the order they are listed in on the report. The arrows to the right of this box can be used to change that order. The user can select a column row, then use one of these arrows:

The sort order can also be adjusted in the "Selected Columns" box using the arrow in the table beside the row. An up arrow indicates that column will be sorted in ascending order, while a down arrow indicates the column will be sorted in descending order. To change the arrow's direction, the arrow can be clicked.

Once the "Selected Columns" box holds all desired column types with their desired list/sort order, the user can then run the report using the "Run Report" button in the center of the interface.

Other Actions

There are also three other button controls that can be used on this interface:

Reset Criteria

The Reset Criteria button removes all selections from the interface to allow the user to start their query building over. To reset the criteria, the user must simply select this button.

Load Query

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

1. Navigate to the base of the interface.
2. Click the Load Query button.
3. In the resulting pop-up, click a row on the table which shows the desired query name and creator.
4. Click the Load Selected Query button after the row is selected.

Save Query

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

1. Navigate to the base of the interface.
2. Click the Save Query Button.
3. In the resulting pop-up, add a Query Name in the textbox.
4. Click the “Others Can View” checkbox to make the query available to other STIP Manager users; if the user wishes for the query to only be viewable to them, they can leave this checkbox unchecked.
5. Click the Save button.

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, and Map Projects on the report. These controls are described below.