Customizing Pontis Reports

with InfoMaker

Todd Thompson, PESeptember 5-6, 2007

Portland, Maine

Outline

• Review Pontis Database structure

• Infomaker Navigation Basics

• Create a SQL query - simple

• Modify an existing report

• Modify an existing layout

• Practice, Practice, Practice

Infomaker

• Assumptions– IM 9 installed already and licensed– ODBC Profile already set up– IM 9 points to that ODBC Profile already– Sample DB (or NHI Training DB)

– If not, we’ll work on this between lessons, breaks

Pontis Database

• Relational Database

• Multiple Tables

• Each Table made of multiple rows/columns

• 3 Supported DB’s– MS SQL Server 2000– Oracle 9i and 10g– Sybase ASA 8 and 9

Common Tables

• Inventory Tables– Bridge, roadway

• Inspection Tables– Inspevnt, eleminsp

• Planning Tables– projects

Custom Tables

• Inventory Tables– Userbridge, userroadway

• Inspection Tables– Userinspection

• Agency can create, define, modify

• Optional

Example columns

• Bridge Table– Brkey– Bridge_id– Struct_num– Featint– District

• 122 columns of data in bridge table

Relational Database

• Data is stored in tables

• A set of related tables forms a database

• Key fields define relation of tables

Relationships

• Referential integrity (aka Dr. Phil) ensures that relationships between tables remain consistent

• Integrity is enforced with primary keys and foreign keys

Example

• Bridge Table and Roadway Table• A bridge can be in the bridge table once• A bridge can have multiple roadway records

(or can have only one roadway record)• Brkey in bridge must equal brkey in roadway• On_under key defines which roadway record

in table

Another Example

• Inspevnt table

• A bridge can have multiple inspections– Brkey– Inspkey– Other fields to help determine what type of

inspection(s) was performed

Schema on Pontis CD

Schema

QUESTIONS?

Infomaker

• Open InfoMaker

• Navigate within InfoMaker

• Connect to a Pontis database

Intro to InfoMaker

• InfoMaker is a software tool

• Sister product to Powerbuilder– PB was used to create Pontis– Allows seamless integration

• Reports

• Forms

• layouts

• Each agency receives a single license

Intro to InfoMaker

• You can use InfoMaker to:– Query data, ad-hoc reports – View and edit data– Run SQL Update scripts– Customize reports and structure layouts– Add agency-specific fields and tables

InfoMaker Navigation

• The working areas in InfoMaker are called “Painters”

• We’ll be working with two painters – database and library

InfoMaker Navigation

Shortcut to library painter

Shortcut to database painter

Database Painter

• Connect to a database

• View tables and columns

• Launch/run SQL statements

• Many other items – we won’t cover

Connect to database

• Click on Database painter icon

Connect to database

Connect to database

View and edit data

• Open up Tables

• Open up Right Click on Bridge, Select Edit Data, Grid

View and edit data

View and edit data

• View the data• Sort – go to Rows, Sort• Filter – go to Rows, Filter• Save data in various formats – go to File,

Save Rows As– Excel– Text– Comma delimited

View and edit data

• Edit data

• Save Changes

Run SQL statement

• Click on ISQL Session Tab

• Type or paste SQL statements

• Run the SQL

• View the data, like before

Library Painter

• InfoMaker reports are stored in libraries

• A library has the file extension PBL

Library Painter

Library Painter

• Create a new library file

• Copy items from one library to another

• Many other items that we won’t cover today

Create new library file

• Navigate in tree to where you want new library file stored

• Click on New icon

• Navigate to Library Tab

• Click on Library Icon and OK

• Name the library file

Create new library file

Copy item to new library file

• Navigate in tree to Pont_pcr.pbl library

• Find insp003_inspection_schedule report

• Right Click, Copy

• Select New Library and then Open

• This copies the report to the new library we created

Safe Practices/Thoughts

• Copy reports from a production library

• Work in a “working” library

• Rename the report

• Copy to a production library

Practice Time

• Start Infomaker

• Open Database Painter– View Data in Bridge Table and Roadway Table– Export Bridge Table to Excel spreadsheet– Open ISQL tab (we’ll run some SQL next

lesson)

Practice Time

• Open Library Painter

