Products
Admissions & Enrollment
After School Activities
API
Cafeteria & POS
School CRM
Data Analytics & AI
CMS
Donations
Enrollment Management System
Financial Aid Management
Finance & Accounting
Fundraising
Point of Sale
Scheduling
Student Information System
Summer School & Summer Camp
Solutions
K-12 Private and Independent Schools
K-12 Public Schools
Colleges & Universities
Medical Schools
Vocational and Technical Schools
International Schools
Dance and Theater Schools
Youth Programs
Why PCR Educator
Team
Knowledge Base
FAQ
Training and Support
PCR independent school management solution and website
Products
Admissions & Enrollment
After School Activities
API
Cafeteria & POS
School CRM
Data Analytics & AI
CMS
Donations
Enrollment Management System
Financial Aid Management
Finance & Accounting
Fundraising
Point of Sale
Scheduling
Student Information System
Summer School & Summer Camp
Solutions
K-12 Private and Independent Schools
K-12 Public Schools
Colleges & Universities
Medical Schools
Vocational and Technical Schools
International Schools
Dance and Theater Schools
Youth Programs
Why PCR Educator
Team
Knowledge Base
FAQ
Training and Support
Sign In
Demo
School CRM
Blog
DAT Webinar - Development Level I
DAT Webinar - Development Level I
DAT Webinar - Development Level I
Please use this article as a supplemental resource for this
DAT
webinar:
link
1. Intro
a. The Data Analytics Tool (
DAT
) is a comprehensive system for creating on-the-fly filters, exports, dashboards, print templates, emailing, queries and reports supporting advanced database concepts with left joins, logical conditions, aliasing, aggregates, pivots, predefined reports, parametrization, and flexible security access.
b. Cover the basics of building Development related queries. Send emails to
support@pcreducator.com
for suggestions.
c.
DAT Query
considerations
i. Plan what information will be extracted.
ii. Plan a workflow on what tables are used and how they will be connected.
a. Most common tables in the
DAT - Development Data Structure
i. Donor - Used to pull basic donor information.
ii.
Donation
- Used to pull basic donation information.
iii.
Contact
Attribute
s - Used to pull contact attributes such as Current Parent, Board Member etc.
iv. Households - Used to pull family information such as address, emails, phone numbers and more.
v.
Fund
Allocation - Used to connect donation table to campaigns and funds table and the amount allocated to each.
vi.
Campaign
s - Used to pull campaign information such as campaign description, type, goal and more.
vii.
Fund
s - Used to pull fund information such as fund description, fund code, and more.
viii. Relations - Student/Inquiry/Teacher - Used to connect the households table to students/inquiries/teacher records.
2. All Donors with
Donation
s Query
a. Start with the Donor table and select common fields such as donor id, contact name, and donor type.
b. Connect the Households table and select common fields such as address, contact names, emails, and phone numbers.
c. Connect the Donor table to the
Donation
(Giving) table and select common fields such as donation amount, donation date, donation type etc.
i. What is the
Donation
Type field used for and what is the difference between Original Amount,
Donation
Amount, and Written Off Amount.
d. Connect the
Donation
table to the
Fund
Allocation table.
e. Connect the
Fund
Allocation table to the
Campaign
s and
Fund
s tables and select common fields.
f. Organize the fields, change the sort order, and change the column order
i. Update Aliases and Display formats
g. Exclude the Pledge Payment donation type from the query because this query is to show donations that have either been pledged, sent in as a one time gift, or are soft credits. If both Pledge and Pledge Type donation types are included in the query, it will count the amounts twice.
i.Show how the conditions work.
h. Change this into a query that only shows donors with students affiliated with the school.
i. Connect the Relations - Student/Inquiry/Teacher table to the Households table.
ii. Connect the Student Info table to the Relations - Student/Inquiry/Teacher table.
a. Optionally, the query can even restrict the results to primary households or grandparent households in the relations table.
i. Student Info table - Used to pull basic student information.
3. Total
Donation
s by Fiscal Year
a. Start with the
Donation
table.
i. Exclude Pledge Payments and
Soft Credit
s.
ii. Add a Fiscal Year Offset multi-select condition for the Current and Last 4 Fiscal Years.
b. Navigate to Fields and update the Display Format for
Donation
Amount.
c. Set Pivot Aggregate to Sum
Donation
Amount and run the query.
d. Add other conditions to this query as needed. For example, to total up soft credits set the condition on the table to only include soft credits.
4. Update an existing Standard Query - [Development] Fiscal Year
a. This query lists the sum of donations that each family donated in the last 5 years.
b. Add household information.
i. Connect the Donor table to the Households table and select desired fields.
c. Save the query.
i. When updating a standard query and re-saving it, select a different name and store it in a different group. If the query is kept it in PCR(Imported) group, it will be overwritten.
5. Add Queries to Dashboard
a. How to add a query
6. Run a
DAT Query
report through multi-action
See also
DAT - Development Data Structure
Donation Types
Soft Credit
Attribute
DAT Query
Campaign
Donation
Contact
Action
Fund
DAT
Linkedin
301-947-7380
sales@pcreducator.com
Schedule Demo
Please, make sure that all required fields (marked with *) are completed.
School Name
*
Relation to School
*
select
Administration - Database Manager
Administration - Head of School
Administration - Registrar
Development - Director of Development
Finance - Business Manager
Finance - CFO
IT - Director of Technology
Other - Parent
Other - Staff
Other - Volunteer
Last Name
*
Your First Name
*
Your Email
*
Your Phone #
*
Household
User
PCR Educator School Information System is an online database engineered for schools and universities to deliver the highest level of flexibility, unique experience, transparent communication and customized design.