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 4 SQL views you can use in Aeries Query if you like. These 4 are not the extent of all that is possible. They are a good example that anything is possible with Aeries.
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 4 items:
Program and Authorization Status (PGMS) - One flat row for their program involvement and authorizations.
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)
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.
Aeries Query Example:
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
Aeries Query to Excel:
NOTE: PGMS also looks good at the form level too.
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: (This output is wide and you may scroll over to see it)
|Student Name||Student ID||Grade||Tchr Num||TGPA||DateStarted||DateEnded||Section||Term||Period||CourseTitle||Teacher||Meets||GradReq||Dept||EnrolledDays||DaysAbsent||DaysTardy||TempNotEnrolled||PresentPercentage||PunctualPercentage|
|Abbott, Allan||99400001||12||708||3.97||07/02/2018||01/11/2019||1118||Y||1||IB Eng HL2||Stockton||MTWTF||A||E||128||12||4||0||90.625||96.551|
|Abbott, Allan||99400001||12||708||3.97||07/02/2018||08/24/2018||3093||Y||3||Span IV IB SL||Alvarado||MTWTF||L||F||39||1||0||0||97.435||100.00|
|Abbott, Allan||99400001||12||708||3.97||08/24/2018||01/11/2019||3194||Y||3||IB Span HL 2||Alvarado||MTWTF||L||F||90||10||1||0||88.888||98.750|
|Abbott, Allan||99400001||12||708||3.97||07/02/2018||12/21/2018||4004||F||4||Civics Cp||Hauser||MTWTF||C||H||120||0||0||0||100.00||100.00|
|Abbott, Allan||99400001||12||708||3.97||01/02/2019||01/11/2019||4188||S||4||Economics Cp||Bryan, R||MTWTF||C||H||8||0||0||0||100.00||100.00|
|Abbott, Allan||99400001||12||708||3.97||07/02/2018||01/11/2019||5133||Y||5||IB Biology SL||Smith||MTWTF||G||S||128||0||0||0||100.00||100.00|
|Abbott, Allan||99400001||12||708||3.97||07/02/2018||12/21/2018||7003||F||7||IB TheoryKnow||Goode||MTWTF||Z||H||120||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/02/2018||01/11/2019||93||Y||0||English 12 CP||Peer||MTWTF||A||E||128||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/02/2018||12/21/2018||1019||F||1||Civics Cp||Hauser||MTWTF||C||H||120||5||0||0||95.833||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||01/02/2019||01/11/2019||1120||S||1||Economics Cp||Wells||MTWTF||C||H||8||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/12/2018||01/11/2019||2015||Y||2||Weight Trng||Sanders||MTWTF||B||P||121||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/02/2018||07/11/2018||2036||Y||2||Algebraic Apps||Sneed||MTWTF||D||M||7||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/12/2018||01/11/2019||3157||Y||3||Algebraic Apps||Sneed||MTWTF||D||M||121||1||0||0||99.173||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/02/2018||07/11/2018||3175||Y||3||Psychology Cp||Trimillos||MTWTF||Z||H||7||0||0||0||100.00||100.00|
|Abea, Ayrianna||99400005||12||708||2.10||07/02/2018||01/11/2019||4127||Y||4||ROP Photo I||ROP-56-Hill||MTWTF||F||R||128||0||0||0||100.00||100.00|
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
NOTE: APBP is useful at the form level too
CONP - Contacts On One Line (Pivoted)
This view is intended for use with Aeries Query alone. It doesn't resemble one line in the form view. It cannot be suppressed from displaying as a form at this time.
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.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.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.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.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.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
GBKA - Gradebook Analysis
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.
While this will display for the top of the alphabet active student in a form, this is also better used within Aeries Query since it is not student specific.
Aeries Query Example: (This example is sorted by Department and Median Percentage descending)
LIST GBKA BY DC MDP ^
How to add these views in Aeries: (by the Database Administrator)
The 4 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.
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.