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 Query and DAT Filter Common Mistakes
DAT Query and DAT Filter Common Mistakes
DAT Query and DAT Filter Common Mistakes
The following are common mistakes related to creating a
DAT Query
with Data Analytics Tool (
DAT
).
-
DAT Filter
ing Differences: Must Have (A and B) versus Must Have A and Must Have B.
- Mostly, your
DAT Filter
s will be of the Must Have (A and B) variety. The conditions A and B are related and must be tested together.
Example
: Inquiry Must Have
Action
Description Application Received and Academic Year 2015/2016. Both the action description and the academic year are checked for the same action record. If you add two separate filters, one filter for inquiry Must Have Academic Year = 2016/2017 and the second filter for inquiry Must Have
Action
Description = Application Received, you won't be getting the results you expect. You will get a list of all applicants who have any action for the academic year 2016/2017, and have another Application Received action for any year. This means someone who applied 3 years ago and re-
inquiried
this year would meet the criteria. The conditions for the
Action
Description and Academic Year must be conditions on the same filter.
- Sometimes, your
DAT
filters will be of the Must Have (A and X) and Must Have (B and Y) variety. In this case, the test for A and B are supposed to be independent.
Example
: The student Must Have
Course
Name “English 12”, and the student Must Have
Course
Name “AP Calculus”.
i. The natural inclination is to create a single filter for the student Must Have
Course
Name in English 12, AP Calculus. The way filters work is they check each record for a match. Does the student have “English 12”? Yes? Then the student passes the condition. This filter acts like an OR condition, listing students who are taking either English 12 OR AP Calculus since either one is sufficient to pass the condition. You will have too many results.
ii. The next try: the student Must Have
Course
Name = English 12 And
Course
Name = AP Calculus. Now, suppose a student has English 12. Again, each record is checked for a match: The first condition is a match, but the second is not - English 12 is not AP Calculus. This second attempt return no results.
iii. So how do you correctly check that a student has both? You need two separate filters: the student Must Have
Course
Name = English 12 And the student Must Have
Course
Name AP Calculus.
Note
: Schedules have school years. These filters are not complete without specifying the school year that class is for. As with “
Action
s have Academic Years” and “Schedules have a school year” above, the school year must appear in both filters.
Note
: (i) uses a range, which when used outside of a filter can be used for
DAT Fields - Step 2 - Pivoting
. Notice that the values for the pivot are turned into columns, and if the record matches one or more of those columns, those matched columns have values. If no columns are matched, the record does not appear at all.
- Student's Status: The “Student Info” used to have Status and Substatus fields, but these were only there for backward compatibility. To ensure you get the correct status, link Student Info to “Statuses By Division”. This table will have the statuses for each division and current/next year. Make sure to specify the status you want, for the school divisions you want, in the school year you want.
- Inquiry's Status: The “Inquiries” table used to have Status and Substatus fields, but these were only there for backward compatibility. To ensure you get the correct status, link “Inquiries” to the “Inquiry Divisions” table. This table has the statuses for each division and academic year.
- If you link students (or inquiries) to households, and only want one row per student returned, add a condition limiting the Primary Indicator to P for the “Relations - Student/Inquiry/Teacher” to only get the primary households.
- Generally, it is not recommended to attempt creating filters when the relevant data is not present as the process of constructing conditions may be challenging. In particular, since the system automatically presents available options using the data in the database, the functionality of specifying conditions is limited.
- It is not recommended to place a
DAT Query
as a
DAT Dashboard Element
if it is expected to return more than 20 records because it may affect performance and take up a large amount of real estate on your dashboard.
- Checking for the absence of a record: To check for the non-existence of a record, you must use a Must Not
Have
filter.
Example
: You want a list of Applicants who have not completed a Student Visit. Construct your filter exactly as if you were looking for all the Applicants that did complete the Student Visit, but use Must Not Have for the filter instead of Must Have.
Example
: You want to find all students who do not have an action of Field Trip. The common mistake is to search for all student who “Must Have” and action with action description <> “Field Trip.” If a student has any other action besides “Field Trip” they will match the criteria for this filter. An “Enrolled” action has an action description unequal to “Field Trip” and matches the erroneous criteria.
-
Action
s have Academic Years: When looking for Inquiries/Applicants or Students who may have or have not completed certain actions, remember to include the Academic Year. Inquiries may have previously inquired and completed various actions, but chances are you are only interested in actions for the year they are currently applying for. Similarly, Students may enroll in your school one year, withdraw another year, and then come back years later.
- Schedules have a school year: When working with Scheduling tables, most of them have a School Year which should be specified. These tables would include Student
Course
s,
Course
Sections, Selections, and more. Before you start scheduling for next year, your queries will appear to work fine without a school year. However, once you start building next year schedules, if you haven't been specifying a school year, then both current year, next year, and What If Scenarios may start showing up.
Note
: School Years in scheduling are designated 0 for the current year, 1 for the next year.
- For Development >
Donation
, records may have donation types of Gift, Pledge, Pledge Payment, Matching, or
Soft Credit
. If you are totally actual giving amounts received then exclude donation types of Pledge, Matching, and
Soft Credit
.
Note
: If you include both Pledge and Pledge Payment in a
DAT Query
, then you may double count the amounts: once for the original Pledge and again for each pledge payment to that pledge.
Note
:
Donation
types of Matching are a special kind of Pledge and should be treated the same as Pledges. If your report excludes Pledges for the purposes of reporting then it should exclude Matching as well.
- For Daily and Period attendance no record is saved for students who are present. If you want to create a “Perfect Attendance” report, you will want to only select student fields and then apply a Must Not Have filter to your list of students.
Example
: You want a list of all students who received no discipline points during the marking period. You would create a filter connecting to Combined Period Attend And Discipline. Then add a condition for your date range and for points > 0. Save your conditions, click add, and change the filter type to Must Not Have.
See also
DAT Fields - Step 2 - Pivoting
DAT Dashboard Element
DAT Fields - Step 2
School Division
Donation Types
Soft Credit
DAT Filter
DAT Query
Donation
Action
Course
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.