Skip to contentU.S. Department of Transportation/Federal Highway Administration
 
FHWA > TPM > Resources > Evaluation and Economic Investment > HERS-ST Example - Ad-hoc Tables and Reports

HERS-ST Example - Ad-hoc Tables and Reports

Objective: Become familiar with the ad-hoc table and report features in HERS-ST.

Tasks:

  1. creating a new table;
  2. modify existing table;
  3. bring additional data items into table;
  4. create report using table query;
  5. modify existing report;
  6. using BASIC script to modify reports; and
  7. saving reports.

Task 1: Creating a new table

HERS-ST Screen Shot: Application Window - showing how to create a new table. Step 1 and 2 Right click on Table, select new item.

HERS-ST Screen Shot: Application Window - showing how to display a table. Step 3 Double click on New Table or right click and select display

HERS-ST Screen Shot: Application Window - showing the table design query tab.

HERS-ST Screen Shot: Table design query view and pop-up table window and table query wizard. Right click in blank area to get list tables.  Step 5 select wizard button. Step 6 Query Wizard.

HERS-ST Screen Shot: Populated table design query after using table query wizard.

HERS-ST Screen Shot: View Results tab.

Task 2: Modify Existing Table


HERS-ST Screen Shot: General table tab - showing how to modify the table name and description. Step 1 select general tab. Step 2 enter name. Step 3 enter description. Step 4 select design query table

HERS-ST Screen Shot: Showing which variables to select and unselect from the tdSysCondDets table. Step 5 uncheck *.  Step 6 check fsFundingPeriod, fsLabel, fsTotal

HERS-ST Screen Shot: Table query viewer. Step 7 uncheck Output for fslabel.  Step 8a select sort.  Step 9 enter apsr into fslabel criteria.

HERS-ST Screen Shot: Down menu for the sort type column from the table query viewer. Step 8b select ascending


SQL Query
SELECT
  tdSysCondDets.fsFundingPeriod,
tdSysCondDets.Total
FROM
  (((tdIterationItems
INNER JOIN tdSysCondDets ON tdSysCondDets.fwSysCondID = tdIterationItems.fwRefID)
INNER JOIN tdIterations ON tdIterations.fnIterationID = tdIterationItems.fwIterationID)
INNER JOIN tdConfigurations ON tdConfigurations.fnConfigID = tdIterations.fwConfigID)
INNER JOIN tdProjects ON tdProjects.fnProjectID = tdConfigurations.fwProjectID
WHERE
  ((tdIterations.fwConfigID = 41) AND (tdIterations.fnIterationID = 52) AND
(tdIterationItems.fsRefClass = 'bcoss1') AND (tdProjects.fnProjectID = 13) AND
(tdSysCondDets.fsLabel = 'apsr'))
ORDER BY
  tdSysCondDets.fsFundingPeriod

HERS-ST Screen Shot: Table view results tab.

Task 3: Bring Additional Data Items into Table


HERS-ST Screen Shot: Table query viewer - showing how to use the IN function to add multiple data items to the table. Step 1 check output for fsLabel.  Step 2 sort ascending. Step 3 Sort Order 1.  Step 3 enter in(apsr, airi) in Criteria for fsLabel

HERS-ST Screen Shot: Table view.

HERS-ST Screen Shot: Table query viewer - showing how to change the column order.  Hold down the shift key and click on the row select button while moving row to the desired location.

HERS-ST Screen Shot: Table view.

Task 4: Create Report Using Table Query


HERS-ST Screen Shot: Table SQL view - showing how to select and copy the SQL text.

HERS-ST Screen Shot: Reports tree view - showing how to create a new report. Step 3 select reports. HERS-ST Screen Shot: New report pop-up menu. Step 4 select new item.

HERS-ST Screen Shot: Reports tree view - showing how to display a report. Step 5 double click on New Report 1 or right click (5a) HERS-ST Screen Shot: Report pop-up menu. Step 5b select display

HERS-ST Screen Shot: Report query designer -  showing how to paste SQL text in the SQL viewer.  Step 6 right click in editor area.  Step 7 paste.

HERS-ST Screen Shots: Report Designer - showing the report templates button and  Select report template window. Step 8 select report designer tab. Step 9 Select report template button.  Step 10 select report template.

HERS-ST Screen Shot: Report viewer tab.

Task 5: Modify Existing Report


HERS-ST Screen Shot: Report query viewer - showing how to add additional variables to the IN statement.  Step 1 type in (lane_miles, vmt, apsr, airi, del_total, aspd_overall)

HERS-ST Screen Shot: Report designer tab - showing how to modify the report. Step 2 page header - system condition.  Step 3 type in summary of system conditions for caption.  Step 4 detail section.  Step 6 Total

HERS-ST Screen Shot: Report detail properties. Step 5

HERS-ST Screen Shot: txtTotal field properties and output format properties window. Step 7 select ... on Output format.  Step 8 select number. Step 9 select decimal places.  Step 10 ok.

Task 6: Using BASIC Script to Modify Reports


HERS-ST Screen Shot: Report designer tab - showing how to view BASIC script.

HERS-ST Screen Shot: BASIC script editor. Step 2 select object.  Step 3 pick object from list.  Step 4 and 5 edit script.

Task 7: Saving Reports


Windows Explorer Screen Shot: File pop-up menu - showing how to copy a file.
Note: this can be done in either Windows Explorer or My Computer

Windows Explorer Screen Shot: Pasting and renaming the file in the HERS-ST templates directory.

HERS-ST Screen Shot: Report designer tab - showing how to open report templates. HERS-ST Screen Shot: Select report templates - showing new report in list of available reports.
Word files can be viewed with the Word Viewer
Updated: 02/14/2013