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 now 6 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)


¡ NEW !

  (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 classes for today pivoted on to one line.



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



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.




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: 



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 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. GBKA must be installed. The view name is extra long because it is intented for the form view and not Aeries Query. Use GBKA for Aeries Query instead.



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.



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
Happy Spring 2019 Conference!

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"