How to set up Excel Plugin for SQL Analytics on Mac

This guide is written to support developers who want to integrate with Kleer.

Bilaga: How to set up Excel Plugin for SQL Analytics on Mac.pdf

Introduction This guide is written to support developers who want to integrate with Kleer. The document is an addition to the main Database documentation available upon request from your contact at Kleer, it is assumed that you have received an API/Database token.

Excel Plugin for SQL Analytics Setup SQL-database

Download and install IODBC with installation type – Typical http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
Download and install ODBC drivers with installation type – Typical https://www.actualtech.com/download.php (These are the drivers we use but there are other options as well)
Open IODBC and click on “Add”. a. Select “Actual Open Source Databases”
Input: a. Data Source Name: PE Analytics b. Description: c. Which type of database do you want to connect to: MySQL and MariaDB d. Server: data.kleer.se e. Port: 3306 f. Click on “Advanced Options” and enter the path for the SSL-certificate under “Authority”. g. Database: report_external_v1

Download CA certificate https://my.kleer.se/web/service/download/report-external/ca

Input the certificate in the correct folder. The path in your “Finder” should look like following:

Setup Excel (Swedish Excel)

Go to “Data” tab
Click on “Get Data” and “From Database (Microsoft Query) under the tab “Data” in Excel
Choose the correct source and enter your user credentials (sent to you by Kleer Accounting).
Input SQL-code in “SQL Statement” (As an example you could put select * from client; which asks Kleer to list all clints).

ODBC connection when using SSH tunnel If not static IP address/addresses can be used there will always be an option to connect to Kleer database using SSH tunnel. Guide to set up SSH tunnel When using SSH tunnel TCP/IP Server is set to localhost.