Customizing Pontis Reports with InfoMaker

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