× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software

More
6 years 11 months ago - 6 years 10 months ago #297 by roller
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.
Last edit: 6 years 10 months ago by roller.

Please Log in or Create an account to join the conversation.

More
6 years 10 months ago #310 by mahmod
Hi Roller,

I'm using Shift Track Plus version 5.5.0.0

i have difficulties to extract 1 by 1 since my Workforce Team create so many Strand for different department. Is there any way you may assist me?

Please Log in or Create an account to join the conversation.

More
6 years 10 months ago #311 by mahmod
Hi Roller

Im using Shifttrack Plus ver 5.5.0.0 in my office

I have difficulties to extract 1 by 1 since my Workforce team create so many strand for different department.


Attachments:

Please Log in or Create an account to join the conversation.

More
6 years 10 months ago #313 by roller
You have to do an SQL statement with joins to collect data from two different tables. The [ShiftTrack].[dbo].[roster] table contains the 'location' and 'strand' columns.

The [ShiftTrack].[dbo].[roster_staff] table has the staff payroll ID and other information.

the join will be on [ShiftTrack].[dbo].[roster_staff].[roster_key] = [ShiftTrack].[dbo].[roster].[key]

Please Log in or Create an account to join the conversation.

More
6 years 10 months ago #317 by mahmod
Sorry as I'm not familiar with MySQL Statement. I hope you may assist me to build the script for me:

1. Login ST Plus
2. From Menu, select Schedule --> Quick Roster Editor
3. On the Editor menu, File --> Open a roster
4. Select Location, Strand & Roster Start, then click OK
5. After roster loading, File --> Export Roster
6. Set Date range to export & location the file will be export then click OK.

Please Log in or Create an account to join the conversation.

More
5 years 9 months ago #504 by mozako
Hi Edgar

Any sample you have or assist me.i follow you guideline but nothing

Please Log in or Create an account to join the conversation.

Time to create page: 0.603 seconds
Powered by Kunena Forum