Transcript Infomaker Training PUG 2007 - Br Management User Group | BrMUG

Customizing Pontis Reports
with InfoMaker
Todd Thompson, PE
September 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
SELECT
bridge.bridge_id,
bridge.facility,
bridge.featint
FROM
bridge
WHERE
bridge.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 Painter
Design 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 340
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?