Please use this article as a supplemental resource for this DAT Webinar:  https://youtu.be/TKexAY9kD4o

1. Intro
    a.  Cover the basics of building Scheduling queries.  Send emails to support@pcreducator.com for suggestions. link
    b.  The Data Analytics Tool (DAT) is a comprehensive system for creating on-the-fly filters, exports, dashboards, mail merges, emailing, queries and reports supporting advanced database concepts with left joins, logical conditions, aliasing, aggregates, pivots, predefined reports, parameterization, and flexible security access.  link
    c.  DAT Query considerations link
        i. Plan what information will be extracted. link
        ii. Plan a workflow on what tables to use in the DAT - Scheduling Data Structure link
            a. Most common starting tables in the DAT - Scheduling Data Structure link
                i.  Student Info link
                    a. Statuses by Division - Used to get the student status. link
                    b. Red box / key fields explanation to avoid DAT Filter Common Mistakes link
                        - School Year field link
                        - Academic Year field link
                ii.  Teachers link
                iii.  Rooms link
                iv.  Courses link
                v.  Departments link
            b. Other important tables in the DAT - Scheduling Data Structure link
                i.  Selections link
                ii.  Student Courses link
                iii.  Course Sections link
                iv.  Course Section Rooms link
                v.  Class Schedule - link
                vi.  Class Schedule Blocks - This links Blocks to Class Schedule. link
                vii.  Blocks link
                viii.  Block Rotation link
2.  Standard "[Admin] Class List" query link
    a. Open "[Admin] Class List" link
        i. Review the report link
            -  DAT Query - Step 1 - Connecting Tables using With or Without link
            -  DAT Results - Step 4 link
        ii. Review the DAT - Scheduling Data Structure link
        iii.  Add a DAT Field Condition on school id link
        iv.  Make school id a DAT Parameterized Condition  link
        v. Connect Courses to School Info by DAT Query - Step 1 - Connecting Tables link
            -  Automatic DAT Field Condition added to School Info link
        vi.  Demonstrate changing parameter on DAT Results - Step 4 link
    b.  Modify the schedule to show days and blocks. link
        i.  DAT Query - Step 1 - Connecting Tables Course Sections to Class Schedule link
        ii.  DAT Query - Step 1 - Connecting Tables Class Schedule to Class Schedule Blocks link
        iii.  DAT Query - Step 1 - Connecting Tables Class Schedule Blocks to Blocks link
        iv. DAT Display Field Day of Cycle and Block Code link
        v. Add DAT Field Condition Multi-select Day of Cycle for days 1 through 5. link
        vi. Review DAT Results - Step 4 link
        vi. DAT Fields - Step 2 - Pivoting "Day of Cycle" on Min(Block Code) link
           - Still on parameter lower school link
        vii. Review DAT Results - Step 4 link
    c. DAT Save - Step 6 saving a standard query link
        i. Choose a different query group link
        ii.  Choose roles for the query link
3.  Class totals with capacity and available seats link
    a. Planning the query using DAT - Scheduling Data Structure link
    a.  DAT Query - Step 1 - Choosing Initial Table Courses  link
        i. Include DAT Key Id Field link
        i. Add school id DAT Parameterized Condition link
    b.  DAT Query - Step 1 - Connecting Tables Courses to Course Section link
        i.  DAT Query - Step 1 - Adding Conditions for school year link
            - School years over 100 link
        ii. Add DAT Parameterized Condition for semester link
        iii.  DAT Display Field Section and Section Capacity link
    c.  DAT Query - Step 1 - Connecting Tables Course Section to Student Courses link
    d.  DAT Query - Step 1 - Connecting Tables Student Courses to Student Info link
        i. Course Sections and Student Courses use the same school year.  Note: All scheduling tables linked directly to each other automatically use the same school id (when available) and school year, except where there is a table which breaks that sequence.  Example: Departments -> Courses -> Course Sections -> Class Schedule all use the same School Id.  Example: Courses > Student Courses > Student Info > Status By Division do not all use the same school year.  The students table does not have a school year which means the school year needs to be specified on Status by Division.  Note:  You can use a condition “= Other  Field” to create this link manually. link
    e.  DAT Query - Step 1 - Connecting Tables Student Info to Statuses by Division link
        i.  DAT Query - Step 1 - Adding Conditions Status Enrolled link
        ii. No School Id condition.  link
        iii.  DAT Query - Step 1 - Adding Conditions School Year for the current year link
    f. DAT Display Field Student Id link
    g.  Review DAT Results - Step 4 link
        i. DAT Key Id Field as links link
    f.  DAT Column Order - Step 3 columns link
    g. DAT Fields - Step 2 link
        i.  DAT Fields - Step 2 - Aggregating Student Id using Distinct Count link
            a. DAT Fields - Step 2 - Customizing Display Fields Appearance  of the aggregate field link
    h.  Review count in DAT Results - Step 4 link
    i. DAT Fields - Step 2 link
        i.  DAT Fields - Step 2 - Adding Calculated Field Section Capacity - Student Id as “Available Spots” link
    j.  Review DAT Results - Step 4 link
        i. Verifying DAT Results - Step 4 link
    k. Summary explanation of DAT Fields - Step 2 - Adding Calculated Field link
    l. Changing school id parameter value in DAT Results - Step 4 link
5. Student Selections by Department - does a student have selections for the academic departments? link
    a. Planning the query using the DAT - Scheduling Data Structure link
    b.  DAT Query - Step 1 - Choosing Initial Table Student Info  link
        i. DAT Query - Step 1 - Selecting Display Fields link
    c.  Link in Statuses by Division link
        i. Add conditions for next school year  link
    b.  DAT Query - Step 1 - Connecting Tables Selections link
        i. Specify next school year link
    c.  DAT Query - Step 1 - Connecting Tables Courses link
        i. DAT Query - Step 1 - Adding Conditions for School Id link
        ii. DAT Query - Step 1 - Selecting Display Fields Course Id link
    d.  DAT Query - Step 1 - Connecting Tables Departments link
        i. DAT Query - Step 1 - Selecting Display Fields Department Name.  link
    e. Review DAT Results - Step 4 link
    f.  Change DAT Column Order - Step 3 link
    g. Creating a pivot for DAT Fields - Step 2 - Pivoting link
        i. DAT Query - Step 1 - Adding Conditions using DAT Operator Multi-select on department names  link
        ii. DAT Fields - Step 2 - Pivoting Department Name on Count of Course Id link
    h.  View DAT Results - Step 4 link
        i. Interpreting the DAT Results - Step 4 link
    i. Discussion DAT Fields - Step 2 - Pivoting link
        i. Implications for this query in different divisions link
4. Review and summary link
 
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.