Transcription
Crystal Reports Training Guide- Appendix BCommon Formulas & Techniques for Handling Skyward DataGrade Level CalculationsThere are several different methods. A few of them are shown here.IF-THEN Statement#1- this method returns a number that represents the grade level of the student. Thismethod is not particularly efficient as it continues to perform the calculation until it finds the condition.IF ({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12 0THEN "KG"ELSE IF ({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12 0THEN "PK"ELSE IF ({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12 12THEN "GD"ELSE TOTEXT(({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12,0,"","")Case Statement- this method is particularly nice if you need something other than the grade numberreturned from your formula. It is fast and efficient as the calculation only needs to be performed once.SELECT ({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12CASE -2: "Early Childhood"CASE -1: "Pre-Kindergarten"CASE 0: "Kindergarten"CASE 1: "First"CASE 2: "Second"CASE 3: "Third"CASE 4: "Fourth"CASE 5: "Fifth"CASE 6: "Sixth"CASE 7: "Seventh"CASE 8: "Eighth"CASE 9: "Freshman"CASE 10: "Sophomore"CASE 11: "Junior"CASE 12: "Senior"DEFAULT: "Graduated";
Grade Level Calculations- ContinuedIF-THEN Statement#2- this method also returns a number that represents the grade level of the student.This method is much more efficient than the first example as the calculation is only performed oncewhile preserving the flexibility. The values are simply held by variables until needed for either printingor comparison.LOCAL stringVar lvGradeDisplay;LOCAL numberVar lvGradeLevel;lvGradeLevel : ({ENTITY.SCHOOL-YEAR}-{STUDENT.GRAD-YR}) 12;IF lvGradeLevel 0THEN lvGradeDisplay : "KG"ELSE IF lvGradeLevel 0THEN lvGradeDisplay : "PK"ELSE IF lvGradeLevel 12THEN lvGradeDisplay : "GD"ELSE lvGradeDisplay : TOTEXT(lvGradeLevel,0,"","");lvGradeDisplay;Middle InitialAs there is no middle initial field in the database, you will need to pull the first letter from the MIDDLENAME field. This can be performed as a simple LEFT function as shown below.LEFT({NAME.MIDDLE-NAME},1)To combine this with the rest of the name is often performed similar to what is shown here:{NAME.LAST-NAME} ", " { NAME.FIRST-NAME} (if isnull({NAME.MIDDLE-NAME})or {NAME.MIDDLE-NAME} ""then ""else " " left(trim({NAME.MIDDLE-NAME}),1) ".")Social Security Number FormatSocial Security Numbers reside in the NAME table, FEDERAL-ID-NO field. This just has the 9 digitsin the field, without the hyphens. Do this with a combination of concatenation, LEFT, RIGHT, and MIDfunctions. The formula is shown below (carriage returns only used due to space limitations of this page):LEFT({NAME.FEDERAL-ID-NO},3) "-" MID({NAME.FEDERAL-ID-NO},4,2) "-" RIGHT({NAME.FEDERAL-ID-NO},4)As with most optional fields (this field is not required for Student Records), it is recommended that aNULL check is performed on this field prior to processing the formula.
Phone Number FormatsPhone numbers also reside in the NAME table. There are potentially four different phone fields(excluding extensions) tied to each individual. These fields The method of formatting this data is similar to Social Security except, the logic for phone numbersmust account for a missing area code. This is achieved by use of the LENGTH function to firstdetermine if there is indeed an area code on this number. The remaining logic is handled with acombination of concatenation, LEFT, RIGHT, and MID functions. The formula is shown below(carriage returns only used due to space limitations of this page):IF ISNULL({NAME.PRIMARY-PHONE}) OR {NAME.PRIMARY-PHONE} ""THEN ""ELSE IF LENGTH({NAME.PRIMARY-PHONE}) 7THEN LEFT({NAME.PRIMARY-PHONE},3) "-" RIGHT({NAME.PRIMARY-PHONE},4)ELSE IF LENGTH({NAME.PRIMARY-PHONE}) 10THEN "(" LEFT({NAME.PRIMARY-PHONE},3) ") " MID({NAME.PRIMARY-PHONE},4,3) "-" RIGHT({NAME.PRIMARY-PHONE},4)ELSE "Incomplete Phone Number on File"Phone Number Type SelectionWhile the previous formula is helpful in formatting a number, it does not help you select a particulartype of number. For example, let’s say there is a form that needs to have the home phone listed. Theproblem is that the data entry staff may have been inconsistent in how home phones are entered; the datamay be the primary number or it may be in SECOND-PHONE or even THIRD-PHONE.To accomplish this selection we will need to do this by use of variables. Variables simply act as aholding mechanism for a piece of data until it is needed again. Works a bit like a bucket or storage binwould in everyday life. To be more efficient, we will assess all the values we need in one formula andthen can create separate fields for each of the specific phone numbers their own formulas. This preventsthe formula for each type of number from reading all three possible fields each time the value needs toprint. The only thing to be careful with here is the formula that reads all the numbers must precede theformulas that will print the telephone numbers. For example, if the numbers print on a detail line thenthe other formula must be on either a group header or on a higher detail line. Note: the line and or thefield can be suppressed; they do not need to print on the report. Example formulas, using the cell phone,are shown below:Formula Continues on the Next Page
Phone Number Type Selection- ContinuedFormula #1:Shared stringVar svHomePhone : "";Shared stringVar svCellPhone : "";Shared stringVar svWorkPhone : "";Shared stringVar svOtherPhone : "";If {NAME.SECOND-PHONE-TYPE} "H"then svHomePhone : trim({NAME.SECOND-PHONE})else if {NAME.SECOND-PHONE-TYPE} "C"then svCellPhone : trim({NAME.SECOND-PHONE})else if {NAME.SECOND-PHONE-TYPE} "W"then svWorkPhone : trim({NAME.SECOND-PHONE})else if {NAME.SECOND-PHONE-TYPE} "O"then svOtherPhone : trim({NAME.SECOND-PHONE});If {NAME.THIRD-PHONE-TYPE} "H"then svHomePhone : trim({NAME.THIRD-PHONE})else if {NAME.THIRD-PHONE-TYPE} "C"then svCellPhone : trim({NAME.THIRD-PHONE})else if {NAME.THIRD-PHONE-TYPE} "W"then svWorkPhone : trim({NAME.THIRD-PHONE})else if {NAME.THIRD-PHONE-TYPE} "O"then svOtherPhone : trim({NAME.THIRD-PHONE});If svHomePhone ""then svHomePhone : trim({NAME.PRIMARY-PHONE})else svHomePhone : svHomePhone;The preceding formula must appear before the formula(s) that show the individual phone numbers.Please review the example on the following page. The preceding field (named PhoneCalc) is shown inred, simply for purposes of illustration.Formula Continues on the Next Page
Phone Number Type Selection- ContinuedFormula #2:Shared stringVar svCellPhone;if length(svCellPhone) 7then left(svCellPhone,3) "-" right(svCellPhone,4)else if length(svCellPhone) 10then "(" left(svCellPhone,3) ") " mid(svCellPhone,4,3) "-" right(svCellPhone,4)else ""Printing Race CodesDepending on state reporting requirements and/or district policies, there are potentially two differentways race data may be stored. If your district does not track the ethnicity breakdown of multi-raceindividuals, then simply use the NAME.RACE-CODE field. It is worth noting, that if you need themajority race of a multi-race individual, this value is also stored in NAME.RACE-CODE. The racedescriptions can be retrieved by linking NAME.RACE-CODE directly to RACE.RACE-CODE.If you track the ethnic breakdown of multi-race students, the process becomes slightly morecomplicated. For the individuals who are not multi-race, their records can be retrieved exactly as shownabove. As mentioned earlier, if you want the majority race for a multi-ethnic individual, that can also beachieved using the above method. However, if you need all the race data for multi-ethnic individuals,NAME-RACE also needs to be in your report.
Race Code Reporting- ContinuedBecause only the multi-race individuals will have records in NAME-RACE, this table will need to be aLEFT OUTER join as will your alias of RACE. Example is shown below:The actual formula for how the race codes will print is shown below. Be sure to perform the NULLcheck before trying to read any other values in this formula.IF ISNULL({NAME.RACE-CODE})OR {NAME.RACE-CODE} ""THEN ""ELSE IF ISNULL({NAME RACE.NAME-ID})THEN {RACE.RACE-SDESC}ELSE {RACEalias Multi.RACE-SDESC}Formatting Account CodesAccount numbers are held in three different fields on Skyward’s Financial Management system. Allthree of these fields are in the same table, FFAMAM-ACCT-MST. As you can see from the examplesbelow, these fields hold the same data, each field just has a different structure.FFAMAM-ACCT-DIM: This is a delimited array field (Progress “extent” field). Each accountdimension is stored in a separate array position. Sample account number would look like:204;5;31;6141;00;103;5;24;000;;;;;;;;;;;;;; (20 positions)FFAMAM-ACCT-STRING: This is just a simple string. Sample account would look like:204531614100103524000FFAMAM-EDITED-ACCT: This field shows the account number in a more friendly format.This format has spaces between each dimension. Sample account would look like:204 E 31 6141 00 103 5 24 000If you simply want to print the full account number, FFAMAM-EDITED-ACCT would work just fine.You can drop this on your report and it would look fine. If you want to group, total, sort, or perform anyother function by a specific account dimension though, you will need a formula. While any of these canbe used for this, FFAMAM-ACCT-STRING would be more complicated due to the need to utilizeLEFT, MID, and RIGHT extensively. The function used here to pull an individual account dimensionfrom FFAMAM-ACCT-DIM or FFAMAM-EDITED-ACCT is SPLIT. An example is shown belowwhere we would be pulling the object code, 4th dimension of the Texas account structure.SPLIT({FFAMAM ACCT MST.FFAMAM-ACCT-DIM},";")[4]
Formatting Account Codes- ContinuedIt should be noted that the account type is always the second position of the account, for every state.FFAMAM-ACCT-DIM and FFAMAM-ACCT-STRING each use a number to signify the account type.The number can be translated as shown on the following page.select split({FFAMAM ACCT MST.FFAMAM-ACCT-DIM},";")[2]case "1":"A"case "2":"L"case "3":"Q"case "4":"R"case "5":"E"Printing Current Lunch StatusPlease note that these instructions apply regardless of whether you license Skyward’s Food Servicesoftware or not. The same table tracks this information for Food Service customers and non FoodService customers alike.Retrieving the lunch status has more to do with grouping on a report than it does with formulas. Thechallenge here is that the table will most likely hold multiple records per student and while there is astart date (FS-TRANSACTION. FS-TRAN-EFFECTIVE-DATE), there is no ending date for theserecords. To pull the most recent record, follow these steps: Group on the student (use a unique value from STUDENT or STUDENT-ENTITY like theSTUDENT-ID or the student key) Place your report fields on the group header line for that new group. This would include the FSTRANSACTION. FS-LUN-CODE-ID field Suppress your detail line so the older records do not show In the Record Sort Expert, sort by FS-TRAN-EFFECTIVE-DATE, in descending orderPrinting Current Entry RecordThis process is not dissimilar to the steps described for retrieving the lunch status. There is an advantagein this table though as there are actual starting and ending records (STUDENT-EW. EW-DATE andSTUDENT-EW. WITHDRAWAL-DATE). With both of these records present, you can set ranges toselect records by date ranges or even check your membership counts vs. your active student counts.To select only currently enrolled students, without regard to the STUDENT-STATUS flag, you coulduse a selection formula like this:{STUDENT ENTITY.ENTITY-ID} "001"and {STUDENT ENTITY.X-DEFAULT-ENTITY} trueand isnull({STUDENT EW.WITHDRAWAL-DATE})If you cannot find your record through a selection formula then you will likely need to address therecords via grouping. To display the most current record: Group on the student (use a unique value from STUDENT or STUDENT-ENTITY like theSTUDENT-ID or the student key) Place your report fields on the group header line for that new group. This would include theSTUDENT-EW. EW-DATE field Suppress your detail line so the older records do not show In the Record Sort Expert, sort by STUDENT-EW. EW-DATE, in descending order
Age CalculationAn individual’s age is one of those values, like a student’s grade level, where it is displayed on thescreen but is not really stored anywhere. To calculate this value, use the formula )/12)Without the TRUNCATE function in use, the value returned will be a fraction. A student with a birthday10 days a way could show as being 13.92 years old. Changing the format would not fix the problemeither as Crystal Reports will automatically round numbers (making the student 14).This formula could be adapted for other uses; a simple list of employees and their length of employmentfor example. Simply replace the birth date value with the hire date.NCLB 2 Data (STUDENT-CLASSIFICATIONS table)Quite a bit of data used for state and federal reporting is stored in a common table named STUDENTCLASSIFICATIONS. Much of the data stored here can be seen by clicking the “NCLB-2” button on theStudent screen. The following is a short list of some of the record types stored in this table: Immigrant Status Homeless LEP Homebound Some transportation records Some Special Ed (everything for non-Special Education customers) Section 504 records IEP records (non-Special Education customers)The key to this table is the field STUDENT-CLASSIFICATIONS. SC-RECORD-TYPE. The typerecord determines how the record will be used by the softwareDue to how these records are stored, it is very common to use a sub-report to pull these records. Thereason for this is that not every student necessarily has a record in this table. If the table is in the mainreport as a LEFT OUTER join then you cannot filter on that table without eliminating the very recordsyou were trying to preserve. This makes a sub-report a very viable option as they are inherently LEFTOUTER joins but you can filter them as tightly as needed. Here are some examples of the formulasinvolved in a fairly typical report which would make use of STUDENT-CLASSIFICATIONS data
NCLB 2 Data- ContinuedFor purposes of example, let’s say that I am a Wisconsin customer who needs to print data for everyactive student but would also like to pull LEP records for those who have a current LEP records. Create a sub-report to pull the piece of information you need The primary table in the sub-report would be STUDENT-CLASSIFICATIONS Link the sub-report from STUDENT-ID in the main report to STUDENTCLASSIFICATIONS.STUDENT-ID but do not link the ENTITY-ID field, even though it showsin the index for STUDENT-CLASSIFICATIONS Build the sub-report with the records you need for the LEP students Create a Record Selection formula similar to the following:{STUDENT CLASSIFICATIONS.STUDENT-ID} {?Pm-STUDENT ENTITY.STUDENT-ID}AND {STUDENT CLASSIFICATIONS.SC-RECORD-TYPE} "WILEP"AND {STUDENT CLASSIFICATIONS.SC-STR-DATE} enter your reporting start dateAND (ISNULL({STUDENT CLASSIFICATIONS.SC-END-DATE})OR {STUDENT CLASSIFICATIONS.SC-END-DATE} enter the ending date for your report)Please keep in mind that the first filter listed above was created automatically when the sub-report wasjoined to the main report. This is the way Crystal Reports ensures that the sub-report only pulls recordsfor the appropriate student. The starting and ending date values are based on your reporting needs. Youcould always control these by a parameter if desired and then enter the values from the main report.Reading Attendance RecordsIn the Skyward Student Management system, there are actually 3 different places to commonly pullabsences from: STUDENT-ATND-DETAIL which holds the detailed absence transactions. STU-ATND-TOT-HST which holds quarterly totals by year. Updated at Year End or by runninga utility, “Update Student Yearly Attendance” (SM/OF/AT/UT/UY in the PaC product). STUDENT-CLASS which holds absences by class. This is also updated through a utility.The process for reading these records is somewhat similar to the earlier procedure for reading accountcodes. Where attendance becomes more complicated is that if you are using the detailed table, theidentifying aspects of the records are inside of the array and there are records for each day of absences(per student). Skyward’s attendance module supports up to 40 periods of attendance. Because of this, theattendance arrays are 40 positions. There is a separate array field for absence reasons and another forabsence types. This means is you want to run a report of students who have more than 5 unexcusedabsences within a date range (not using term start/ stop dates), you will have to read every attendancetransaction within your date range to find out if it should be included.The tables with the totals are a little easier to work with as these values are broken down by schedulingterm. The values from STUDENT-CLASS give you just the total number of absences for the course.The values from the Total History table have separate fields from excused, unexcused, other, and tardy.Attendance Examples on Next Page
Reading Attendance Records- ContinuedHere are some examples of what a formula needs to return and how it would be written. These examplescover all three of the aforementioned tablesTo read 2nd Term Unexcused absence records from the Total History table:Field D-TOT-HST.SATH-UNEXCUSED},“;”)[2]To read 3rd Term absence or tardy records for a particular class:Field LASS.SCHD-TRM-TDY},“;”)[3]To read 5th Period absence Type records from the Detail table (each date has its own record):Field ��)[5]Note: absence records in the REPORT-CARD table are stored just like those in the Total History tableUser-Defined FieldsUser-defined fields (UDFs) are agreat way of holding informationon students and families that isnot normally tracked in the PaCsoftware. In order to use thisinformation in a Crystal report,you need to add the appropriatetable to the report.The first step is to open theU.D.S. builder and find theSystem, Field-ID and Data Type.First, note the system in the topleft corner of the screen. Thenhighlight the field name in thelist and use the scroll bar at thebottom of the list to scroll to the right and display the Field-ID column. Make note of the ID number.Lastly, make note of the Data Type from the Field Properties area on the right side of the screen.Information Continues on the Next Page
User Defined Fields- ContinuedIn this example, the System is Student, the Field-ID is 148 and the Data Type is Character.Here is how what you see/create on the screen is stored in the software:-The TABLE DESRIPTION (or screen name) is stored and defined in the table QUDTBLTABLES. This stores the name of the “Table”/Screen, assigns a unique identifier QUDTBLTABLES.QUDTBL-TABLE-ID, and tracks which system it is tied to (Profile Master, Student,Special Ed, etc) by use of the field QUDTBL-TABLES.QUDSYS-CODE.-The FIELDS area of the screen is stored and defined in the table QUDFLD-FIELDS. Each of theboxes you see/create has its own field in this table. There are also fields for the System-ID andTable-ID.- The data itself is stored in the table QUDDAT-DATA. You will find date fields, char fields, decfields, etc. Which one is populated is determined by how the Field Properties in the User DefinedDictionary are defined. If you created a DATE field then your data will be in QUDDATDATA.QUDDAT-DATE, and so on. There are also fields here for the Table-ID and System-ID.The link back to a pe
Crystal Reports Training Guide- Appendix B Common Formulas & Techniques for Handling Skyward Data Grade Level Calculations There are several different methods. A few of them are shown here. IF-THEN Statement#1- this method returns a nu
