Skip to contentU.S. Department of Transportation/Federal Highway Administration
 

HERS-ST Highway Economic Requirements System - State Version: User's Guide

Chapter 10 Creating Reports

This chapter describes the four types of reports products that HERS-ST can create from the analysis data. Charts, GIS Maps, Reports, Tables.

Document Types

The results of the HERS-ST analysis provide a wealth of information. The application provides an interface that enables you to create four different types of documents that allow you to visualize this information:

Document Type Used To
Chart Create a wide variety of charts and graphs
GIS Map Create geo-spatial representations of the road sections
Report Build ad hoc documents
Table Build ad hoc queries to retrieve and present information

The HERS-ST reports present the analysis results to make them clear and understandable. Once the HERS-ST analysis is complete, a great deal of information exists that can be used to create reports that depict the information in a useful way.

The results of the analysis must be saved to the database before they are available for reporting. After a HERS-ST analysis is complete, the information will normally be saved to the database automatically. However, this function can be disabled using the application options described in Chapter 11.

Reports are displayed on the Reports tab of the Workset tree (Figure 10-1).

To create a new report, right-click one of the four report-type branches and select the New Item menu option from the popup menu that appears, or click one of the new report buttons in the tool bar. A new report branch is displayed in the Workset tree.

Figure 10-1. Workset Tree (Reports).

Figure 10-1. Workset Tree (Reports).

Charts

Chart reports are used to create complex charts and graphs to compare information from one or more analysis runs. You can create many types and styles of charts using extensive formatting features and save your chart designs to a template library for reuse.

Click the New Chartnew chart button button in the tool bar to create a new chart. To display the chart viewer, DOUBLE-click its branch in the Workset tree (Figure 10-1) or select the Display item from the popup menu.

General Tab

The General tab of the chart viewer shows the name and brief description of the chart (Figure 10-2). The last user to modify the chart and a corresponding timestamp are also displayed.

Figure 10-2. Chart (General).

Figure 10-2. Chart (General).

Design View Tab

The Design View tab of the chart viewer (Figure 10-3) is the chart design interface. Here you will select the analysis Scenario that the chart is to be based on and specify data and annotations for the chart.

Figure 10-3. Chart (Design View).

Figure 10-3. Chart (Design View).

The data that will be graphed in the chart are specified in this window. The table contains columns of data for the X and Y axis of the chart and is an active spreadsheet that allows for both manual data entry and formula calculations. When you click on a cell, information about the selected cell is displayed and can be edited using the fields and tool bar above the table. Chart data can also be retrieved automatically from the database using the options available through chart popup menus.

Menus. By RIGHT-clicking on the column headers of the data table, you will activate a popup menu (Figure 10-4) that allows you to manipulate the column data and properties. Items in this menu apply only to the column that was clicked.

Figure 10-4. Chart Data Table (Column Menu).

Figure 10-4. Chart Data Table (Column Menu).

  • Clear the data column.
  • Query information from the HERS-ST database.
  • Activate trend lines for the column data.
  • Apply a mathematical formal to the entire column.
  • Show, hide, rename, and remove the data column.

Table Columns. The left most column of the table displays the data for the X-Axis and the other columns display data to create Y-axis plots. These columns can also be designated as OFF, in which case the data is still saved, but not plotted in the chart. The column type is designated from a drop-down list at the bottom of each column.

  • X-Axis Column. The chart table will always have an X-Axis column displayed in its left-most position. The data in the X-Axis column is plotted on the X-Axis of the chart. There are no holes permitted in the X-Axis, so every row of the X-Axis column must be provided with a value.
  • Y-Axis Columns. There are two types of Y-Axis columns available, designated as the Y-Axis (left) and Y2-Axis (right). One or more columns can be added to the table with each column corresponding to a separate plot on the chart. Each plot column is designated as a Y-Axis or Y2-Axis plot. Holes are permitted in the Y and Y2 plots if no data corresponding to the X-Axis are available. To create a hole, skip a row in a plot column, leaving the unwanted cell blank.
  • Other Columns. Not all columns in the table need to be plotted onto the chart. The data in columns that are not directly plotted can be used as source data or intermediate steps for formula calculations. These columns can be hidden in the table for clarity.

Symbol: Letter Eye for Information

When a chart is displayed, the Y-axis is drawn on the left and the Y2-axis on the right. A chart always displays a Y-axis; however, if you do not designate any columns for the Y2-axis, it will not appear on the chart.

Y-Axis Columns. You can add any number of Y-axis columns of data to the design grid and have each column correspond to a separate plot on the chart. To add a plot column, click the Add button for either the Y or Y2 axis. To delete all existing plot columns for either the Y or Y2 axis, click the Clear button. You can also enter a caption for both the Y and Y2 axis.

Manual Edit. Manual editing allows you to add data to the chart, that were not created by HERS-ST, or use data that is not otherwise in the database. Simply type desired values directly into the cells. Use the right-click popup menu to cut, copy, paste, insert, and delete cell values.

