Computed Student Fields

Computed Student Fields allow districts to create their own ways of collecting, categorizing, and communicating student demographic data. Computed Student Fields can easily be created by anyone in the district with a knowledge of writing SQL program queries; these queries will correlate with stored data, and appear to end-users – administrators, teachers, and/or staff – in the selected student demographics tabs on Student Info screens. These Computed Student Fields will also be available options for analysis when running Advanced Reports and/or for communication via Print Letters & Send Email

Computed Student Fields

Computed Student Fields is a flexible feature that allows districts (through their programmers’ queries) to define their own data fields, so that any Focus user with permission-access can utilize custom-created data fields for tailored data analysis and communication.

1. From the Students menu, click Computed Student Fields.

2. The categories in the left column correlate with the student demographics tabs found on the Student Info screen. Click the Category for which a new data field is to be created.

Computed Student Fields

Focus will automatically display and validate existing queries within the selected category. A green checkmark along with the "All queries are validated" message displays when they are validated.

3. Click Add column to create a correlating header for the new query (an output column). Include a separate column for each alias in the Select statement.

4. In the Columns section, enter the title of the output column in the first text box; the text entered here displays for the field on the applicable Student Info tab.

5. In the Column text box enter the alias given to the output data in the query. The column field and the alias used in the query must be exactly the same. Note: These aliases are case sensitive.

6. Select Text or Check as the output data option for the student computed field.

For Check student computed fields, the output 'Y' should be used for the green check mark and the output 'X' should be used for the red X in the query.

The immunization compliance check box is an example of a check student computed field. An example query template for this is: Case when...then 'Y' else 'X.'

7. To add a new query, click the Add Query button in the Queries section.

8. To add a New Query, click the corresponding link and title the query. The title will be displayed in field listing, such as Advanced Reports or Print Letters & Send Email.

9. Once titled, enter the query in the provided text box.

Before creating student computed fields, queries should be written in run query to ensure the query is valid and accurate. To access run query, append /runquery.php to your Focus URL; for example, demo.focusschoolsoftware.com/runquery.php.

10. Click Add Query to save the new query.

11. Click Remove this query to remove the displayed query.

12. Once the query has been added, click Save and Validate.

Upon clicking Save and Validate, you will receive a validated message as well as a Performance Report.

13. Click the Import button to import code for computed student fields from other sites.

a. Paste your exported data in the text box provided and click Import.

b. Click Close to cancel the import.

14. Click the Export button to export the code for the computed student fields to be shared with another site.

a. Copy the query from the text box to import to another site.

b. Click Close once text has been copied to your computer clipboard.

15. Click the Help link for an example of placeholders (aliases).

Example of a Student Computed Field

For the Racial Category student computed field, only one column was needed.

For the example query, the field IDs have been given below.  They may differ for your school district.

SELECT

(CASE

WHEN custom_100000105 = 'Hispanic/Latino'

THEN 'Hispanic/Latino’

WHEN custom_100000100 = 'Yes' AND custom_100000101 = 'No' AND custom_100000102 = 'No’ AND custom_100000103 = 'No' AND custom_100000104 = 'No’

THEN 'American Indian or Alaska Native'

WHEN custom_100000100 = 'No' AND custom_100000101 = 'Yes' AND custom_100000102 = 'No’ AND custom_100000103 = 'No' AND custom_100000104 = 'No'

THEN 'Asian'

WHEN custom_100000100 = 'No' AND custom_100000101 = 'No' AND custom_100000102 = 'Yes’ AND custom_100000103 = 'No' AND custom_100000104 = 'No'

THEN 'Black or African American'

WHEN custom_100000100 = 'No' AND custom_100000101 = 'No' AND custom_100000102 = 'No’ AND custom_100000103 = 'Yes' AND custom_100000104 = 'No’

THEN 'Native Hawaiian/Pacific Islander'

WHEN custom_100000100 = 'No' AND custom_100000101 = 'No' AND custom_100000102 = 'No’ AND custom_100000103 = 'No' AND custom_100000104 = 'Yes'

THEN 'White'

ELSE 'MultiRacial'

END)

AS racial_category

FROM

students s,

student_enrollment se

WHERE

s.student_id = se.student_id

AND

s.student_id = {STUDENT_ID}

se.syear = {SYEAR}

Notice the output alias in the SELECT statement (AS racial_category) matched exactly with the column field in the column display.

xx.student_id = {STUDENT_ID}

The line above MUST be included in the WHERE statement of the query.  The xx represents the alias given to the students table in the query.

When viewing a computed student field, this line ensures that the query is ran for the specific student being viewed.

Student Computed Fields can be accessed when using the following features in Focus: More Search Options, Advanced Reports, and Print Letters & Send Email.