Result Entry by Spreadsheet User Guide

(1)

Created in version 2007.3.0.1485 1/50

Result Entry by Spreadsheet User Guide

(2)

The Markbook allows you to track and manage Student’s marks in a freeform spreadsheet environment. In creating a Markbook Student’s marks can be entered, modified and manipulated using formulas and formatting similar to Excel. The Markbook can be saved independently from the database and exported to Excel to allow marking to be completed in a familiar environment.

Collation

The Collated Markbook allows Markbooks to be shared between teachers in the same Year Level and Subject. This feature should only be accessed by Curriculum Coordinators.

Once the necessary Curriculum data has been entered, Teachers can easily enter their results via Result Entry and Markbook. This information will then be available in other areas of PCSchool, for example student profiles or reporting.

The Result Entry by Spreadsheet view has several features, all of them appertaining to entering Results for Students against Subjects.

(4)

Result Entry

Introduction

Once the necessary Curriculum data has been entered, Teachers can easily enter their results via Result Entry and Markbook. This information will then be available in other areas of PCSchool, for example student profiles or reporting.

The Result Entry by Spreadsheet view has several features, all of them appertaining to entering Results for Students against Subjects.

Prerequisites;

• ••

• Prior to using this Module it is expected that all Student and Curriculum information has been

entered.

• ••

• XML’s specific to the schools Assessment Reporting need to be created and made available

for selection.

• ••

• Optional; A default XML can be set •

••

• XML’s that are to be used should be made readily available.

XML Availability

Path: Assessment → Result Entry by Spread Sheet

IMPORTANT: This should only be done by your IT person or System Administrator. XML’s are used in result entry by spreadsheet, not Markbooks.

XML’s can be made available for selection rather than having to browse for them, to do this the code in the XML will need to be changed. You can set up one report as a Default.

1. Reports can be set up to appear in the drop down list.

2. If a report is not available you can browse for it.

(5)

Created in version 2007.3.0.1485 5/50 Add the necessary line of code to your XML.

1. Go to PCSchool/Documents on the server.

2. Find the XML you wish to make changes to and make a copy. 3. Right click on the

4. Styles are the format types that will be available with this report.

5.

Enter the line of code as shown. Use either True or Default.

• • •

• True: This will make the XML available in the list. •

• •

• Default: This will make the XML appear automatically when first entering Result

Entry by Spreadsheet. If your teachers will be using a particular XML for result entry you would set this as the Default XML.

(6)

Result Entry

XML Reports are created for the Schools and the results will be available when printing Assessment Reports. Curriculum data must be entered to use these reports.

Overview

1. Check you are in the correct Period ID.

2. Select a Teacher.

3. Select a Subject/Class. Only the Subjects and Classes taught by the selected Teacher will

be available for selection.

4. If a default XML has been set up it will be available, otherwise select from the drop down list.

If a report is not available you can browse for it.

5. Click to Browse for an XML.

6. The Format will default from the XML. Depending upon the XML different formats may be

available.

7. Check the box if Departed Students are to be included.

8. Read further to find out about Exporting the file.

9. When the preferences have been selected click Create Spreadsheet to populate the fields.

(7)

Created in version 2007.3.0.1485 7/50 Entering Results

These reports are examples only; each School will have their own reports and they may look very different to the examples show here.

Class Student Page XML

Students Results can be entered once the Spreadsheet has been Created. This will create a page per Student Spreadsheet. This XML is very basic.

1. Select a Student.

2. Enter Results as per your school requirements, several of which are entered in Curriculum

Control Files.

3. Click Submit to Database to save the entries.

(8)

This is an example of another XML with the format of Class Student Page with tick boxes. This Report has one Student per page. The same report is below created in Class Tree Format.

Class Tree XML

Students Results can be entered once the Spreadsheet has been created. Students will be viewed on one page following each other. This is the same report as above with Class Tree selected.

• ••

(9)

Created in version 2007.3.0.1485 9/50 This XML has Result options available from a drop down list.

1. Select the Result from the drop down list.

2. Click Comment and select from the Comment bank or enter a Comment.

3. Click Submit to Database to save the entries.

(10)

Example

Class Break Page

Students Results can be entered once the Spreadsheet has been Created.

1. Select the Result from the drop down list.

2. Enter a Percentage if known.

3. Click Comment and select from the Comment bank or enter a Comment.

(11)