You can select multiple cells by dragging the mouse cursor over a range of cells. When you copy and paste a range of cells, the entire range is pasted using the selected cell as the upper left corner of the target area. The copy and paste operation includes data contained in columns that have been hidden. If the target area does not contain enough rows or columns to paste all the data, it will be cropped to fit the available cells. The cut, copy, and paste operation includes data contained in columns that have been hidden.

Symbol: Letter Eye for Information

Sometimes when you copy and paste a cell with a formula, it may produce an invalid cell reference. In this event, or in the case of an invalid mathematical operation, such as a division by zero, the cell will display #VALUE# until the error is corrected.

When you copy and paste a cell with a formula, the cell references in the formula change based on the distance that the formula has moved from its original cell. For example, the cell A1 contains a formula that references B1. When it is pasted into the cell C2, the reference address is changed to reference D2.

Calculating Data Using Formulas. Formulas are equations that perform calculations on values. Formulas may be applied to individual cells as well as entire columns. A cell formula must always start with an equal sign (=) followed by an equation. Formula equations may contain numerical values, mathematical operators and functions, and references to other cells in the table. Cells are referenced by their address in the table, which is always their column letter (A-Z) followed by their row number.

You can enter a cell formula directly by typing in the cell or by using the edit field above the table. If you click a cell in the table while in edit mode, that cell's address is copied into the formula. Click the Enter button or press the Enter key to exit edit mode.

Expression Builder. The expression builder provides assistance with writing formulas by providing common mathematical operators and functions as well as data constants that can be used in the formula. To access the expression builder dialog (Figure 10-5), click the Expression Builder button. There are three predefined data constants that provide information about the analysis that are useful in formulas (Table 10-1).

Symbol: Letter Eye for Information

When working with a scenario with a multiple run analysis, the three formula constants will take on their values from the settings of the first run iteration.

Figure 10-5. Expression Builder.

Figure 10-5. Expression Builder.

Table 10-1. Formula Constants.
FP Length of the Funding Periods used in the HERS-ST analysis
NFP Number of Funding Periods used in the HERS-ST analysis
AP Analysis Period defined as (FP*NFP)

Functions. The Functions dialog window (Figure 10-6) provides a list of available functions that can be used in a formula. You can access the functions window either by clicking the Functions button on the tool bar or from the Expression builder dialog. Select a function from the list and click the OK button to add it to your formula. When you select a function from the list, its usage prototype and a brief description are displayed.

Figure 10-6. Function Prototypes.

Figure 10-6. Function Prototypes.

Column Formulas. Column formulas apply to the entire column. The syntax used by column formulas is identical to that used by cell formulas except that in addition to cell references, column formulas may also use column references. Columns are referenced by their column letter (A-Z) only and are NOT followed by a row number.

Symbol: Letter Eye for Information

Column formulas supersede manually entered data. Cell formulas supersede column formulas.

The column reference is essentially a partial cell address that is completed by the row number of each cell in the column to which it applies. For example, a reference to column B would become B3 when applied to a cell in the third row of the table.

You can create column formulas using the Expression builder dialog. To activate the expression builder, click the Column Formula item in the columns popup menu.

Order of Precedence. Cell formulas supersede column formulas and column formulas supersede manually entered data. Data retrieved from the database is treated by the data table as manually entered data. As such, it will be superseded by both cell and column formulas.

Querying the Database. You can query data directly from the database using one of the following three methods selected from the popup menu. When data is queried from the database, any previous data in the column is replaced with the query results.

Symbol: Letter Eye for Information

Data retrieved from the database is treated by the chart table as manually entered data. As such, this data will be superseded by both cell and column formulas.

  • Predefined Queries. Select one of the predefined queries from the menu.
  • Query Wizard. Use the query wizard to extract data from the database.
  • Ad Hoc Query Builder. Custom build a SQL query to retrieve data.

Predefined Queries. Predefined queries are the simplest method for retrieving data from the database. They are built into the application and retrieve information commonly used for charting. Select a predefined query using the Query/Predefined popup menu. Table 10-2 lists the predefined queries.

Table 10-2. Predefined Queries.
Name Description
Average Annual Funding Retrieves the total initial cost of the design period and divides it by the length of the analysis period. The length of the analysis period is the length of the funding period multiplied by the number of funding periods.
Average PSR Retrieves the average total PSR (APSR) of the last funding period.
Average Speed Retrieves the total overall average speed (ASPD_Overall) of the last funding period.
Congestion Delay Retrieves the total congestion delay (DEL_Total) of the last funding period.
Crash Rate Retrieves the total average crash rate (CRAR) of the last funding period.
Average Annual User Costs Retrieves the total user costs (TUC) and vehicle miles traveled (VMT_All) of the last funding period and multiplies them together. The result is divided by the length of the analysis period.
User Benefits Retrieves the total initial cost and the total average BCR for the analysis period and multiplies them together. The result is divided by the length of the analysis period.
Volume/Capacity Ratio Retrieves percent of deficient VCR Miles (%Miles-VCDL) of the last funding period.
Total Initial Cost Retrieves total initial cost (INICOST) for the analysis period.
Minimum BCR Retrieves the value of the minimum BCR used for the runs of a multiple minimum BCR scenario.

