Get Reporting Data via API

Introduction

This short tutorial shows you how to use the Holistics APIs to get report data in raw tabular form (CSV, Excel).

This tutorial uses Ruby, but you can easily use any other language for it. You can also refer to the full source code here.

info

We'll be working on client libraries wrapper around our API. Once done, using the APIs will be simpler by just making a few function calls.

Mechanism

Since Holistics uses a async job queuing system to process job, you can't make a single API call to retrieve the results. We need to submit an 'export request', then wait for the export job to finish, and then make an API call to download the results.

API Endpoints used:

Steps

Let's go through the steps here.

1. Setting Up API Key

Please see guide to set up and retrieve your API key.

2. Initial code structure

To make things simple and reusable, we'll wrap our code around a HolisticsAPI class. We'll also use the httprb gem to handle making HTTP calls.

require 'http'
class HolisticsAPI
  def initialize(api_key, host: 'secure.holistics.io')
    @api_key = api_key
    @api_url = "https://#{host}/api/v2"
    @http = HTTP.headers({'X-Holistics-Key' => @api_key})
  end
end

3. (Optional) Get Filters ID for your Dashboard Export

If you want to include Filters in your export, you will need to get their Filter IDs. Please follow these steps to obtain them:

1. Get your Dashboard ID

The Dashboard ID can be retrieved by looking at its URL in the browser. In this sample URL below, the Dashboard ID would be 16076.

2. Get Filter ID

Supposed that your dashboard has a sets of filters like the one below. Let's get the ID of the Date filter to include it in our export.

We will use Get Dashboard API for this purpose. Let's call the API with the Dashboard ID from step 1.

curl --location --request GET 'https://secure.holistics.io/api/v2/dashboards/{your_dashboard_id}' \
--header 'X-Holistics-Key: your_API_key' \
--header 'Content-Type: application/json' \

The response would be quite lengthy, but you just need to find the dynamic_filters field to get the Filter ID.

You can then use this Filter ID in the next step.

Make sure you have the DashboardWidget ID in hand. The widget ID can be retrieved by opening up the widget in the dashboard, and look at the _e parameter in the URL. For example, 4175 is the widget ID of the below.

https://secure.holistics.io/dashboards/v3/12345-some-dashboard/?_e=4175

(Optional) Include filter conditions in your export

If you wish to include a filter condition in your export, first refer to step 3 to get your desired Filter ID.

Then, append dashboard_filter_conditions in your request body.

dynamic_filter_id is the Filter ID from step 3.
condition:
- operator: refer to Data Modeling Condition for all available operators.
- values: is an array of strings or integers that go with the operator.

For example, assuming that you have completed step 3, to apply a Date Filter that filters data from 2 months ago to the export, simply include this snippet to your request.

{
  "dashboard_filter_conditions": [
    {
      "dynamic_filter_id": 2335,
      "condition": {
        "operator": "matches",
        "values": [
          "2 months ago"
        ]
      }
    }
  ]
}

Then we make the call to submit widget export:

class HolisticsAPI
  # ...

  # output: 'csv' or 'excel'
  def submit_report(widget_id, output: 'csv')
    url = @api_url + "/dashboard_widgets/" + widget_id.to_s + "/submit_export"

    response = @http.post(url, json: {output: output})
    res = JSON.parse(response.to_s)

    if response.code == 200
      res['job']['id']
    else
      raise StandardError.new(res['message'])
    end
  end
end

If successful, this method returns the job ID of the job created.

5. Waiting for job to complete

The job will the go to the queue system waiting to be processed. This method below will continuously poll the job's metadata until it is either success, or failure.

class HolisticsAPI
  # ...

  def wait_for_job_status(job_id)
    url = @api_url + "/jobs/" + job_id.to_s

    while true do
      response = @http.get(url)
      res = JSON.parse(response.to_s)

      raise StandardError.new(res['message']) if response.code != 200

      status = res['job']['status']
      puts "===> status: #{status}"

      unless ['created', 'running', 'queued'].include?(status)
        return status
      end

      # Wait a while before pinging again
      sleep 2
    end
  end
end

6. Downloading the results

Once the job finishes, we make one final API call to

class HolisticsAPI
  # ...

  def download_export(job_id)
    url = @api_url + "/exports/download"
    response = @http.follow.get(url, params: {job_id: job_id})

    raise StandardError.new(JSON.parse(response.to_s['message'])) if response.code != 200

    response.to_s
  end
end

7. Putting things together

Once the above class is defined, let's put in a short code to perform all 3 steps to get the data.

API_KEY = 'your_api_key'
WIDGET_ID = 1234 # your widget

api = HolisticsAPI.new(API_KEY)

job_id = api.submit_report(WIDGET_ID)
puts "===> job_id: #{job_id}"

job_status = api.wait_for_job_status(job_id)
puts "===> job_status: #{job_status}"

if job_status == 'success'
  csv_data = api.download_export(job_id)
  puts csv_data # your CSV-formatted data here!
end

7. Profit!

Save the data into CSV, convert them into array, or feed them to other applications. The potentials are limitless!

Introduction​

Mechanism​

Steps​

1. Setting Up API Key​

2. Initial code structure​

3. (Optional) Get Filters ID for your Dashboard Export​

4. Submit widget export request​

5. Waiting for job to complete​

6. Downloading the results​

7. Putting things together​

7. Profit!​