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 Insert Tools Toolbar Expert Tools Toolbar Formatting 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 it’s 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 > 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@aug.edu • Mark Brown • mbrown30@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