Crystal Reports 9 using the Banner Database

1
Crystal Reports 9 using the Banner Database

• Augusta State University
• Projects Office

2
Agenda

• Introductions
• Running a report
• SCT BANNER Databases
• Report Wizard
• Crystal Professional
• Summary Reports
• Cross-tabs

3
What is Crystal?

• Report writer for SCT Banner, PeopleSoft and
  other databases
• Can create reports, datafiles, letters, labels
  and merge files for mass e-mails

4
Running a Crystal Report

• Most users only run reports
• Where to find reports at ASU
• How to run a report

5
When to use Crystal

• The information is simple to obtain
• You are only one that needs the information
• It is needed immediately and you can get it from
  Crystal
• Sent a request to ITS Programming if
• If the information is complicated (requiring
  calculations, extensive joins, etc.)
• If the information will be needed on a routine
  basis or is needed by other departments

6
DatabasesRelational Structures

• Key Field- a field which uniquely identifies each
  row of data on a table
• Multiple Tables
• Empty or Null rows do not take up space
• Can have virtually unlimited rows ie Multiple
  telephone numbers

7
Understanding Banner Database
Banner uses forms to enter or change data in an
Oracle database Data entered on one form used by
other forms, reports, jobs
8
BANNER Database

• SGASTDN - Form name
• SGBSTDN - Table data is stored in
• Each table has an owner
• Format for a field name Database table
  name_ field name
• How to find Field Names
• Place cursor in field and click F1
• Help, Dynamic Help Query

9
What is a View?

• A grouping of information in a logical order
• (i.e. enrolled students)
• Designed to make report writing easier
• Some are database fields (Last Name)
• Some are calculated fields called functions
  (Freshman)

10
Why are views better than tables?
11
Example of Tables used in BannerOwner - Saturn
12
ASU ViewsSome examples
13
Report Wizard

• Find a database view or table
• Link multiple tables and/or views together
• May need to link on multiple fields
• Pidm
• Term
• CRN
• Seq No
• Appl No

14
Report Wizard

• Logon to database
• Database displays table and views in expandable
  nodes by owner

15
Banner Databases

• HOT - prod.ac.usg.ga.us
• PPRD prep2.ac.usg.ga.us
• Once used, database name under History
• Can also right click and add Banner databases to
  list of Favorites
• Sign on to database

16
Complex Linkage
17
Report Wizard

• Add fields
• Select records
• Select Template
• Optional
• Can create master templates

18
Record Selectionwithin Report Wizard
19
Crystal Reports Toolbars
Standard Toolbar
Formatting Toolbar
Insert Tools Toolbar Expert Tools Toolbar
20
ToolbarsExpert Tools Toolbar
Highlighting
Group
Record Sort
Section
Template
Database Expert
Group Sort
Select
Formula Workshop
Format
21
Professional Window

• Save often
• Print
• Sort the data
• Export into a spreadsheet
• Summary information
• Grouping

22
Report Selection within Crystal Professional
23
Exporting Crystal Reports

• Click envelope
• Send to non-Crystal user
• .pdf file
• Microsoft Word file
• Excel file
• Datafile for mail merge

24
Formatting

• Report titles
• Column headings
• Logos and pictures
• Borders and colors
• Headers and footers
• Charts and tables

25
Sample Formatting
26
Summary Reports

• Summary Report
• Create full report
• Hide or suppress detail
• Can suppress detail based on formula
• Suppress if balance 0

27
Summary Operations

• Sum
• Average
• Maximum and minimum
• Count and distinct count
• Statistical analysis

28
Drill Down Reports

• Drill down report
• Summary reports
• Double click on summary to get detail within a
  group
• Mouse pointer becomes a magnifying glass when
  drill-down is toggled
• For confidential information (ie salary of
  employees)
• Suppress (No Drill-Down)

29
Groups

• Group sort with breaks
• Summarize by group
• Subtotals
• Exclude group based on selection criteria
• Exclude vendors with 0 balances
• Grand total at end of report

30
Formula Editor

• Check formula syntax

31
Cross-Tabs Report
32
Cross-Tab Overview

• Summarize large quantities of data
• Rows and columns for an easy to read format
• Identify trends, make comparisons
• Descriptions where by is included
• Best of all its remarkably easy!

33
When to use Cross-Tabs

• Average GPA by major
• Admit type by Ethnicity
• Hope awards by classification

34
Cross-Tab Expert
35
Cross-Tab Tips

• Fields with most groups should be rows for
  printing purposes
• Cross-tab cells cannot contain anything but
  numbers created from summary functions
• Select multiple objects by using CTRL-click.
  Then formation all at the same time through
  Formatting toolbar or the Format Editor
• Cannot drill down on a cross-tab cell

36
Editing an Existing Cross-Tab
Right click in white box in upper left hand corner
37
Alerts

• When a value requires immediate action
• Bad Data
• Amount over a specified total
• Negative numbers
• Examples
• Printing gt 500 letters (add paper to printer or
  use email)
• Report on SAT tape loads with Alert if GPA H or
  G

38
Report Design

• 10 Things to Ask Before You Create a Report
• What is the purpose of the report?
• What fields do you want to included in the
  report?
• What views or tables are they from?
• Do you want all the records or a subset?
• How do you want the data grouped?
• How do you want the data sorted
• What summary calculations do you want in the
  report?

39
Crystal Tips

• When designing report, use Saved Data if
  possible to save time, help overall performance
• Use parameters Term, Major
• Do not end task for at least 15 minutes

40
Questions Later?

• Augusta State University
• Projects Office
• Mary Filpus-Luyckx
• mfilpusl_at_aug.edu
• Mark Brown
• mbrown30_at_aug.edu
• Get the tech briefs from Crystal Decisions
  http//support.crystaldecisions.com/
• Books Crystal Reports 9 for Dummies A
  Reference for the Rest of Us, by Allen G. Taylor
• Crystal Reports 9 The Complete Reference, by
  George Peck