Other Special Reports

(12)

For the Area Result Field selection to function correctly the specific report being printed must have been written to be sensitive to this control.

(13)

Export the Spreadsheet

The Spreadsheet can be exported in various formats, when importing the spreadsheet back this is done through Markbook as in CSV format.

1. Select the correct Period ID.

3. Select a Subject.

4. Select a Report Style.

5. Click Create Spreadsheet.

6. Click Export.

The Spreadsheet can be Exported;

• ••

• As a CSV •

••

• To a Markbook •

••

(14)

Export to CSV

Once the file is exported as a CSV it can be opened in Excel, data entry can be completed and the file can be imported back into PCSchool.

1. Select Export to CSV.

2. Make sure you save the file where you can find it.

3. Enter the File name. The Save as Type should be CSV Files.

(15)

Created in version 2007.3.0.1485 15/50 Find the file, open it and make any necessary entries.

Export to Markbook

1. Select Export to Markbook.

2. Click OK.

3. Go the Markbook tab.

(16)

Export to Excel

Once the file is exported as in Excel format, data entry can be completed and the file can be imported back into PCSchool.

1. Select Export to Excel.

2. Make sure you save the file where you can find it.

3. Enter the File name. The Save as Type should be CSV Files.

4. Click Save.

(17)

Import CSV to Markbook

Import the CSV File back into PCSchool.

Open the Wizard.

1. Click Import/Export.

(18)

Select the File

1. Check that CSV File is selected.

2. Click Select File.

3. Find the File.

4. Click Open.

(19)

Created in version 2007.3.0.1485 19/50 Identify the Students.

1. Check if the file has a Header Row.

2. Select Student#.

3. Make sure the Student# is highlighted.

4. Click Next. Select an option.

1. Select an option.

(20)

(21)

Created in version 2007.3.0.1485 21/50 Save the Markbook

Path: System Functions → Curriculum Control Files → Subject Codes

In order to Export to Result Entry you will need the Subject to be tagged this will be either as a Personal Markbook, Collated Markbook or both.

1. View the Markbook.

2. Give the Markbook a name.

3. Click Markbook.

4.

Select a Save option, this will vary depending upon your security;

• • •

• No Synch Save will save the Results to the Markbook. •

• •

(22)

Export the Markbook to Result Entry.

Once the Markbook is saved it can be exported to Result Entry.

1. View the Markbook.

2. Give the Markbook a name.

3. Click Markbook.

4. Select a Save option, this will vary depending upon the settings in the Subject.

The Markbook data will be exported to the Result Entry spreadsheet in the format determined by the XML selected and the CSV information.

(23)

Markbook

Prerequisites

Prerequisites required before using the Markbooks are;

• Security

• Subject Information • Secure User ID# • Enable Sync Save

• Setup Alternate Label for Result Fields • Delete All Markbooks

• Assign Markbooks with a New Subject Period ID

Security

Path: Utilities → User Security Setup → Group Profiles

Three Securable items must be added to User Profiles through the User Security Settings by the System Administrator.

(24)

Subject Information

Path: System Functions → Curriculum Control Files → Subject Codes

Information needs to be added to the Subject Codes to allow access to Collated Markbooks by Department Heads and Subject Coordinators. You can also allow other staff to access them.

1. Select the Subject Co-ordinators (Department Heads) name to allow them access to

Collated Markbooks for the selected subject.

2.

To Allow other Staff Members access to the Collated Markbook for this subject add their Timetable Code (separated by pipes “|”) to the Access Collating Markbooks field in Subject Supplement.

(25)

Created in version 2007.3.0.1485 25/50 Secure User ID#

Path: Administration → Staff View → Work tab

Select their Secure User ID#.

Enable Sync Save

Path: System Functions → Curriculum Control Files → Subject Codes

Results entered into the Markbook can be saved directly to the student without having to export them from the Markbook into result entry, and vice versa.

Teachers can be allowed to enter and save results in Markbooks, but not commit these to the student result set until the Subject Coordinator has checked them.

The ability to allow Markbook results to be synced with the student result set can be set up as follows.

1.

Enable Result Sync in Personal Markbooks;

• • •

• Tick to allow teachers to sync save results entered in their Personal Markbook to

the student result set.

• • •

• Untick if you do not wish results in Personal Markbooks to be synched.

2.

Enable Result Sync in Collating Markbooks;