The information retrieved by the predefined queries is extracted from the analysis Runs contained in the Scenario selected in the charts design view. One data value will be returned for each run. For example, a multiple minimum-BCR analysis that has five run iterations will return five values, a value extracted from the results of each run.

Query Wizard. To generate a plot based on System Conditions, Improvement Statistics, Section Conditions, or Highway data from the database, use the Query Wizard. The query wizard is activated by clicking the Query/Wizard item from the popup menu.

Scenario Tab

The Scenario tab (Figure 10-7a) provides you the means to quickly and easily build a SQL query that will retrieve data for any analysis Scenario and funding period. There are four option fields (Table 10-3) that are used to determine what scenario you would like to query data from. Once you have selected your options for each field, click the Next button to continue or click the Apply button and the wizard will assemble the required SQL syntax.

Figure 10-7a. Query Wizard - Scenario.

Figure 10-7a. Query Wizard - Scenario.

Table 10-3. Query Wizard Scenario Fields.
Field Function
Data Source Select the data source from the scenario from which you want to get data. The data sources are the Highway data and the results of a HERS-ST analysis. Each of the data sources are organized into tables which can have data extracted using their Category, Label and Field references.
Scenario This is a list of all available analysis scenarios that you can query. Pick the scenario that you want to retrieve data from.
Run Displays a list of analysis runs for a multiple run analysis. Pick the run that you want to retrieve data from.
Funding Period Displays a list of the funding periods that were analyzed by the scenario. Pick the funding period you want to retrieve data from.

If you retrieve data from a single-run analysis and select ALL for the funding period option, the values returned are from each funding period in the run. The number of records returned will equal the number of funding periods that was specified for the analysis.

If you retrieve data from a multiple-run analysis and select ALL for both the funding period and run fields, the values returned will be from each funding period from each run. The number of records will be equal to the number of runs times the number of funding periods. However, if you select a particular funding period, then the number of records will be equal to just the number of runs.

Category Tab

The Category tab (Figure 10-7b) of the query wizard allows you to select the statistical category that you would like to retrieve data for. This tab is only enabled if you have selected the Improvement Statistics data source from the Scenario tab. Select the statistical category or categories that you would like to retrieve data for.

Figure 10-7b. Query Wizard - Category.

Figure 10-7b. Query Wizard - Category.

Label Tab

The Label tab (Figure 10-7c) of the query wizard allows you to select the table records (rows) that you are interested in. This tab will only be enabled for the Improvement Statistics and System Conditions data sources.

Figure 10-7c. Query Wizard - Label.

Figure 10-7c. Query Wizard - Label.

Fields Tab

The Fields tab (Figure 10-7d) of the query wizard allows you to select the table fields (columns) that you are interested in. This tab is available for all types of data sources.

Symbol: Letter Eye for Information

Although you can select multiple fields with the query wizard, only the data from the first field selected will be returned to the charts data table.

Figure 10-7d. Query Wizard - Fields.

Figure 10-7d. Query Wizard - Fields.

Advanced Tab

The Advanced tab of the query wizard allows you to filter and sort the data retrieved from the database. The Filter window (Figure 10-7e) provides a list of field names that the filter can use. The list of values contains all the unique values of the selected field. The Verify button will validate the syntax of the query to ensure that it is correct before you apply it. If syntax errors are found, a message will be displayed directing you to its probable cause.

Symbol: Letter Eye for Information

The syntax of the filter text represents the WHERE clause of a Structured Query Language (SQL) statement and must comply with all SQL syntax rules.

Figure 10-7e. Query Wizard - Advanced Filter.

Figure 10-7e. Query Wizard - Advanced Filter.

The Sort window (Figure 10-7f) provides a list of field names that can be used to sort the data. To select a field for sorting, simply add it to the Sort By list. You can sort by multiple fields. When using multiple fields, the sorting priority is performed in the order that the fields were chosen.

Figure 10-7f. Query Wizard - Advanced Sort.

Figure 10-7f. Query Wizard - Advanced Sort.

Ad Hoc Query Builder. The ad-hoc query builder (Figure 10-8) is the most flexible and advanced means to retrieve data from the database. It is organized in a three-pane window that will allow you to both graphically create a SQL query and manually type the SQL syntax.

Figure 10-8. Ad Hoc Query Builder.

Figure 10-8. Ad Hoc Query Builder.

Diagram Pane. The top pane of the design window provides a graphical diagram of the database tables and relational links between them. You can RIGHT-click in the blank area to display a list of the tables from the database. From this list you can select the table or tables on which the query is to be based. You can create relational joins between tables by dragging a data field from one table to its related field in another table. A line representing the relationship will appear. To delete an existing relationship, highlight it and press the Delete key.

Fields Pane. The center pane provides a view of the query that will allow you to create features such as sorting, name aliases, and filter criteria. You can add data fields to the query by dragging them from the tables displayed in the top pane to the columns on the middle pane. If you want to use a field for sorting or filtering, but do not want values displayed for the field in the query output, clear the field's Output check box.

