This guide details the process of creating a data export to show all employees alongside their working pattern.
In the Admin Tool, click Import/Export Data.
Click Create an export definition.
Click Select the data to export.
Change the database to SelectHR.
Log in.
Copy the below SQL:
Select Top 100 Percent Pattern.Details.[Pattern Name], Pattern.Days.[Pattern Number], Pattern.Days.Week, Pattern.Schedules.[Schedule Name] As Sunday, Schedules1.[Schedule Name] As Monday, Schedules2.[Schedule Name] As Tuesday, Schedules3.[Schedule Name] As Wednesday, Schedules4.[Schedule Name] As Thursday, Schedules5.[Schedule Name] As Friday, Schedules6.[Schedule Name] As Saturday, Person.Snapshot.[Person Number], Person.Snapshot.[First Name], Person.Snapshot.Surname, Person.Snapshot.[Effective Status] From Pattern.Details Inner Join Pattern.Days On Pattern.Details.[Pattern Number] = Pattern.Days.[Pattern Number] Inner Join Pattern.Schedules On Pattern.Schedules.[Schedule Number] = Pattern.Days.[Sun Schedule Number] Inner Join Pattern.Schedules Schedules1 On Schedules1.[Schedule Number] = Pattern.Days.[Mon Schedule Number] Inner Join Pattern.Schedules Schedules2 On Schedules2.[Schedule Number] = Pattern.Days.[Tue Schedule Number] Inner Join Pattern.Schedules Schedules3 On Schedules3.[Schedule Number] = Pattern.Days.[Wed Schedule Number] Inner Join Pattern.Schedules Schedules5 On Schedules5.[Schedule Number] = Pattern.Days.[Fri Schedule Number] Inner Join Pattern.Schedules Schedules4 On Pattern.Days.[Thu Schedule Number] = Schedules4.[Schedule Number] Inner Join Pattern.Schedules Schedules6 On Pattern.Days.[Sat Schedule Number] = Schedules6.[Schedule Number] Inner Join Person.Snapshot On Pattern.Details.[Pattern Number] = Person.Snapshot.[Pattern Number] Group By All Pattern.Details.[Pattern Name], Pattern.Days.[Pattern Number], Pattern.Days.Week, Pattern.Schedules.[Schedule Name], Schedules1.[Schedule Name], Schedules2.[Schedule Name], Schedules3.[Schedule Name], Schedules4.[Schedule Name], Schedules5.[Schedule Name], Schedules6.[Schedule Name], Person.Snapshot.[Person Number], Person.Snapshot.[First Name], Person.Snapshot.Surname, Person.Snapshot.[Effective Status] Order By Person.Snapshot.Surname, Pattern.Days.Week
In the top left of the data export window, click Options, Paste SQL from clipboard.
Click Data.
Once the data export is created, a Primary Key must be selected. Click the Key Field icon and select the appropriate Key. The Key Field must be represented by a number (i.e. Person Number, Absence Number etc.). When the export is finished it can be saved for future use.
Click OK to continue the export. The Data Export Wizard windows appears.
In the Definition name field, enter a full name. This will be the name of the report saved in the Admin Tool, which can be edited in the future, if needed. There is more information available about how to edit an existing report.
Click Browse to choose a location for the report exported to Excel.
In the File name field, enter a name. This will be the name of the Excel document once exported on the local machine.
Use the File Format drop-down and select Microsoft Excel 97-2003.
Click Next. A preview of the data appears.
In the Column section under the columns preview, click a column header to select a column and set export options.
Click Next.
Click the Open file after export check box. The document will then open once the export is completed.
Click Finish. The Export will now appear in the External Data Transfers list and can be run again.