• Tick to allow teachers to sync save the results entered in a Collated Markbook to

the student result set.

(26)

Alternate Label for Result Fields

PCSchool has the ability to hold six different results (collectively called the result set) for each Area of Assessment). These are Grade, Result, Result2, Result3, Result4 and Result5. Each of these result fields can be used for a different period or term of assessment reporting.

Most commonly these fields would be used as follows:

• Result = Term 1 / Mid Semester 1 • Result2 = Term 2 / Semester 1 • Result3 = Term 3 / Mid Semester 2 • Grade = Term 4 / Final Grade

These fields can be renamed by the System Administrator to reflect the result they hold.

Rename the Fields

Path: Utilities → User Security Setup → Global Protect

(27)

NOTE: The new Alternate Label will only be seen by users who do not have SYSADMIN rights to PCSchool.

Delete All Markbooks from a Subject Period

Path: System Functions → Curriculum Control Files → Subject Period ID

1. Select the Subject Period you wish to delete Markbooks from.

2. Select Delete All Markbooks in Selected Period.

3. Make sure you are deleting the Markbooks from the correct period.

(28)

Assign Markbooks with a New Subject Period ID

Path: Subjects → Copy Student Subjects to New Period

When copying Students Subjects to new Period you have the option of copying existing Markbooks into the new Subject Period.

Copying Markbooks using this routine will not create ‘blank’ Markbooks;

• But will assign a new period ID to the existing Markbooks

• Saved information (results, comments etc) will be retained in the Markbook.

When copying Students Subjects to a new Period after all the filters have been added you can also select the option Assign new Period to Markbook.

(29)

Markbook

Path: Assessment → Result Entry by Spreadsheet → Markbook

The Markbook allows entry and viewing of student assessment results in a column or spreadsheet format as well as having an Import Export Option.

The Result Entry by Spreadsheet will perform differently in each mode. Teachers will use the Result Entry view, while coordinators will use the Collation section to view and edit collated Markbooks.

Teachers Personal Markbooks are those created by individual teachers and are not accessible by other staff members. Teachers may create their own personal Markbooks to hold results for tests, exams, essays etc that students are assessed on through the term or the year for their own personal reference.

Import/Export

Within the Import/Export view you have the option to;

• Import file to Markbook • Export to Results • Export to Excel

(30)

Import File to Markbook

Path: Assessment → Result Entry by Spreadsheet → Markbook

Result Entry

2. Select the asTTle Subject.

3. Go to the Markbook tab and click Import/Export.

(31)

Created in version 2007.3.0.1485 31/50 At the Welcome window, click Next.

File Selection.

1.

Select the file type from the drop down list;

• CSV File or • asTTle File

2. Click Select File.

3. Select the asTTle file.

4. The file name will display.

5. Click Open.

(32)

Select the Unique Identifier.

1. Check the Unique Field of Student# is selected.

2. Highlight Student Master ID.

(33)

Created in version 2007.3.0.1485 33/50 Data Import Rules will default to ‘Create a New Markbook with this Data’, can edit; click Next.

(34)

Details from the file will be imported into PCSchool.

Save the Markbook

If this is a new Markbook once the data is imported you will need to save the Markbook. Your save options will depend upon the settings in the Subject.

(35)

Teachers Personal Markbooks

Path: Assessment → Result Entry by Spread Sheet → Markbook tab

Personal Markbooks are those created by individual teachers and are not accessible by other staff members. Teachers may create Personal Markbooks to hold results for tests, exams, essays etc that students are assessed on through the term or the year for their own personal reference.

Create a new Personal Markbook

1. Select the correct Period ID.

2. Select the Teacher Code.

3. Select the Subject/Class.

4. Go to the Markbook tab.

5. Click Markbook.

6. Click New.

7. A list of students studying this subject (with this teacher in this period) will populate in

(36)

Add Columns to your Markbook.

1. Click on the New button. This will add a column.

2.

Using the dropdown list select the column type:

• ••

• String – both alpha and numeric or a mixture will be used when entering

student results.

• ••

• Numeric – only numbers will be used when entering student results. •

••

• Comment – comments will be used in this column, either typed in full or

using comment codes.

• ••

• Data – denotes the column will hold static data such as Date of Birth or

Ethnicity pulled through from the student file.

3. Type in the column name or description.

4. Name the Markbook once you have added your columns.

