Weird & Wonderful IT

New version out – 1.36. 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 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 brief 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.

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 and save the results in an Excel file.
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 sheet.

• 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

Compatability & Security

The spreadsheet has been tested and works on both Microsoft Office 2007 and 2010.

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

Updates

1.10 – Fixed the following:

• 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 no 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

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.

The Download

Here it is:

Absence & Cover Analysis (619)

Disclaimer

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