Text Pane. As you build a query graphically, the bottom pane displays the actual SQL syntax. You may also manually edit the SQL syntax in this pane. To enter the text edit mode, click the Text button or just start typing in the SQL syntax pane. In this mode, the graphical panes are not displayed. To return to the normal design view, click the Design button. In some cases, a manually entered SQL query may not be able to be displayed in design view. This will be the case if the SQL syntax either has an error or is using syntax that is too complex to be shown graphically. In these cases the query builder will only operate in text mode. To see the results of the query, click the View button.

Symbol: Letter Eye for Information

When using the moving average trend line, you are asked how many samples (minimum of two) should be averaged to produce the trend line.

Trendlines. Trend lines can be produced for any plot on the chart. To activate a trend line for a plot, select the Trendline item from the columns popup menu. The trend line will appear as a separate plot on the chart and can be either linear or a moving average.

Chart Template Library. Charts can be saved to a template library and reused with data sets from other scenarios. When a chart template is loaded, it is adapted to the analysis scenario. Select the scenario you want to use from the drop-down list before loading the template. You activate the template library dialog (Figure 10-9) from the pop-up menu that appears when you RIGHT-click anywhere in the neutral area of the data table.

Figure 10-9. Chart Template Library.

Figure 10-9. Chart Template Library.

The chart template dialog allows you to manage the library for the chart templates which are stored in the directory specified in for your environment. You can use this window to add, select, or remove chart templates from the library.

Chart templates are divided into two categories. When you save a chart template to the library, HERS-ST will usually be able to determine if it is a multiple-run or single-run chart and it will be categorized under the appropriate section of the template library. Any templates whose category is unknown will be displayed under the Other Files section.

  • Single-Run Charts. Single-run charts are those whose design is intended to produce data from a single HERS-ST run. If you select a single-run chart template for a multiple-run analysis, you will be asked to pick the run data to be used to create the chart.
  • Multiple-Run Charts. Multiple run charts are those that are intended to compare results from a HERS-ST multiple-run series such as a multiple minimum BCR analysis or a sensitivity analysis. If you select a multiple-run chart template for a single-run analysis, the resulting plot will have only one point on it.

After you create a chart, you may save its template to the library by selecting the Save menu item from the pop-up menu. When saving a chart, HERS-ST attempts to determine if it is a multiple-run or single-run chart. However, if it is not sure, it will ask.

A number of predefined chart templates come shipped with HERS-ST. Your library may contain others. The predefined templates serve as good examples of the types of charts you can create. You can use them as-is or modify them to your own needs.

Chart View Tab

The Chart View tab (Figure 10-10) displays the chart of the data entered into the design table. This tab also provides a means to print the chart or copy it, as a bitmap, to the Windows clipboard to be pasted into other applications.

There are many properties that you can use to customize the appearance of the chart. You can RIGHT-click on the chart area to display a dialog box that will allow chart customizing. The Chart properties screen displays a series of property tabs. Each property tab is comprised of one or more sub-tabs on which you can specify chart properties.

Figure 10-10. Chart (Chart View).

Figure 10-10. Chart (Chart View).

TRY IT ON YOUR OWN!

Create a New Chart
  1. Right-click the Charts branch in the workset tree.
  2. Click New Item to create a new chart.
  3. When the new chart appears in the workset tree, give it a new name.
  4. Right-click the new chart branch in the workset tree and click Display to display the chart in the workspace.
Use the Pre-Defined Charts
  1. Click the Design View tab of the chart.
  2. Select an analysis scenario on which to base the chart.
  3. Right-click in the body of the design table to access the Chart Templates screen.
  4. Highlight a single-run or multiple-run chart.
  5. Click the OK button on the Chart Templates screen. Data from the scenario populates the chart designer table and configures the chart.
  6. Click the Chart View tab to display the chart.
Use the Query Wizard to Fill a Plot Column
  1. Click the Design View tab of the chart.
  2. Add a Y-Axis data column to the design grid. (click Add button).
  3. Right-click the header of the Y-Axis data column in the design grid.
  4. Select the Query/Wizard menu item.
  5. Select one of the query data sources; Improvement Statistics, System Conditions, Section Conditions, or Highway data.
  6. Select an analysis scenario to retrieve data from.
  7. Select the run to retrieve data from, or use the default ALL option.
  8. Select the funding period to retrieve data from, or use the default ALL option.
  9. On the Category tab, select a statistical category.
  10. On the Label tab, select a record label.
  11. On the Fields tab, select a data field.
  12. Click Apply.
  13. Click the Chart View tab to display the chart.
Customize the Appearance Your Chart
  1. Click the Chart View tab of the chart.
  2. Right-click in the body of the chart to display the charts property window.

GIS Maps

GIS maps are used to create maps of the highway sections that need to be improved. Maps can be used to show the distribution of traffic flow, pavement conditions, or any other information collected or generated by the HPMS data and HERS-ST analysis.

Click the New GIS Map button on the tool bar to create a new map. To display the GIS map viewer, DOUBLE-click its branch in the Workset tree (Figure 10-1) or select the Display item from the popup menu.

General Tab

