Getting Your Data Out Using Custom Tables with Query

1
Getting Your Data OutUsing Custom Tables with
Query Crystal Reports

• Session 109
• March 5, 2002 1145 a.m.
• HEUG 2002 Conference - Las Vegas

2
Chris Couture Contributor Relations
Specialist Functional Technical Lead Maryland
Institute College of Art Contributor Relations
Implementation
3
FOR YOU

• My hope is to share with you a picture of MICAs
  decision making process relating to reporting
  needs problems, including
• Determining which data to include in custom
  tables
• How we decided where to manipulate that data
• Which options put the end result in the users
  hands.

4
MICA ENVIRONMENT

• SQL Server 7
• Student Admin 7.6, all available modules
• PeopleTools 7.62
• Crystal Reports 6
• 36,000 Constituents in Contributor Relations
• 12 years of gift related data

5
WHAT THEY WANT

• Annual Fund Director Progress reports in
  numerous variations, receipts, reminders,
  solicitation mailing list files, single donor
  giving history, Phonathon cards.
• Alumni Manager Event mailing lists, report of
  alumni by year/region.
• Capital Campaign Planned Giving Team Single
  donor giving history.

6
WHAT THEY WANT

• Communications Department Mailing list for
  newsletters, exhibition event announcements.
• Parent Coordinator Labels and mailing lists,
  report of parents by year.
• Database Manager Various ad hoc reports
  requested by above and senior level management.

7
FREQUENTLY REQUESTED DATA

• Preferred, Joint, Birth, and Spouse Names
• Organization Name and Contact Name
• Address (including Country if outside USA)
• Phone e-mail address
• Highest ranking constituent type

• Board Council membership
• Prospect Rating Giving Club
• Service Indicators
• Class Year
• Giving History
• Recent gift information

8
REPORTING NEEDS

• Put the ability to run report into the hands of
  those who need the reports.
• Have Giving Club levels and Prospect Ratings
  accurately reflect recent giving.
• Short query run time.
• Include persons and orgs in one report, file, or
  list.

9
PROBLEMS

• SQL Server makes outer joins in Query difficult.
• Delivered Giving Club processes fails to meet
  needs.
• Desired bits of data reside in numerous tables.
• Person and org data reside in independent tables.
• Lack of technical ability beyond Database Manager.

10
PROPOSED SOLUTION

• Procedurally update certain data based on
  previous days activity.
• Repopulate daily four MICA designed reporting
  tables.
• Use Query to extract data from reporting tables.
• Develop Crystal Reports to use these queries in
  presenting the data.
• Compile the Crystal Reports into executables
  which can be run by end users.

11
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
12
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
13
SQL STORED PROCEDURES

• Four procedures run at 200 am into production
  database

• Inserts higher level Prospect Rating, if an
  upgrade is required, based on recent giving
  activity
• Updates Giving Club level based on recent giving
  activity
• Populates three reporting tables
• Populates fourth reporting table.

14
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
15
REPORTING TABLES
MICA_AV_BIODEMO Bio/Demo data
MICA_AV_CMP_TBL All campaign gifts, filtered
MICA_AV_SRVC_IND Negative service indicators
MICA_AV_LGIFT_T Ratings, levels, most recent
gift
Data is never more than 24 hours old.
16
REPORTING TABLES
MICA_AV_BIODEMO
17
REPORTING TABLES
MICA_AV_BIODEMO

• Includes both person and org data.
• Allows for preferred, joint and maiden names.
• Places primary contact name in org row.
• Includes ID for spouse, student, and org contact.
• Includes involvement code primarily for select
  purposes.
• Includes current PERM address/Primary Location.

18
REPORTING TABLES
MICA_AV_CMP_TBL

• Data comes from
• Specifically excluded are
• paid pledges
• org payment of Matching Gift
• adjusted gifts.

19
REPORTING TABLES
MICA_AV_CMP_TBL

• Contains gift data from all campaigns.
• Includes only these types of gifts
• Unpaid pledges
• Pledge payments
• Non-pledged gifts
• person portion of Matching Gift commitment.
• Includes name of Matching Gift company.

20
REPORTING TABLES
MICA_AV_LGIFT_T

• Data comes from
• AV_GCLUB_MBR
• AV_PRSN_RATING
• AV_RCG_DES

21
REPORTING TABLES
MICA_AV_LGIFT_T

• Includes summary of most recent giving for
  Annual, Capital and Other campaign.
• Includes Giving Club level based on most recent
  gift for campaign.
• Includes Prospect Rating for campaign effort.
• Every person or org who has a Constituent Type
  has one row in this table.

22
REPORTING TABLES
MICA_AV_SRVCIND

• Data comes from
• SRVC_IND_DATA
• AV_SRVC_IND_ORG
• Includes all Advancement related Negative Service
  Indicators in one row for each person or org.
• Every person or org who has a Constituent Type
  has one row in this table.

23
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
24
PS QUERY

• Using the Reporting Tables, developed a set of
  basic queries used for nearly all reports.
• Output is generally consistent, thus the focus
  can be put on modifying the select criteria.
• Most queries include run-time prompts.
• Now, Query is generally the easiest part of
  developing a new report!

25
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
26
CRYSTAL REPORTS

• Receipts Reminders
• Phonathon Cards
• Campaign progress reports
• Mailing labels
• Data file generation for mailing house
• Data file generation for internally generated
  mailings

27
CRYSTAL REPORTS

• Utilize formulas for selection of
• Joint vs. single name
• Class year printing where appropriate
• Printing of Org name
• Combining Faculty Staff into one Constituent
  Type
• Creating a Category for Trustees within
  Constituent Type.

28
CRYSTAL REPORTS

• Most utilize run-time prompts built into queries.
• May also include Crystal prompts for report
  headers.
• Some utilize Crystal select options to further
  filter data selected by queries.

29
CRYSTAL REPORTS

• Compile as executables.
• Place in shared directory on a central file
  server.
• Add shortcuts on each person's Start Menu to
  minimized the potential for them to mess up the
  actual files.

30
IMPLEMENTING THE SOLUTION
SQL Stored Procedures
Reporting Tables
Crystal Reports
PS Query
31
RESULTS

• End users able to run reports as needed.
• Automate Giving Club and Prospect Rating update
  process.
• Reports can be run on demand.
• Reports can include all constituent types.
• Ease in report development reduces turnaround
  time.

32
KEY ELEMENTS FOR SUCCESS

• Teamwork!
• Mental physical well being.
• Strong commitment from senior management.
• Firm understanding of business practices,
  procedures, and policies.
• Technical knowledge of and experience with the
  application.

33
Chris CoutureContributor Relations
Specialist443.416.7589chris_at_couturetech.netwww.
couturetech.netHTTP//higheredsig.cua.edu/
(attendees may download HEUG2002 presentations
from the archives at this location)
PLEASE ASK ME!