• Create New Library – PUG2007.pbl

• Copy insp006_ report from pont_pcr.pbl library to our new library PUG2007.pbl

Open Infomaker

• Start Infomaker

• Find the Database and Library Painters

• Go to Tools, Toolbars– Click on Show Text, if you want Text next to

icons– Change Font Size, Show Power Tips, etc

Toolbars - customization

Open Database Painter

• View Bridge table data

• Export bridge table data as Excel format

• View Roadway table data

• Navigate to ISQL tab– After Next lesson, we’ll run a SQL statement

Open Library Painter

• Create a new library called PUG2007.pbl

• Copy report insp003_ from pont_pcr.pbl to PUG2007.pbl

Simple SQL Query

• Work on simple SQL statements

What is SQL?

• SQL – Structured Query Language

• Provides standard language for working with data in relational databases – Select– Sort– Count

SQL Statement

• SELECT “some data” FROM “some table(s)” WHERE “some criteria” are met SORT BY “some data”;

Sample SQL Statement

SELECT

bridge.bridge_id

FROM

Bridge

WHERE bridge.owner = ‘1’

ORDER BY bridge.bridge_id ASC;

Sample SQL Statement

• Last query makes a list of all bridge id’s where the owner code = ‘1’

SQL

• Foundation for all reports, queries, forms, structure layouts

• Four hours wouldn’t be enough time to cover everything

FAQ

• Start with existing reports, structure layouts

• Review their SQL statements

• Add or subtract from them

• From within Pontis, right click on a data field to learn it’s table and column name

Info from Pontis

Test Run a SQL

• We want:– Bridge.bridge_id– Bridge.facility– Bridge.featint– Culverts

Test Run a SQL

SELECTbridge.bridge_id,bridge.facility,bridge.featintFROMbridgeWHEREbridge.designmain = '19'ORDER BY bridge.bridge_id ASC;

QUESTIONS?

Reports Outline

Reports

• Created/modified in Infomaker

• Can be made available to be run from Pontis

• Pontis Technical Manual pages 3-36 through 3-38

Modify Existing Report

• Start Infomaker

• Copy standard report from pont_pcr.pbl to “working” pbl

• Set “working” pbl as working pbl

• Rename report using Save As

• Customize report and save changes

• Copy to pontuser.pbl

Reports

• Can create a report from scratch– But need to create SQL– Layout the report– Format it– Clean it up

• Why not take a similar report and just modify it?

Reports – Design View

• Open Library Painter

• Navigate to PUG2007.pbl

• Double click on insp003_ report– Or Right Click Edit

Report PainterDesign View

Properties

Preview View

Report Design

• Add/remove data via SQL

• Revise data in design view

• Preview report look in preview view

Report Exercises

• Copy insp010_ report from pont_pcr.pbl to PUG2007.pbl

• Rename insp010_ to insp210_

• Add bridge.featint to report, make report landscape instead of portraint, place bridge.featint to right of bridge.facility

QUESTIONS?

Layouts

• A layout is a certain type of report

• Working with layouts is very similar to working with reports

• But are some specific design details that are different

• Inspection AND Project layouts

Layout Outline

• Open an existing layout in design view

• Add a field to the layout (or remove)

• Save the changes

• View the updated layout in Pontis

• Optional - Make formatting changes to the layout

Pontis Layouts

Structure Layouts

• The layout must select at least the bridge.brkey fields– But doesn’t have to be displayed

• Report must be named following the convention xl_name– Comment – important

• Pontis Technical Manual – page 3-38 thru 3-40

Structure Layouts

• Column headings must be named correctly– Example – if the column is named facility, then

the heading must be facility_t– Allows sorting and filtering to work in Pontis

Structure Layouts

• Default layouts are located in Pontuser.pbl

• Register updated layouts using the USERLIBRARY option in the Pontis Configuration module

Layout Comments

Project Layouts

• Same except – named as pj_name– Must select projkey from projects table

Layout Exercise

• Use xl_material_and_type

• Copy from pontuser.pbl to PUG2007.pbl

• Add location field (bridge.location)

• Save as xl_pug2007– Comment as Material with location

• Copy back to pontuser.pbl

• Check in Pontis that it exists

QUESTIONS?