Absence & Cover Analysis

New version out – 1.71 (9th March 2017). See the change list and download below.

Absence & Cover Analysis is a spreadsheet which uses the absence and cover information that is provided by SIMS to produce tables and charts which provide an insight into the data – something that we cannot make SIMS natively provide us with.

After processing the information, you are provided with the following tables and graphs.

• Absences
  • Days Lost by Job Role
  • Employee Absences
  • Employees with more than 10 days absent
  • Number of Absences in the Last 12 Months
  • 12 Month rolling absence summary
  • Number of absences on each day
  • Chart of Number of Absences on each day, with trend line
  • Absences by Reason
  • Chart of Absences by Reason
  • Absences by Reason and Day of Week (to show if all sickness occurs mainly on a Friday or Monday)
  • Chart of Absences by Reason and Day
• Cover
  • Year Groups affected by Cover Lessons
  • Subjects affected by Cover Lessons
  • Matrix of Years and Subjects
  • Chart of Years and Subjects affected by Cover Lessons
  • Number of Supply Teachers by day with number of cover periods provided by that Teacher
  • Cover Type by Date – whether Internal, Supply, or No Cover
  • Internal Staff Cover Lessons taken – for monitoring Rarely Cover
  • Number of Lessons Covered by Reason (New!)

All provided in an easy to use package!

Some images of the output data are below to give you an idea of what you may be able to produce.

A package containing the necessary reports from SIMS and the Excel spreadsheet itself is included below, with some instructions on how to use the spreadsheet.

If you think of something that you would like the spreadsheet to show, which it doesn’t already, let me know. Use the contact form above to send me a message. As long as the data is available in the SIMS reports then it can be included in any of the reports.

Hope this saves a few administrators some time with their number crunching!

Instructions

1. Download the zip file and extract the contents to a folder on your computer.
2. Open SIMS and go to Reports –> Import and import the SIMS Absence Reports.rptdef file from the extracted files.
3. Run the ‘Absences: Absences’ report and save the results in an Excel file.
4. Run the ‘Absences: Classes’ report. You should be asked to enter the Academic Year of the classes to export. Save the results in an Excel file. (If you do not get prompted for the academic year, then you are probably not running the latest version of the reports. Please re-import the reports.)
5. Go to Focus –> School –> Arrange Cover. Open a Cover Day and select Print. Make sure Notice Board Summary, Full List and Include Reason for Absence are selected and click Print. Save the webpage to a folder either with the default .mht extension or a .htm extension. You will need to repeat this for every day that you wish to look at. I found that I could save a whole terms cover lists in around 30 minutes.
6. Open the spreadsheet. You may be prompted to Enable Macros. If you do not then the spreadsheet will not work.
7. Click Absences in the Load Data group on the ribbon. Select the Absences file that you saved in step 3.
8. Click Classes in the Load Data group on the ribbon. Select the Classes file that you saved in step 4.
9. Click Cover in the Load Data group on the ribbon. Select all the Full Notice Board Summary files that you created in step 5. You can import up to 100 days of cover at one time. (May take a while though!)
10. Click Set Periods in the Generate group on the ribbon. Type in all of the periods in your day and choose a period type from column two. Order does not matter as the workbook will sort it itself.
11. Once everything is imported, select Set Dates on the Generate Data group. Choose the dates that you want to analyse. The greater the range, the longer it will take to generate.
12. Once you are happy, click Generate Data.
13. Once generation is complete you can use the Absence Analysis Reports and Cover Analysis Reports buttons to get to a menu of all the available tables and charts.

Once data has been imported, it remains in the workbook.

• Class data is overwritten every time it is imported.
• Absence data is overwritten every time it is imported.
• Cover data is overwritten if the same day has already been imported, else it is appended.

The Ribbon

Following the new Microsoft convention – we have a ribbon! This allows you to access all the functions of the workbook.

• Home – takes you back to the Welcome page.
• Cover – Loads Cover data into the workbook
• Classes – Loads Class data into the workbook
• Absences – Loads Absence data into the workbook
• Set Dates – Specifies the dates to perform the Analysis on
• Generate Data – produces tables and charts on the imported data
• Absence Analysis Reports – a menu of all the absence related tables and charts
• Cover Analysis Reports – a menu of all the cover related tables and charts
• Print Current Worksheet – prints the currently active worksheet
• Print Cover Reports – prints all the cover related tables and charts
• Print Absence Reports – prints all the absence related tables and charts
• Clear Data – removes all the data from the workbook
• Import Previous Version – imports an older version of the workbook
• Exit – closes the workbook