The General tab of the GIS Map viewer shows the name and brief description of the map (Figure 10-11). The last user to modify the map and a corresponding timestamp are also displayed.

Figure 10-11. GIS Map (General).

Figure 10-11. GIS Map (General).

HERS Data Tab

The HERS Data tab of the GIS Map viewer (Figure 10-12) allows you to set the various settings used to create the map. These settings include selecting the HERS-ST analysis scenario, run, and specific funding period to be used as the source data to be joined to the map. A highway shape file must also be specified before the map can be generated. The Map Viewer tab will not be enabled until all these settings are set.

You can also choose the fields from the Section Conditions results data associated with the selected Scenario, Run, and funding period that you would like to join with the data in the GIS Shape file. Individual fields may be selected by simply clicking the check box in front of each field name. RIGHT-clicking in this area will also give you the option to select or clear all fields.

Figure 10-12. GIS Map (HERS Data Tab).

Figure 10-12. GIS Map (HERS Data Tab).

Selecting a GIS Shape File

To use the HERS-ST map feature, you must specify a GIS Shape file. If you selected a shape file for the highway data used in the scenario, its setting will be appear here. If the highway data does not specify a Shape file, or the Shape file is missing, or you wish to use a different shape file, then you must select it.

Symbol: Letter Eye for Information

See the section on Highway Data in Chapter 6 for more information about selecting a shape file.

GIS Map Tab

The GIS Map tab of the GIS Map viewer (Figure 10-13) displays the highway map. By default, the highway sections that are recommended for improvements during the selected funding period are highlighted. The map will also display a background image of the State's counties, water, and urban areas based on the FIPS code in the Section Conditions data. The background image is created using the States map information located under the directory specified in the environmental options. If this information is unavailable, then no background images will be displayed.

Figure 10-13. GIS Map (GIS Map Tab).

Figure 10-13. GIS Map (GIS Map Tab).

Map Legend. The legend of the map is displayed in a hierarchal tree. You can turn individual items in the map on and off using the check boxes on the right side of the legend pane. By DOUBLE-clicking items in the legend, you will display a dialog window to edit the settings for that item.

Add Layers. To add a new layer to the map, click the Add Layeradd layer button button on the tool bar. Each layer consists of a shape file or other GIS format. Browse to the GIS file that you want to add to the map and select it. The new layer is added to the map and placed as the top layer. DOUBLE-click the map item in the legend to display its settings window, where you can set properties, style, and labeling of the map information on the layer. To delete an existing layer, click the Remove Layerremove layer button button in the tool bar.

Creating Themes. A theme sub-divides the sections of the map into two or more divisions and highlights each with a unique color. Each subdivision is based on a SQL query that is unique, but similar to the other subdivisions to produce a thematic map.

To apply a theme to a map layer, click the Themetheme button button on the toolbar and the Theme Builder dialog (Figure 10-14) will be displayed. Each layer in a map can have a color theme applied to it. All the sections of the layer receive a color from the theme. The colors of the theme supersede the color of the layer.

Symbol: Letter Eye for Information

The filter clause for each division must be expressed as a valid SQL WHERE clause.

Figure 10-14. Theme Builder Dialog.

Figure 10-14. Theme Builder Dialog.

You can create themes for any attribute of the highway section or any data joined to it. Sections are divided based on the value of a field that you select from the drop-down list.

After you select the data field that the theme is based on, select the number of subdivide classes. A filter for each division is calculated and assigned a color and line width automatically and displayed in the table; however you can edit and modify these items to custom tailor the themes presentation. If the layer is comprised of polygons, the options to set the fill color for each division will be displayed.

There are three methods available to create the theme divisions:

  • Unique Values. This method will create a theme where each unique value in the data is assigned a division. This type of theme is useful when you are working with data that is an enumeration or has a limited number of unique values.
  • Range of Values. This method automatically determines breakpoints to create a theme where each division includes about the same number of items.
  • Equal Intervals. This method will automatically determine breakpoints to create a theme where each division spans an equal range of values.

Creating Selections. A map selection is a visual representation of a SQL query of the map data. The selection is highlighted with a user-set color, line thickness, and style.

Symbol: Letter Eye for Information

A selection that highlights the highway sections that HERS-ST has recommended for improvements is automatically created on the map.

To create a selection, click the Selectionselection button button on the toolbar. A new selection will appear in the legend. The selection is created in the currently selected map layer. DOUBLE-click the new selection branch in the legend to display its settings window, where you can set properties and style of the selection.

The toolbar also provides three buttons that provide you with methods to populate the items in the selection. There are three options.

  • Select by Attributes.select by attributes This option will display the query dialog (Figure 10-15) where you can build a SQL query statement to select items based on their attributes. The syntax of the selection criteria represents the WHERE clause of a SQL statement and must comply with all SQL syntax rules. To assist in building the selection criteria, the selection builder provides a list of attribute fields and their respective unique values. Use the Selection Type list to determine how the SQL will be applied to the selection; New, Add or Remove.
  • Select by GIS.select by GIS This option allows you to manually add items to the selection by clicking on them in the GIS map. Only items that are in the currently selected map layer can be added.
  • Select by Shortest Path.select by shortest path This option applies to line-layers only and allows you to automatically select a map corridor between two points. If no unbroken path between the two points can be found then no items will be added to the selection.