5. Go to Markbook and Save. Depending on your security settings you may only have the

(37)

Created in version 2007.3.0.1485 37/50 Opening, Entering Data, Saving

Once the Markbook has been created you can open it and enter data/results in, as the information becomes available.

1. Select the correct Subject Period ID.

2. Select the Teacher Code.

3. Select the Subject/Class.

4. Select the Markbook from those available under Sheet Name.

5. Go to Markbook – Open.

6. Enter student data / results against the students in the table.

7. Go to Markbook – No Sync Save or Quick Save to save the results to the Markbook.

Collated Markbook

The Collated Markbook allows Markbooks to be shared between staff Teaching the same Year Level and Subject. This feature should only be accessed by Curriculum Coordinators.

The Result Entry by Spreadsheet will perform differently in each mode. Teachers will use the Result Entry view, while coordinators will use the Collation section to view and edit collated Markbooks.

(38)

Create a Collated Markbook

Path: System Functions → Curriculum Control Files → Areas of Assessment

Collated Markbooks should only be created by the Curriculum Co-ordinator or Heads of Departments. The Markbooks are created in Setup Default Areas of Assessment.

Select the Subject

1. Select the Subject No.

2. Tag the Year Level you wish to create a Markbook for (multiple year levels can be done

at once).

3. Select Allocate Tagged to Markbook. This will bring up the Auto Build of Master

(39)

Created in version 2007.3.0.1485 39/50 Auto Build Master Collating Markbook

Path: Scholastic → Curriculum Control Files → Areas of Assessment

The Auto Build Master Collating Markbook view allows you to select the various items you wish to include when building the collated Markbook.

1. Select the Subject Period ID you wish to create the Markbook in.

2. Select Items to include in the Markbook, check the codes in the Areas of Assessment.

3. Select the Grade or Result field to include in the Markbook.

4.

Validation codes can be entered now, if not available these can be created, to create them;

• • •

• Enter an Alpha or Numeric digit in the bottom box and click +. •

• •

• The codes can also be entered in the Markbook.

5. Select the Column Heading and Column Format to be used in the Markbook.

6. Tick if Subject 999999 is to be included in the Markbook.

7. Currently Markbooks will only replace results in your student result set. The option Add

or Adjust Results is not available.

8. Will populate the Markbook with Students already attached to the Subject.

9.

File Name Template will default to Master %1, this will name the Markbook as Master and add the information from the Subject. Leave as default or enter a different name for the Markbook.

10. Select Begin to create the Markbook. A Markbooks Created window will appear listing

(40)

Working with a Collated Markbook in Collated Mode

Curriculum and Subject Co-ordinators should be the only staff to open Markbooks in Collation Mode. This will allow access to all students in a year level, studying the selected subject, to be viewed at once.

Path: Assessment → Result Entry by Spread Sheet → Collation

Opening the Collated Markbook, when the Markbook is created the appearance will depend upon your selection when creating the Markbook.

2. Select the Subject.

3. Select the Year Level.

4. Select Markbook tab.

5. Select the Markbook / Sheet Name.

6. Click Markbook then Open.

7.

When the Markbook is opened students studying the subject in the selected year level will be displayed alphabetically with the subject Teachers Timetable code and their form class.

(41)

Created in version 2007.3.0.1485 41/50 Markbook Management

Once the Collaged Markbook has been created through the Curriculum Control Files, you can modify it (edit, delete or add columns) through Markbook Management.

1. Go to Markbook, select Manage Markbooks

2. This will bring up the Markbook Management view displaying Markbooks created for the

displayed subject.

3. Select the Markbook you wish view or modify.

4. Heading: displays the name of the Markbook column. These can be renamed in this

view.

5. Type: displays the data type of the column. These can be changed in this view.

6. Validation: displays the Validation for the column. These codes can be edited in this

view.

7. Synch Field: displays the synchronise path for the column.

8.

Synch?: displays the saving option for the column.

• • •

• Display Only; this would not be used for results. •

• •

• Not Synchronised: select if you wish the data in this column to never be

synchronised (saved) to Students results.

• • •

• Synchronised: select if you wish the data in this column to be synchronised

only when you select to do a Full Save of the Markbook.

• • •

• Synchronised Off: allows you to turn the Synchronise on and off.

9. Columns can be moved up or down, highlight a line and click the arrows.

10. Select Save Column Settings to save any changes made to your Markbook.