Compatibility & Security

The spreadsheet has been tested and works in Microsoft Office 2007, 2010 and 2013.

I recommend that as soon as any information is imported into the workbook, it is password protected and stored in a suitably secure area.

Updates

1.10

• Generate Data no longer fails when Absences have been recorded which do not have an end date.
• Generate Data ignores all absences which have neither a start nor end date as they are erroneous
• Periods can be defined which are then used to determine Class cover information
• Improved Class lookups when analysing Cover Data
• Pivot tables have had any school specific filters removed from them

1.12

• Fixed Ribbon buttons in Microsoft Excel 2007
• Fixed missing VBA reference in Import Cover

1.21

• Fixed further missing VBA references in Import Cover
• Inserted missing declarations in Import Absences and Import Classes
• Import Cover Days now updates and shows correct information
• Cover – Cover Type report dates are now shown in date order
• Clear Data leaves header rows in place enabling Import to run
• Tables have gridlines
• Import Previous Version command added
• Lessons Covered by Reason report added

1.22

• Multiple selected sheets no longer stop absences and classes from importing

1.25

• Corrected PivotTable datasources
• Stopped tables showing spurious data when no data imported
• Error checking when running Generate Data – checks for data existence first

1.27

• Missing Role Information does not stop import of Absences
• Fixed bug in Import Cover where Cover date not in expected row

1.33

• Added in Status Bar Notifications when importing or processing data
• Significant performance improvements when importing cover, class and absences data, also when running the Generate Data routines
• Import Cover searches for correct data to import solving a problem where the covering member of staff may not have been imported

1.36

• Amended Import Cover to not import Changed Room
• Minor code fixes in Imports
• New Set Working Days option

1.39

• No Cover is now set correctly when running Generate Data
• Removed the 32768 record limit on Cover Data
• Formats Year and Class names properly when using two digit year groups (e.g Year 07)

1.41

• Importing Cover now allows HTML formats as well as MHT and HTM
• Set Dates allows dates to be set up to 3 years into the future (rather than the 2014 hard limit)

1.54

• Absences by Date with Trend Line now shows data
• Print Current Worksheet does not error when print is cancelled
• Print All Absences and Print All Cover now include all of the reports
• Internal Staff Cover Lessons no longer shows external supply staff
• Generate data checks the formatting of every sheet and corrects if necessary
• A variety of other formatting fixes and tidying up

1.55

• New table to show number of periods of absence over the last 12 months

1.59

• Fixed bug when setting pivot table filters
• New report to show a rolling 12 month absence summary
• Updated import of Previous Versions to correctly calculate absence information
• Tidied up formatting of some reports

1.66

• Fixed bug when sorting periods on Generate Data would fail due to workbook protection.
• Cover By Subjects and Years now shows all data labels
• Fixed broken link in the Cover Analysis Reports page
• Fixed issues with importing previous version of workbook where cover data would not be copied
• Updated SIMS Reports to request for Academic Year when exporting classes

1.70

• Fixed issue where Generate Data would tell you that no data was available
• Fixed issue with importing cover sheets that have room changes listed
• Added ability to export to PDF straight from the ribbon

1.71 (9th March 2017)

• Corrected the Absences in Last 12 Months to show Sum of Days and Count of Periods

Upgrading to a New Version

Since 1.20 this is now much easier. To upgrade to a new version, you do not need to import all the data from scratch. You can import the previous saved version of the workbook into the new version of the workbook.

Click the Import Previous Version button in the Manage section of the ribbon, and locate the older version of the workbook. Click OK and you can take advantage of the new features.

If you upgrading to 1.33, then you may need to import your Cover data again.

If you are upgrading to version 1.66 from any previous version, then you will need to update your reports in SIMS. Please delete the previous versions of the reports and re-import the reports.

The Download

Here it is:

Download “Absence & Cover Analysis” AbsencesCoverAnalysis.zip – Downloaded 3434 times – 424.06 KB

Disclaimer

This workbook is provided as is. The data produced by the charts and tables is believed to be correct, but no liability will be taken for incorrect data produced by the workbook.