Aeries Query is quite empowering, yet you may wish to arrive at some results that could take many steps after running Aeries Queries. Some of those steps may not be something you can easily pass on for others to repeat. So to provide some empowering results with a single Aeries Query and make it easy, I made scripts that create many SQL views you can use in Aeries Query if you like. These views and custom tables are not the extent of all that is possible. They are a good example that anything is possible with Aeries.
If you currently have SQL Queries that you use they can easily become a View. Extended Query fields and DAQ field values can be included in your view. Custom Views appear in Permissions and can be made visible to Parents and Students, or limited to Users/Groups
Since that sounds complicated, I'll start by defining the ease and benefit for users. If you are hosted with Aeries, most any support rep could run the scripts to create these for you. If you are the Database Administrator that would easily run the creation scripts, that will be later in the article. The views can conveniently persist through rollover.
There are many items:
Program and Authorization Status (PGMS) - One flat row for their program involvement and authorizations. NOW EVALUATES OPTIONAL DATE IN APDS.JOD MAKING IT HISTORICAL.
Contacts On One Line (Pivoted) (CONP) - Yes, it does just that when you query it.
Gradebook Analysis (GBKA) - Many rich details about how students are performing in a gradebook in aggregate
Attendance Percentage by Period (APBP) NOW FOLLOWS OPTIONAL DATE RANGE IN APDS.
(GradebookClassStats) - A personalized view of how a students gradebook mark and percentage trend against their class (Your choice if you want to turn that on for Parents/Students)
(MSTP) - Current MST classes for today just on day in APDS.JOD pivoted on to one line.
(SMSP) - Current SMS classes for today just on day in APDS.JOD pivoted on to one line.
(APDR and APDS) - Attendance Percentage by Date Range (APDR) which will follow date range parameters specified in Attendance Percentage Date Setings (APDS). This allows parameters stored in a custom table to be used by a custom view. Currently attached in the ZIP file with more examples on its use to come...
(APFP/APFS) - Attendance Percentage by FLEX Period. APFS is Attendance Percentage by FLEX summary (how many periods in aggregate) for the optional date range in APDS.
PGMS - Program and Authorization Status
This View is adaptive to what your district actually uses. There are many Programs you may not populate so they are not a part of your PGMS reality. Authorization code choices are also different for every district. So when this is created by the script being run, only the things you use will become a part of your PGMS view. It is truly a one size fits all solution.
For Example:
Aeries Query:
LIST STU PGMS STU.SC STU.ID STU.NM STU.GR STU.SP? STU.TG? PGMS.NSL PGMS.SPD PGMS.P1 PGMS.P2 PGMS.P3 PGMS.P4 PGMS.P5 PGMS.P6 PGMS.P7 PGMS.P8 PGMS.A1 PGMS.A2 PGMS.A3 PGMS.A4
Not all Students have all the Programs included in the Query so it's not easy to tell at a glance what each Students Programs are.
Aeries Query to Excel:
NOTE: NSLP and SpEd are included in this example. You may not necessarily have them if they were hidden. Your amount of Programs and Authorizations will likely differ.
APBP - Attendance Percentage by Period
This allows you to see both attendance and tardy information by class. It tends to run long as an Aeries Query, so sending it to Excel is best. In the event that it produces nothing, it is likely that it exceeded the allowed time. Scoping the output to a single school, grade or even one period can allow it to complete from Aeries Query to Excel. If an entire district extract is desired at the same time, then the view may best be used from SQL after prime hours of the day.
Aeries Query Example:
LIST STU APBP STU.NM STU.ID STU.GR STU.CU STU.TP APBP.DS APBP.DE APBP.SE APBP.SM APBP.PD APBP.CO APBP.TE APBP.DY APBP.S1 APBP.DC APBP.EIC APBP.DAB APBP.DTY APBP.TNE APBP.PRS APBP.PNC IF STU.GR = "12"
Aeries Query to Excel:
There are many columns in the view so you may dig in to the data in many imaginative ways. (e.g. Attendance percentage by Department or Graduation Requirement)
Here is a SQL query to output the same. You can tweak the where clause to run the entire school or district since the timeout is not a limiting factor. Naturally exporting a huge district all at once may be best performed after peak hours.
select STU.SC, LOC.NM SchName, STU.LN + ', ' + STU.FN NM, STU.ID, STU.GR, STU.CU, STU.TP, APBP.DS, APBP.DE, APBP.SE, APBP.SM, APBP.PD, APBP.CO, APBP.TE, APBP.DY, APBP.S1, APBP.DC, APBP.EIC, APBP.DAB, APBP.DTY, APBP.TNE, APBP.PRS, APBP.PNC FROM STU LEFT JOIN APBP ON STU.SC = APBP.SC AND STU.SN = APBP.SN INNER JOIN LOC ON STU.SC = LOC.CD WHERE STU.SC = 994
When this SQL is used as a Custom View:
CONP - Contacts On One Line (Pivoted)
This view is intended for use with Aeries Query alone. It is hidden from the navigation by choosing no where for it to appear.
Up to 6 Contacts displayed on one line. The [CON].[OR] sort order is used in conjunction with 4 items that would indicate what contact is the more primary from left to right. (Access to Portal, Primary Contact, Educational Rights Holder, Lives With)
Aeries Query Example:
LIST STU CONP STU.SC STU.ID STU.FN STU.LN STU.GR CONP.RL1?CON.RL CONP.CD1?CON.CD CONP.NM1 CONP.FN1 CONP.LN1 CONP.TL1 CONP.CP1 CONP.WP1 CONP.EM1 CONP.AD1 CONP.CY1 CONP.ST1 CONP.ZC1 CONP.ME1 CONP.RL2?CON.RL CONP.CD2?CON.CD CONP.NM2 CONP.FN2 CONP.LN2 CONP.TL2 CONP.CP2 CONP.WP2 CONP.EM2 CONP.AD2 CONP.CY2 CONP.ST2 CONP.ZC2 CONP.ME2 CONP.RL3?CON.RL CONP.CD3?CON.CD CONP.NM3 CONP.FN3 CONP.LN3 CONP.TL3 CONP.CP3 CONP.WP3 CONP.EM3 CONP.AD3 CONP.CY3 CONP.ST3 CONP.ZC3 CONP.ME3 CONP.RL4?CON.RL CONP.CD4?CON.CD CONP.NM4 CONP.FN4 CONP.LN4 CONP.TL4 CONP.CP4 CONP.WP4 CONP.EM4 CONP.AD4 CONP.CY4 CONP.ST4 CONP.ZC4 CONP.ME4 CONP.RL5?CON.RL CONP.CD5?CON.CD CONP.NM5 CONP.FN5 CONP.LN5 CONP.TL5 CONP.CP5 CONP.WP5 CONP.EM5 CONP.AD5 CONP.CY5 CONP.ST5 CONP.ZC5 CONP.ME5 CONP.RL6?CON.RL CONP.CD6?CON.CD CONP.NM6 CONP.FN6 CONP.LN6 CONP.TL6 CONP.CP6 CONP.WP6 CONP.EM6 CONP.AD6 CONP.CY6 CONP.ST6 CONP.ZC6 CONP.ME6
GBKA - Gradebook Analysis
This view is intended for use with Aeries Query alone. It is hidden from the navigation by choosing no where for it to appear.
The freely included gradebook in Aeries is designed to make things easy for teachers with the added benefit of allowing students and parents to know how they are doing. Nothing can help assure that opportunity is not lost than an easy to use gradebook visible to all stakeholders. That having been said, this analysis allows you to see how your classes are doing within their gradebooks. A teacher with few assignments that has only begun to use a gradebook may benefit if you partner them with someone more adept at the use of the gradebook. Please don't be too presumptuous when reviewing data. If you found grading appeared harsh with a median percentage of 68% for example, having a conversation with someone may reveal that they take on the more at risk students that were failing remarkably. If that were the case, they are a hero.
Aeries Query Example: (This example is sorted by Department and Median Percentage descending)
LIST GBKA BY DC MDP ^
GradebookClassStats - How is a student comparing to just their class alone?
This view depends upon GBKA which must be installed first. The view name is extra long because it is intented for the form view and not Aeries Query. Use GBKA for Aeries Query instead.
Attendance Percentage by Date Range (APDR) and Attendance Percentage Date Settings (APDS)
After installing these two items you may change any one student or many to have a date range in the APDS custom table.
NOTE: The (APDS.NTS) Note field is persistently populated by trigger with an informative comment
The Attendance Percentage by Date Range (APDR) view will then use the parameters in th APDS custom table to result in custom content using the specified range. This is a first example of passing parameters to a view since it would not otherwise be possible from the IF portion of Aeries Query.
If no dates are specified in APDS they will not be displayed and the range is naturally year to date
How to add these views in Aeries: (by the Database Administrator)
The 6 items in the ZIP file attached to this article is all you need to run against your database in SQL Server Management Studio. If you are hosted with Aeries, simply ask that it be done.
Once installed, only an Aeries Admin will see them until they grant permissions to others directly or to groups within security. GBKA is not for parents and students.
NOTE: Your new year creation (Rollover) options should have Copy custom SQL modules selected and these views will remain with you as long as you like.
To be usable in Aeries Query the view name is 4 characters. 3 characters can work for a custom table yet collide with a future table name choice by Aeries Software. I recommend 4 character names.
Change Log:
2018-10-25 Now includes version control to update only if newer and not specifically customized by you.
2018-11-28 PGMS - DEL tagged AUT rows were considered in error - FIXED
PGMS also updated to allow hiding the NSLP status and SpEd columns conveniently
2018-12-20 CONP now includes CON.CD
2019-01-03 Changed GBKA to be displayed on first alphabetical student instead of first student number. Also ordered CTF elements at bottom of creation script to look better.
2019-01-12 PGMS now defaults to not populate NSLP and Special Ed columns in to the the CTD definition. They are still a part of the SQL view.
2019-02-04 CONP now includes the memo field with carriage returns, line feeds, and tabs suppressed. It also no longer displays in the navigation
GBKA no longer appears in the navigation
2019-03-01 Created new GradebookClassStats view which is intended to focus on one student and how they trend against their class. This may be something you want visible to parents.
2019-03-01 Created new pivot view of current classes (MSTP) as in "MST pivoted"
2019-05-02 PGMS updated to tolerate new state specific fixed codes for PGM
2020-03-16 APDR and APDS added Attendance Percentage by Date Range (APDR) which will follow date range parameters specified in Attendance Percentage Date Setings (APDS). This allows parameters stored in a custom table to be used by a custom view.
2020-06-25 APDR now includes [Days Made Up] column which is factored in to the percentage
2020-10-05 MSTP is no longer restricted to the classes of today. Simply specify just one day in the APDS.JOD field beforehand.
(e.g. CHANGE APDS JOD TO "10/5/2020")
2020-10-05 SMSP was created because it is similar to MSTP and since SMS is never in use on 'Today' the use of APDS.JOD now allows for this.
2020-11-24 PGMS was modified to suppress header description to 128 characters as this is the maximum header width of MSSQL. Keep in mind that verbose headers are not likely as helpful. (e.g. 'Acceptable Use Policy Consent' vs 'I have both read and understand the Acceptable Use Policy here in after refered to as the AUP, I hearby consent to the terms of the AUP and will instruct my student to operate within the limitations of the AUP.')
2021-06-02 APDS was lacking properly defined primary key allowing some to insert more than one row via SQL. Fixed.
2021-06-30 APDS trigger was heavy on rollover and will only run 24 hours after the first LOG.DT row. APDS rows can no longer be deleted.
2021-09-03 APDS last adjustments were not posted to the 'All_Custom_Objects.sql' file - now updated
2022-03-06 APFP/APFS Attendance Percentage by FLEX Period. APFS is Attendance Percentage by FLEX summary (how many periods in aggregate) for the optional date range in APDS
2022-03-06 MSTP and SMSP now work with FLEX and tracks. NOTE: SMSP will work once the coming year's calendar exists just after rollover since tracks are in DAY.
2022-03-06 PGMS is now historical by evaluating a date specified in APDS.JOD
2022-03-06 APBP Attendance Percentage by Period now limits to an optional date range in APDS.SWH through APDS.TWH
2022-09-26 APDS changes on 2022-06-02 were missing. Corrected. All hosted customers having APDS were corrected
Longer term potential idea:
Dynamic creation of TST aggregate score analysis. View names of TS01 through a potential TS99 will be used.
Example of TST.PL
Goal: Every test ID has its own view, each view dynamically senses parts that exist and which columns have actual population. This will be a HEAVY lifter for sure paving the way for Analytics to shine even brighter!