Report Building Workshop

1
Report Building Workshop

• Presented By Todd Stout

2
Introduction

• Who am I?
• Who are you, what do you know what do you want
  out of this class?
• Objectives Agenda
• Changes to Agenda?

3
Who am I?

• Todd Stout, Stout Solutions
• Former EMS Stockboy, EMT, Paramedic, Consultant,
  Manager, Corporate Dude
• Former TriTech Manager
• Now Self-Employed Access Developer, Husband
  Father

4
What is Stout Solutions?

• Microsoft Access Report Development
• Hourly By Project
• VisiCAD 3.x
• VisiCAD 2.x
• Some Operational SSM Consulting

5
Who Are You?

• What VisiCAD customer are you with?
• Whats your level of experience?
• With the VisiCAD System database?
• With Microsoft Access?
• What do you hope to get out of this class?

6
Objectives Agenda

• Overview of VisiCAD System database
• Simplify your perspective
• Report Building Process
• Build a report from scratch
• Tips cool stuff

7
VisiCAD SQL System Database

• v3.7.2 has 234 tables 2,260 fields (see
  handout)
• 229 Autonumber (ID) fields
• 931 Number fields (various types)
• 815 Text fields
• 152 Date/Time fields
• 125 Yes/No fields
• 7 Memo fields 1 Binary field

8
VisiCAD SQL System Database

• Todds table types
• TriTech system tables (e.g., SetupInfo,
  Status_Actions)
• Lookup/Code File tables (e.g., Locations,
  Personnel)
• Event tables (e.g., Activity_Log, Page_Log)
• Call Shift tables (e.g., Response_Master_Incid
  ent)
• Dependant tables (e.g., Response_Vehicles_Assigned
  )
• Mixed/Other (e.g., Unit_Names)

9
VisiCAD SQL System Database

• System configuration and operational practices
  can change reporting approach
• Incidents vs. Responses, etc.
• Test your assumptions
• some fields with same names have different data
  types, etc.

10
VisiCAD SQL System Database

• Be cautious of reports impacting other VisiCAD
  users
• Live System (careful queries, SQL cursors, etc.)
• Importing to local workstation as Access tables
• Data Warehouse

11
Simplify Your Perspective

• Only 15 to 20 tables needed for most reporting
  purposes (see handout)
• You can always add more tables
• Getting started is much easier

12
Report Building Process

• There are many ways to create reports
• SQL, Stored Procedures, Crystal, VB, Excel, even
  many different ways within Access
• This is the way I recommend, but its always
  subject to change

13
Report Building Process (contd)

• 1. Think of something you want to know
• Consider if youll need it once or regularly
• 2. Design the output and get buy-in
• Consider the raw data and your ability
• Identify underlying and user-entered criteria
• 3. Who will run the report?

14
Report Building Process (contd)

• 4. Plan/design/draft the process
• May be one step, may be several
• 5. Build foundation query or queries
• Unless they are already built
• 6. Use Access wizards, or existing reports
• Or both

15
Report Building Process (contd)

• 7. Modify newly created report for 1st draft
• 8. Test criteria, calculations get feedback
• 9. Finalize report and process
• Develop forms to enter user-criteria and run
• Automate if applicable
• Make it pretty, fast unobtrusive
• Show Criteria On Report

16
Use Access Build a Report

• Tables
• Linked to SQL
• Linked to other MDBs
• Local Temporary
• Local Info Linking
• Queries
• Select
• Make Table
• Parameters for user input

• Queries (contd)
• CrossTab
• SQL Union Pass Through
• Can base queries on queries
• Forms
• gather user input
• inform the user
• Can be interim step or end result
• display data

17
Use Access Build a Report

• Reports
• Simple or complex lists
• Summaries
• Charts or graphs
• Can be recycled
• Can be exported
• Macros
• Makes programming point-and-click easy

• Macros (contd)
• Autoexec macro runs automatically at startup
• Use to provide action when the user clicks a
  button or takes action
• Use to make multiple steps seem like a single
  step to the user
• Modules
• Visual Basic

18
Tips Cool Stuff

• Backup your reporting mdb before making
  modifications
• Backup individual queries, forms, etc. during
  development
• Re-use your work
• Use Wizards

• Learn develop on a small data set
• Plan on writing 5-10 queries for every report you
  develop
• Plan your final output before you touch your
  computer

19
Tips Cool Stuff

• Use the Access help files and samples
• Use a standard naming convention names that
  make sense to others
• Use macros to print multiple reports
• Use Find Replace (or similar tool

• Consider Total Access Developer Suite
• Document your work
• Use WinAT (from the NT Resource Kit) to
  automatically run reports, macros, etc.
• Talk with TriTech before moving to Access 2000

20
Questions...

• Get presentation final mdb on website next week
  (http//www.stoutsolutions.com)
• Ask questions now or after workshop
• Ask later

Todd Stout, Stout Solutions 619 S. Vulcan, 205,
Encinitas, CA 92024 todds_at_stoutsolutions.com Phon
e 858-759-6950 Fax [phone]
Thank You!