This article aims to explain how to export a dataset to a broadsheet to be used in a mail merge for KS4 or KS5 Results.
Exporting to Broadsheet
All users can export a broadsheet of students’ grades, as well as SPI / L3VA scores (depending on Key Stage) or points scores from REPORTS > select KS3/4 or KS5 > select the cohort and dataset report and then Broadsheet Export.
Then, select from the following options:
- School Grades Broadsheet (KS3/4 only) – Only includes grades eligible to count towards school performance measures (e.g. discounted grades, unapproved qualifications are not included etc.).
- Student Grades Broadsheet (KS3/4 and KS5) – Includes all grades uploaded into that data set / assessment.
- SPI Broadsheet (KS3/4 Only) - All SPI scores for each student in each qualification where they have a grade uploaded.
- Value Added Broadsheet (KS5 Only) - All L3VA scores for each student in each qualification where they have a grade uploaded.
- Student Points Broadsheet (KS3/4 and KS5) - Includes points scores of all grades uploaded into that data set / assessment.
As an Admin or member of staff with access to DATA, you can also export a broadsheet of school or student grades, depending on the key stage as well as SPI/VA by clicking DATA > select KS, and cohort > GRADES > Grades Management > Report Exports > select Dataset.
Then, click Export next to your chosen export (the report needs to be published for this to become available). You can read and look through the table in the screenshot below for more information on each export.
This will export the published grades into Excel in a broadsheet format, with the students shown down the left, qualification names shown across the top and the grades scattered within the middle. This data can then be used with a mail merge to fill in students’ grades into individual documents for Results Day.
Setting up a Mail Merge
Step 1 / Saving your Broadsheet
To set up a mail merge with the exported broadsheet spreadsheet you’d need to first open the broadsheet file and save it as an Excel Workbook. To do this open the Excel file and go to FILE > Save As > Browse, then select ‘Excel Workbook’ from the ‘Save as type:’ dropdown.
Step 2 / Creating your template and linking to your broadsheet
Next open a Word document and create the template for your mail merge where the fields from your broadsheet export can be entered – below is an example.
Then go to the MAILINGS tab and click Select Recipients > Use an Existing List. Then select your saved Broadsheet Excel Worksheet and, in the next window that opens, select the sheet within your Workbook that you’d like to pull the data from. If no new sheets were added to your Broadsheet Export, then there should only be one sheet to select here. Then click ‘OK’.
Step 3 / Inserting the fields from your broadsheet
Now that your broadsheet has been linked to be used in the mail merge you can click Insert Merge Field and select the field you’d like to insert.
For example, the placement of ‘«Stu_Name»’ indicates that this merge field has been
inserted here in the document, and that this field from your broadsheet should appear
here.
Then in the MAILINGS tab, click Preview Results and you can check if your mail merge has been set up correctly.
Step 4 / Making final edits and finishing your mail merge
Finally, click Finish & Merge, followed by Edit Individual Documents. Then in the window that appears select ‘All’ and click 'OK'. This copies all of the Mail Merge records to one document where you can edit any of the individual records. For example, if you had one student who did not have a grade in PE, you could scroll down to this student and delete the row for PE in the table.
When you’ve finished making individual edits, you can save or print these as you wish.
Advanced Mail Merge Options
If you wanted to create a more complex mail merge document with more information in, you could open your Broadsheet Export file in Excel and use a lookup to add fields from your Student data, or from any other file into this – like the example below, where classes, KS2 and select filter data has been added in as fields within this file.
If you then save this more complex file and continue with Step 2 onwards of the Mail Merge you can end up with a more complex template and as such more complex mail merge records, as shown below.
Thanks for reading. If you have any questions, we recommend contacting your Sisra Administrator, or take a look at our other articles in HELP.