1 / 74

Customizing Pontis Reports with InfoMaker

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.

palani
Download Presentation

Customizing Pontis Reports with InfoMaker

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Customizing Pontis Reports with InfoMaker Todd Thompson, PE September 5-6, 2007 Portland, Maine

  2. Outline • Review Pontis Database structure • Infomaker Navigation Basics • Create a SQL query - simple • Modify an existing report • Modify an existing layout • Practice, Practice, Practice

  3. 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

  4. 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

  5. Common Tables • Inventory Tables • Bridge, roadway • Inspection Tables • Inspevnt, eleminsp • Planning Tables • projects

  6. Custom Tables • Inventory Tables • Userbridge, userroadway • Inspection Tables • Userinspection • Agency can create, define, modify • Optional

  7. Example columns • Bridge Table • Brkey • Bridge_id • Struct_num • Featint • District • 122 columns of data in bridge table

  8. Relational Database • Data is stored in tables • A set of related tables forms a database • Key fields define relation of tables

  9. Relationships • Referential integrity (aka Dr. Phil) ensures that relationships between tables remain consistent • Integrity is enforced with primary keys and foreign keys

  10. 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

  11. Another Example • Inspevnt table • A bridge can have multiple inspections • Brkey • Inspkey • Other fields to help determine what type of inspection(s) was performed

  12. Schema on Pontis CD

  13. Schema

  14. QUESTIONS?

  15. Infomaker • Open InfoMaker • Navigate within InfoMaker • Connect to a Pontis database

  16. 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

  17. 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

  18. InfoMaker Navigation • The working areas in InfoMaker are called “Painters” • We’ll be working with two painters – database and library

  19. InfoMaker Navigation Shortcut to library painter Shortcut to database painter

  20. Database Painter • Connect to a database • View tables and columns • Launch/run SQL statements • Many other items – we won’t cover

  21. Connect to database • Click on Database painter icon

  22. Connect to database

  23. Connect to database

  24. View and edit data • Open up Tables • Open up Right Click on Bridge, Select Edit Data, Grid

  25. View and edit data

  26. 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

  27. View and edit data • Edit data • Save Changes

  28. Run SQL statement • Click on ISQL Session Tab • Type or paste SQL statements • Run the SQL • View the data, like before

  29. Library Painter • InfoMaker reports are stored in libraries • A library has the file extension PBL

  30. Library Painter

  31. Library Painter • Create a new library file • Copy items from one library to another • Many other items that we won’t cover today

  32. 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

  33. Create new library file

  34. 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

  35. Safe Practices/Thoughts • Copy reports from a production library • Work in a “working” library • Rename the report • Copy to a production library

  36. 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)

  37. Practice Time • Open Library Painter • Create New Library – PUG2007.pbl • Copy insp006_ report from pont_pcr.pbl library to our new library PUG2007.pbl

  38. 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

  39. Toolbars - customization

  40. 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

  41. Open Library Painter • Create a new library called PUG2007.pbl • Copy report insp003_ from pont_pcr.pbl to PUG2007.pbl

  42. Simple SQL Query • Work on simple SQL statements

  43. What is SQL? • SQL – Structured Query Language • Provides standard language for working with data in relational databases • Select • Sort • Count

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

  45. Sample SQL Statement SELECT bridge.bridge_id FROM Bridge WHERE bridge.owner = ‘1’ ORDER BY bridge.bridge_id ASC;

  46. Sample SQL Statement • Last query makes a list of all bridge id’s where the owner code = ‘1’

  47. SQL • Foundation for all reports, queries, forms, structure layouts • Four hours wouldn’t be enough time to cover everything

  48. 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

  49. Info from Pontis

  50. Test Run a SQL • We want: • Bridge.bridge_id • Bridge.facility • Bridge.featint • Culverts

More Related