Symbol: Letter Eye for Information

When searching for the shortest path, the spatial relationship between the highway sections is used to determine if a path exists. Accurate spatial data must be provided for this option to work properly.

Figure 10-15. Query Dialog.

Figure 10-15. Query Dialog.

Joining Data. Joining is the process by which data from an external database are combined with the Shape file database to allow its information to be used in creating selections and themes. The HERS-ST Section Conditions data fields that you selected on the HERS Data tab are joined into the highways Shape file automatically.

Symbol: Letter Eye for Information

Once data from an external database is joined, you can use the external data to create Themes and Selections.

To join other external data to a map layer, click the Joinjoin button button on the tool bar to activate the Join Builder dialog (Figure 10-16).

Figure 10-16. Join Builder Dialog.

Figure 10-16. Join Builder Dialog.

Symbol: Letter Eye for Information

At least one data field must be selected for joining.

The join builder dialog window allows you to select the database and data table that you want to join to the map layer. You can also specify a SQL query to filter the records of the source data. The data table may have many fields; however you may only be interested in a few of them. You can specify which fields from the data table that you would like to join to the map. All of the data fields in the table are listed on the left. Use the arrow buttons to move fields from the available list to the join list.

Next you must specify the relationship between the records of the external database and those of the maps Shape file. Click the Next button to advance to the next dialog window (Figure 10-17) where you can define the relationship.

Figure 10-17. Join Builder Dialog.

Figure 10-17. Join Builder Dialog.

When joining the data from two databases together, it is necessary to specify how the records between the two databases are related. That is to say, each record in the external data table must correspond to a record in the Shape file database.

Symbol: Letter Eye for Information

At least one relationship between the two databases must be specified.

The relationship window displays a list of fields in both the external data table and the existing Shape file to allow you to create a relationship association between them. To create a joining relationship, select a data field from both the GIS Fields and the Join Table Fields and click the join button. To delete a field association, click the check box in the Remove column.

Dynamic Segmentation. Dynamic segmentation is a GIS procedure that breaks a line into segments that can have their own attributes. Using this procedure, one can create a new map layer if the following conditions are met.

  • A map line layer exists with attributes to identify a group (i.e. road name) and the measured values (i.e., beginning and end milepost).
  • A data table also has fields that can be used to join it to the map layer. The identity field (road name) must be the same for both the map layer and the data table.

The measure field values of the data table do not need to match with their counterparts in the map layer. The dynamic segmentation procedure will select the appropriate segment from the lines based on the measured values and will associate the data table's attributes to it.

For example, you may have data for 5 sections of highway; however all these sections are represented in your corresponding highway Shape file by a single section. You can dynamically re-segment the original highway section into 5 smaller sections, each corresponding to your data.

To re-segment a map, click the Dynamic Segmentationdynamic segmentation button button on the tool bar to activate the Dynamic Segmentation dialog. The first window allows you to select the database and data table that you want to use for your dynamic segmentation. This window is identical to the Join Builder window (Figure 10-16) discussed earlier.

Next you must specify how you want to re-segment the map. Click the Next button to advance to the next dialog window (Figure 10-18) where you can define your dynamic segmentation options.

The dynamic segmentation process creates a new Shape file with the new segmentation. This dialog allows you to specify the name of the new Shape file to be created. You must also specify the geometry type of the new Shape file and indicate whether or not you would like it to be displayed in the GIS map viewer after it is created.

The remaining fields control how the dynamic segmentation is to be done. This process is similar to the method for specifying a relationship for data joining discussed in the previous section. You must specify the common fields between the original highway GIS Shape file and the new data table that describe the Route and the Beginning and Ending mileposts. Once this is done, click the OK button to create the new dynamically segmented Shape file.

Figure 10-18. Dynamic Segmentation Dialog.

Figure 10-18. Dynamic Segmentation Dialog.

Viewing Attributes. To view an item's attributes, click the IdentifyIdentify button button on the toolbar and then click the item on the map. Items on any layer of the GIS map may have many attributes that describe them. These attributes are useful to create map selections and themes. The attributes for the item appear in a table under the legend.

Zoom/Pan and Copy. You can adjust the maps image using the zoomZoom button and panPan button buttons on the toolbar. To copy the map image to the Windows clipboard, adjust the map image as desired and click the copyCopy button button. To copy the legend image to the Windows clipboard, select the Copy Legend item from the legend's right-click popup menu.

Layout. On the Layout tab (Figure 10-19) you can create a printer page-layout using your GIS map, its legend, labels and other graphical tools. The Layout tab displays a vertical toolbar that provides the capability to print the page; edit the page setup; and to place maps, legends, text, shapes, tables, lines, and pictures on a page layout.

Figure 10-19. GIS Map (Page Layout).

Figure 10-19. GIS Map (Page Layout).

To create an object and place it on the printer layout page, click its button in the toolbar and then drop it on the page where desired. You can reposition an object by dragging it with the mouse or editing its properties. DOUBLE-click on an object to display a dialog where you can edit the object's properties.

