Paylocity

This will require Data Admin privileges to setup and Builder for implementation

iDashboards is able to connect to a Paylocity instance by using a JDBC driver. A JDBC driver is a software component enabling a Java application to interact with a database. To connect with individual databases, JDBC (Java Database Connectivity API) requires specific drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between the client and the database. iDashboards implements native protocols and does not require additional software installed on the driver host. In other words, iDashboards provides driver connectivity with the JDBC Data Source via the specified driver. This document explains a step-by-step procedure to create the Data Source with a Premium Data Connector.

CDATA for Paylocity

CData JDBC Driver for API is where we get the documentation on making this connection, however, the Premium Data Connectors are not a straight forward process which is why we make articles like this to help you make the connections more easily. This one requires a few steps, with a few different links to get very specific pieces of information to make this connection within iDashboards. The API Driver is part of the Premium Data Connectors if you do not have these please contact your Client Success Manager. 

Oauth2 Credentials, Company ID and API Profile file

To get the Oauth2 Client ID and secret, you need to make a request to Paylocity:

Paylocity will provide the secure client credentials and set up the scope (type of requests and allowed company numbers). You will receive the unique client id, secret, and Paylocity public key for the data encryption. The secret will expire in 365 days.

In order to find your Company ID:

On desktop, you can also find your Company ID in the top left-hand corner of any application within Paylocity after logging in. In the Paylocity Mobile App, you can find your Company ID by choosing "More" and then "About" in the menu after you log in.

To obtain the Paylocity API Profile file:

It will be attached to this article.  You will need to download and place it on the server where it is easily accessible.  For reference, I will write this article with it existing in the C:\Program Files\iDashboards_SRV\ivizgroup\CDATA\ (you will have to create this directory). 

Building the Connection String

1. Open a Command Prompt as administrator

2. Navigate to c:\Program Files\iDashboards_SRV\ivizgroup\drivers\

3. Bring up the Driver's Console, by typing java -jar cdata.jdbc.api.jar

4. These are the settings you need to set:

• Under the OAuth heading, you need to set the InitiateOAuth setting to "GETANDREFRESH"
• set the OAuthClientId field by adding the Client Id you received from Paylocity.
• set the OAuthClientSecret field by adding the Client secret you received from Paylocity.
• Under the APIProfice heading, you need to add the location to the paylocity.apip file you download from this article
• Set the ProfileSettings field by adding CompanyId=<your Company ID

Data Connection

Follow the steps below to set up a data source in iDashboards Admin or Data Hub

1. Select Data Sources
2. Then New
3. Select the Data Source Type: Generic JDBC
4. Under Data Source Name, Give it a name (this is how iDashboards will see you Data Source)
5. Make sure the Quote Characters are set to: " "(Quotation Marks)
6. Click the check boxes for Both: Quote Table and Column Names and Allow Custom Queries
7. In the JDBC Driver Class field enter: cdata.jdbc.api.APIDriver
8. In the Database URL field, you can click on Copy to Clipboard and paste it into the field.  It should look something like: jdbc:api:InitiateOAuth="GETANDREFRESH";OAuthClientId="<your Client ID>";OAuthClientSecret="<Your Client Secret>";Profile="C:\Program Files\iDashboards_SRV\ivizgroup\CDATA\paylocity.apip";ProfileSettings="CompanyId=<your Company ID>";

Additional Information

Most tables require a parameter to query data.  IE, employee ID when pulling data, so you will need to pull data by including that information in the query.  (select * from employee where employeeId=<employeeId or Grouop>)

Disclaimer: iDashboards Technical Support Engineers are not proficient in the Management and Integration of every API that exists as new APIs are being created everyday with each company that wants to provide seamless integration with other software. However, we do our best to help you make these connections because we want to assist you in your success. If you do not see an article for a connection you would like to make please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this integration.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.