Not many of you would use Open Wave ShiftTrack Plus rostering and workforce palnning software but if you work in a call center and are responsible for rostering or reporting and would like to extract some data directly from ShiftTrack Plus tables to an Excel workbook you will find this article handy.
The version I am using is 5.5.2.0 this is not the latest version but the logic is the same for all versions I would imagine.
ShiftTrack Plus uses a SQL Express Server database to store all its information. The version I am using has two separate databases; one is called shifttrack and the other shifttrack_plus. There are more than one method to get to this data and I will demonstrate one of them in this article.
To get access to this data you will need to set reference from your Excel VBA editor to the following Objects. You do this via the Tool/references menu in the VBA editor:
1. ShiftTrack Data Connection
2. Open Wave Interop Library
3. Microsoft ActiveX Data Objects 2.8 Library
First lets get into the shittrack database and get all the leave applications from the [leave] table and while we are at it we will get the employees names from the personnel table by performing and left join – this is because the leave details table only contains the employee ID and not the full names.
So the code to do this:
Sub getSTLeave()
wb = "23/12/2013" ‘ the week beginning date for the roster we are interested in
ThisWorkbook.Sheets(1).Range("a2:m65536").Cells.ClearContents
Set STC = ComWrapper.SupportObject.OpenSTDatabase
mySQL = "select t1.* , t2.surname, t2.firstname from leave t1 left join personnel t2 on t1.payroll = t2.payroll where [start] between '"
mySQL = mySQL + Format(wb, "yyyy-mm-dd") + "' and '" + Format(CDate(wb) + 6, "yyyy-mm-dd 23:59") + "' order by 1"
Set rs = New ADODB.Recordset
rs.Open mySQL, STC.Db
ThisWorkbook.Sheets(1).Range("a2").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
After running the above subroutine you should see the data on sheet 1 of your workbook.
It is that simple, and you can use any SQL statement to get data from any other tables.
To get data from the other database shifttrack_plus the macro is almost the same with one different reference. Here how you get the data from the shift_breaks table for example:
Sub getSTP()
Cells.Clear
Set STC = ComWrapper.SupportObject.OpenSTPDatabase ‘ this is the different reference
Set rs = New ADODB.Recordset
rs.Open "select top 100 * from shift_breaks", STC.Db
Range("a1").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
That simple few lines of code and you can automate the extraction of your data not to mention having access to all possible information stored in the software.