TRY IT ON YOUR OWN!

Create a New GIS Map
  1. Right-click the GIS branch in the workset tree.
  2. Click New Item to create a new GIS map.
  3. When the new GIS map appears in the workset tree, give it a new name.
  4. Right-click the new GIS Map branch in the workset tree and click Display to display the GIS map in the workspace.
Select a Shape File and Join in the Highway Data
  1. Click the HERS Data tab of the GIS map.
  2. Select an analysis scenario on which to base the map.
  3. Select the run and funding period from which you want to create the map.
  4. If a Shape file is not already loaded, click the Load button and complete the Shape file dialog window.
  5. Select the section conditions data fields to be joined to the GIS map.
Work with the GIS Map
  1. Click the GIS Map tab of the map.
  2. Click the Create a Selection button and double-click the new selection that appears in the legend.
  3. Click the Style tab and change the color, size, and line style of the selection and click OK.
  4. Click the Select by GIS button in the toolbar.
  5. Click on various highway sections to add them to the new selection. Notice how they take on the selections color you specified.
  6. Click on the Theme button and complete the theme wizard. Notice how the theme colors are below the selections.
  7. Use the zoom buttons to change the view of the map.
  8. Click on the check boxes in the legend to turn items on and off.
Design the Layout of Your GIS Map
  1. Click the Layout tab of the map.
  2. Click the Properties button to view the page setup for the map.
  3. Click the Label Text button. With the Label Text button activated, click the map where you want to add text.
  4. Click the Legend button and add a legend to the layout.
  5. Click the map button and add a GIS map to the layout.
  6. Click the Print button to print the page.

Reports

You can use the HERS-ST reports to create document-style ad hoc reports using information from the database, manually written text, and advanced formatting and graphics.

Click the New ReportNew Report button button on the tool bar to create a new report. To display the report view, DOUBLE-click its branch in the Workset tree (Figure 10-1) or select the Display item from the popup menu.

General Tab

The General tab of the Report viewer shows the name and brief description of the report (Figure 10-20). The last user to modify the report and a corresponding timestamp are also displayed.

Figure 10-20. Report (General).

Figure 10-20. Report (General).

Query Designer Tab

The Query Designer tab of the Report viewer (Figure 10-21) provides an ad-hoc query builder that provides the ability to build a SQL query. A report is usually based on a query of data from the database. The fields of the query are available to be used in the report. The ad-hoc query builder is organized in a three-pane window that will allow you to both graphically create a SQL query and manually type the SQL syntax.

  • Diagram Pane. Select one or more tables that the query is based on and define relationships between the tables.
  • Fields Pane. Select the data fields that will be included in the query and specify sorting and filtering criteria.
  • Text Pane. View and edit the actual SQL syntax for the query.

Symbol: Letter Eye for Information

For more information on the query designer, see the Ad Hoc Query Builder paragraph in the Charts section of this chapter.

Figure 10-21. Report (Query Designer).

Figure 10-21. Report (Query Designer).

Symbol: Letter Eye for Information

For more information on the Query Wizard dialog, see Query Wizard paragraph in the Charts section of this chapter.

Query Wizard. The data used to create reports generally resides in one of three types of result tables for a particular HERS-ST analysis: Section Conditions, System Conditions, or Improvement Statistics. The Query Wizard tab will assist you in writing SQL queries to retrieve data from any of these three tables. Click the Wizard button to activate the query wizard.

Report Designer Tab

The Report Designer tab of the Report viewer (Figure 10-22) provides a set of tools for creating or editing report formats. You can create a new report or load an existing template from the report template library. The report design is assembled using three panes.

Figure 10-22. Report (Report Designer).

Figure 10-22. Report (Report Designer).

You select report components from the tool bar at the top of the window and place them on the body of the report as desired. To delete a report component, select it and press the Delete key.

You can use only the fields returned by the query to create the report. To add these fields to a report, drag the field from the left pane and drop it on the report body. The section of the report body that contains a data field will be repeated the number of times that there are records returned from the query. That is, if the query returned five records then any report section with a data field in it will be repeated five times, each time with the field displaying data for each record.

The report body can be divided into many sections. A typical report will have at least three sections made up of a page header, details section, and footer.

If the report contains data fields that are not returned by the report query, these data fields will not function and the report will treat them as labels. This may be the case if you change the report query after adding data fields to its body.

Report Templates. Report templates are available to produce reports that use the data from any of the results tables. Before loading a report template from the library, you must select the HERS-ST analysis Scenario on which you want the report to be based. The list of available scenarios is displayed in a dropdown list at the top of the Report Designer tab. When a template is loaded, its query syntax will be modified to direct it to the data for the selected scenario. The Template Library dialog window (Figure 10-23) is displayed by clicking the Load button on the Report Designer.

Figure 10-23. Report Template Library.

Figure 10-23. Report Template Library.

Report templates are divided into three categories, one for each type of result data. When you save a report template to the library, HERS-ST will usually be able to determine which type of result data it is based on and it will be categorized under the appropriate section of the template library. Any template whose category is unknown will be displayed under the Other Files section.