11. Exit the Markbook Management View and go to Markbook → Open to display the

(42)

Entering Results into a Collated Markbook

1. Once you open the Markbook you can enter student results.

2. Invalid data will be highlighted in RED. This means that the data entered in this field

does not fit the Validation Codes for this column.

3.

Missing Areas of Assessment will be highlighted in ORANGE. This means that these students have not been allocated this Area of Assessment and this will need to be done through your Curriculum Control Files.

(43)

Saving in a Collated Markbook

1. Once the correct data has been entered you can now save the Markbook. Go to _{Markbook to view your save options.} 2. No Sync Save: Saves the data to the Markbook only, without impacting the student

result set.

3. Synchronised saving will write the results entered in the Markbook to the student result

(44)

Working with a Collated Markbook in Result Entry Mode

Once the Master Collated Markbook has been created by the Curriculum Co-ordinator teachers will be able to use the Master Markbook to view and enter student results for their class through the Result Entry – Markbook tab.

Path: Assessment → Result Entry by Spread Sheet → Result Entry tab → Markbooktab

Opening the Collated Markbook in Result Entry Mode

2. Select the Teacher.

3. Select the Subject / Class.

4. Select the Markbook / Sheet Name.

5. Go to Markbook → Open.

6. When the Markbook is opened Students studying the selected subject with the selected

(45)

Entering Results the Collated Markbook in Result Entry Mode

1. Open the Markbook and enter student results.

2. Invalid data will be highlighted in RED. This means that the data entered in this field

does not fit the Validation Codes for this column.

3.

Missing Areas of Assessment will be highlighted in ORANGE. This means that these students have not been allocated this Area of Assessment and this will need to be done through your Curriculum Control Files.

4. You will be unable to save the Markbook until you have corrected the invalid data.

Saving a Collated Markbook in Result Entry Mode

1. Once the correct data has been entered you can now save the Markbook. Go to

Markbook to view your save options.

2.

If ‘Enable Result Sync in Collating Markbook’ has NOT been ticked in the Curriculum Control Files teachers will only be able to do a No Sync Save. This will save the results in the Markbook, but not write them against the student results.

3.

If ‘Enable Result Sync in Collating Markbook’ has been ticked in the Curriculum Control Files teachers will have Quick Save, Full Save and 2Way Sync Save available and results will be saved in the Markbook as well as the student result set.

(46)

Writing/Saving the Results to the Student Result Set

1. Open the Master Markbook in the Collation Tab.

2. Results entered by teachers through the result entry tab will be displayed.

(47)

Inserting Formulas into Spreadsheets

Before inserting a formula the field must be set to Numeric.

1. Right click a cell and select Set to Numeric.

(48)

Formula Wizard

1. Select the Required Formula.

2. Some Formula options have a brief description.

3.

When a Formula has been selected the expression will be automatically inserted. Enter the fields the function relates to. For example: AVERAGE () is automatically inserted, the fields which are to be averaged are (C4:F4).

Formula Operators

Operator Description Literal (Samples) Cell Reference

+ Addition 2 + 2 B1 + A2

- Subtraction 2 – 1 B1 – A1

* Multiplication 2 * 3 BI * C1

/ Division 2 / 3 B1 / C1

: Create a Range Average cells from A1 to D1 A1:D1

# Wild Card Relative reference of all rows _{from A1 to A?} A#

, Expression (Argument)

delimiter.

Sum (cell 1 and cell 2 and cell

3) (A1,A2,A3,)

Formula Descriptions

AVERAGE Finds the Average of the values within a range of cells MAX Returns the Maximum value within a range of cells. MEDIAN Returns the Centre most value of a range of values.

(49)

Created in version 2007.3.0.1485 49/50 Sample Formulas

SUM Step 1

Sum row values and display row total in Column F for all rows.

1. Right click in cell F3.

2. Drag the mouse to cell F12.

Step 2

Right click and select Insert Formula.

1. Select Sum.

2. View the description of Sum.

3.

In Formula to be inserted enter the following;

• • •

• Sum (C3,D#,E#) •

• •

• The () begin and end the formula. •

• •

• The # is used as a wildcard and will be replaced with the relevant row reference. •

• •

• The comma , separates the argument.

The formula could also be written as;

• • • • Sum(C#:E#) • • •

(50)

Step 3

Result Entry by Spreadsheet User Guide