This document will outline the creation of a staff absences export for the current year.   SIMON support will create a SIMON read-only account to allow users to access the SIMON data.  This account is called “simonro” and resides on the actual SIMON database server.

Step 1: Open Excel

Step 2: From the “From Other Sources” menu select “From SQL Server”

Step 3: Enter Server name (supplied by IT support) and the account username and password, then click next.

Step 4: Select Database and Table with following settings and then click next.

Step 4: Complete the details and click finish.

Step 5: Click on the properties button on the bottom left of the box and complete the information on the usage tab as illustrated.

Step 6: Click on the properties on the Definition tab and compete the data as illustrated.

This is the text required for the Command text:

SELECT     Community.UID, Community.Surname, Community.Preferred, LuStaffCategory.CategoryDescription AS StaffCategory, ISNULL(LuStaffAbsenceReasons.CategoryDescription,

                      '*** Unknown Category ***') AS CategoryDescription, StaffNotifiedAbsences.StartTime, StaffNotifiedAbsences.EndTime

FROM         StaffNotifiedAbsences INNER JOIN

                      Community ON StaffNotifiedAbsences.UID = Community.UID INNER JOIN

                      Staff ON StaffNotifiedAbsences.UID = Staff.UID INNER JOIN

                      LuStaffCategory ON Staff.StaffCategory = LuStaffCategory.CategoryCode LEFT OUTER JOIN

                      LuStaffAbsenceReasons ON StaffNotifiedAbsences.AbsenceCategory = LuStaffAbsenceReasons.CategoryCode

WHERE     (YEAR(StaffNotifiedAbsences.StartTime) = YEAR(GETDATE())) AND (YEAR(StaffNotifiedAbsences.EndTime) = YEAR(GETDATE()))

ORDER BY Community.Surname, Community.Preferred, StaffNotifiedAbsences.StartTime

Step 6: Click on the “OK” button, answer yes to any questions about changes to the workbook. And the spreadsheet will then define where the data will be placed, just click on the “OK” button to finish.

Step 7:  Save file.  Each time file is opened the data will be refreshed.