Report Viewer Tab

The Report Viewer tab of the Report viewer (Figure 10-24) displays the report in a page formatted view that allows you to see how the generated report will appear when printed. In this view, the data fields used in the Designer will be replaced by the actual data from the database. You can use the tool bar to zoom in and navigate through the pages of the report and to print it.

The Report View tab also allows you to save the report to an external file in a variety of file formats including RTF, TXT, PDF, XLS and HTM. Saving the report to an external file allows you to export the report in one of these formats; however, it is not necessary to save the report to an external file for it to be saved as part of the Workset in the HERS-ST application.

Figure 10-24. Report (Report Viewer).

Figure 10-24. Report (Report Viewer).

TRY IT ON YOUR OWN!

Create a New Report
  1. Right-click the Reports branch in the workset tree.
  2. Click New Item to create a new Report.
  3. When the new Report appears in the workset tree, give it a new name.
  4. Right-click the new Report branch in the workset tree and click Display to display the Report in the workspace.
Design a Query for a Report
  1. Click the Query Designer tab.
  2. Right-click the top pane to display a list of database tables.
  3. Highlight a table in the list and click Add to add it to the diagram window.
  4. Check the box next to each field that you want included in the query results.
Use the Pre-defined Report Templates
  1. Click the Report Designer tab.
  2. Select a scenario on which to base the report.
  3. Click the Load button to access the Report Templates screen.
  4. Select a report template from the tree.
  5. Click the OK button to load the report into the report designer.
View the Report
  1. Click the Report Viewer tab.
  2. Use the tool bar buttons to access functionality such as the ability to view the Table of Contents, save the report to an external file, and print the report.

Tables

The HERS-ST Tables are used to build ad hoc queries of the information in the database. The information is presented in a table fashion and can be directly printed or copied.

Click the New TableNew Table button button on the tool bar to create a new table. To display the Table view, DOUBLE-click its branch in the Workset tree (Figure 10-1) or select the Display item from the popup menu.

General Tab

The General tab of the Table viewer shows the name and brief description of the table (Figure 10-25). The last user to modify the table and a corresponding timestamp are also displayed.

Figure 10-25. Table (General).

Figure 10-25. Table (General).

Design Query Tab

The Design Query tab of the Table viewer (Figure 10-26) provides an ad-hoc query builder that provides the ability to build a SQL query. The results of the query will be displayed in the table. The ad-hoc query builder is organized in a three-pane window that will allow you to both graphically create a SQL query and manually type the SQL syntax.

  • Diagram Pane. Select one or more tables that the query is based on and define relationships between the tables.
  • Fields Pane. Select the data fields that will be included in the query and specify sorting and filtering criteria.
  • Text Pane. View and edit the actual SQL syntax for the query.

Figure 10-26. Table (Design Query).

Figure 10-26. Table (Design Query).

Symbol: Letter Eye for Information

For more information on the query designer, see the Ad Hoc Query Builder paragraph in the Charts section of this chapter.

Query Wizard. The data used to create reports generally resides in one of three types of result tables for a particular HERS-ST analysis; Section Conditions, System Conditions, or Improvement Statistics. The Query Wizard tab will assist you in writing SQL queries to retrieve data from any of these three tables. Click the Wizard button to activate the query wizard.

Symbol: Letter Eye for Information

For more information on the Query Wizard dialog, see Query Wizard paragraph in the Charts section of this chapter.

View Results Tab

The View Results tab of the Table viewer (Figure 10-27) displays the results of the query in a table. Data from the results table can be copied to the Windows clipboard to be pasted into other applications. When you select this tab, the Print Preview tab will become enabled.

Figure 10-27. Table (View Results).

Figure 10-27. Table (View Results).

Print Preview Tab

The Print Preview tab of the Table viewer (Figure 10-28) displays a page layout preview of the query results table as it will appear on the printed page. This tab is only enabled after a query has been created and is displayed on the View Results tab. The toolbar at the top of the preview window allows you to switch between Landscape or Portrait layouts and to zoom, scroll, and print the table.

Figure 10-28. Table (Print Preview).

Figure 10-28. Table (Print Preview).

TRY IT ON YOUR OWN!

Create a New Table
  1. Right-click the Tables branch in the workset tree.
  2. Click New Item to create a new Table.
  3. When the new Table appears in the workset tree, give it a new name.
  4. Right-click the new Table branch in the workset tree and click Display to display the Table in the workspace.
Design a Query for a Table
  1. Click the Design Query tab.
  2. Right-click the diagram window to display a list of database tables.
  3. Highlight a table in the list and click Add to add it to the diagram window.
  4. Check the box next to each data field that you want included in the query.
Use the Query Wizard
  1. Click the Design Query tab.
  2. Click the Wizard button to activate the Query Wizard.
  3. Select the Data Source, Scenario, Run, and Funding Period.
  4. Complete the Category, Label, Fields, and Advanced wizard tabs.
  5. Click the Apply button.
PDF files can be viewed with the Acrobat® Reader®
Updated: 02/14/2013