The following are common mistakes related to creating a DAT Query with  Data Analytics Tool (DAT).

- DAT Filtering Differences: Must Have (A and B) versus Must Have A and Must Have B.  
    - Mostly, your DAT Filters 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 “Actions 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” has a Status and Substatus field, but these are only there for backward compatibility.  To ensure you get the correct status, join 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.

- 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.

- 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.

- Actions 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 Courses, 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 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.
PCR Educator K-12 School Information System is an online database engineered for independent and private schools to deliver the highest level of flexibility, unique experience, transparent communication and customized design. Being a complete cloud-based solution, PCR Educator system guarantees convenient access anytime, anywhere.