Running ActionKit Reports And SQL Using The RESTful API¶

The RESTful API allows you to create, edit and run reports. In addition to Query and Dashboard reports, you can run ad-hoc queries against the production database.

Creating Reports¶

You can create reports by POSTing to the list resources for query or dashboard reports.

For QueryReports you'll need to provide at least:

description - a description of the report

name - a unique, human readable name for the report

short_name - a unique, short code for the report, containing only letters, numbers, and _, for use in dashboard templates and URLs

sql - a sql query

The schema has the full list of fields. Here's an example, creating a report that counts the number of subscribed users whose first name is Jeff. We'll name it "Subscribed Jeffs".

$ curl -i -u user:password -X POST -H 'Content-type: application/json' -d '{ "description": "Count Jeffs who are subscribed.", "name": "Subscribed Jeffs", "short_name": "jeffs", "sql": "SELECT COUNT(*) jeffs FROM core_user WHERE subscription_status = \"subscribed\" AND first_name = \"Jeff\"" }' https://docs.actionkit.com/rest/v1/queryreport/
HTTP/1.1 201 CREATED
Date: Thu, 14 Jun 2012 10:55:40 GMT
Vary: Cookie,Accept-Encoding,User-Agent
Content-Type: text/html; charset=utf-8
Location: https://docs.actionkit.com/rest/v1/queryreport/406/
Transfer-Encoding: chunked

To include parameters in your report, use double curly brackets around a parameter name: {{ name }}. We can rewrite our Subscribed Jeffs report to work for any name.

$ curl -i -u user:password -X POST -H 'Content-type: application/json' -d '{ "description": "Count of subscribers with {{ name }}.", "name": "Subscribed  {{ name }}", "short_name": "named", "sql": "SELECT COUNT(*) {{ name }}s FROM core_user WHERE subscription_status = \"subscribed\" AND first = {{ name }}" }' https://docs.actionkit.com/rest/v1/queryreport/
HTTP/1.1 201 CREATED
Date: Thu, 14 Jun 2012 11:21:08 GMT
Vary: Cookie,Accept-Encoding,User-Agent
Content-Type: text/html; charset=utf-8
Location: https://docs.actionkit.com/rest/v1/queryreport/407/
Content-Length: 0

To create a DashboardReport you'll need to provide at least:

description - a description of the report

name - a unique, human readable name for the report

short_name - a unique, short code for the report, containing only letters, numbers, and _, for use in dashboard templates and URLs

template - a Django template including one or more QueryReports

The template syntax is discussed in the manual section on DashboardReports. Here's an example using two instances of our 'Subscribed {{name}}' report:

$ curl -i -u user:password -X POST -H 'Content-type: application/json' -d '{ "description": "Counts of subscribed users named Jeff or Bob", "name": "Users named Jeff or Bob", "short_name": "jefforbob", "template": "<ul><li>Bobs: {% report \"named\" with \"Bob\" as name %}<li>Jeffs: {% report \"named\" with \"Jeff\" as name %}</ul>"}' https://docs.actionkit.com/rest/v1/dashboardreport/
HTTP/1.1 201 CREATED
Date: Thu, 14 Jun 2012 11:44:37 GMT
Vary: Cookie,Accept-Encoding,User-Agent
Content-Type: text/html; charset=utf-8
Location: https://docs.actionkit.com/rest/v1/dashboardreport/408/
Content-Length: 0

In the next section, we'll explain how to run reports.

Running Reports¶

You can run reports immediately (synchronously) or by creating a background task (asynchronously). Running a report always requires using the POST method. Synchronous reports are limited to 1000 rows.

The format of the results is determined using the Accept header, or a format query parameter. The format parameter must be in the query string. Valid formats are: html, json, tsv, or csv.

For tsv or csv formatted reports, you can email the results using the following parameters:

use_email - Set to true to send results via email, requires setting email

email - The email to send results to

zip - Set to true to Zip-compress the results.

For html formatted reports, you can email the results using the following parameter:

to_emails - A comma delimited list of emails.

For JSON formatted reports, you cannot email the results.

Reports are always cached. There's more detail below on the default caching and settings you can use to adjust it for your needs.

Running Reports Synchronously¶

To run a report immediately POST to the URL:

https://docs.actionkit.com/rest/v1/report/run/{{ short_name }}/

Here's a simple example:

$ curl -u user:password -X POST 'https://docs.actionkit.com/rest/v1/report/run/action_takers/?page_id=1162'
[[5477]]

You can pass parameters in query string or in the POST data using any format the API supports. If you send parameters in both with the same name, the two sets are merged, but those in the POST win out over any in the query string.

$ curl -u user:password -X POST 'https://docs.actionkit.com/rest/v1/report/run/action_takers/' -H 'Content-type: application/json' -d '{ "page_id": 1162 }'
[[5477]]

Synchronous reports are limited to 1000 rows and will timeout after 600 seconds.

Running Reports Asynchronously¶

To create a background task and run a report asynchronously, send a POST to the URL:

https://docs.actionkit.com/rest/v1/report/background/{{ short_name }}/

Use the URI in the Location header of the response to poll for the results.

$ curl -i -u user:password -X POST 'https://docs.actionkit.com/rest/v1/report/background/action_takers/' -H 'Content-type: application/json' -d '{ "page_id": 1162 }'
HTTP/1.1 201 CREATED
Date: Thu, 14 Jun 2012 12:28:07 GMT
Vary: Cookie,Accept-Encoding,User-Agent
Content-Type: text/html; charset=utf-8
Location: https://docs.actionkit.com/rest/v1/backgroundtask/9739/
Content-Length: 0

$ curl -u user:password -X GET https://docs.actionkit.com/rest/v1/backgroundtask/9739/ | python -mjson.tool
{
    "completed": false,
    "created_at": "2012-06-14T12:28:06",
    "details": {},
    "error": "",
    "id": 9739,
    "message": "",
    "params": {
        "email": "",
        "format": "json",
        "page_id": 1162,
        "report_id": 62,
        "report_name": "action_takers",
        "use_email": false,
        "zip": false
    },
    "resource_uri": "/rest/v1/backgroundtask/9739/",
    "updated_at": "2012-06-14T12:28:06"
}

Check the value of the completed key to see if a report has been run. A report that failed to run successfully will have a status of failed in the details hash, and a message with more information.

[aaron@local ~]$ curl -u user:password -X GET https://docs.actionkit.com/rest/v1/backgroundtask/9740/ | python -mjson.tool
{
    "completed": true,
    "created_at": "2012-06-14T12:38:25",
    "details": {
        "status": "failed"
    },
    "error": "",
    "id": 9740,
    "message": " A report SQL error occurred.  The error occurred in this SQL statement:<br><br><code>SELECT COUNT(*) Jeffs FROM       core_user WHERE subscription_status = subscribed WHERE first = Jeff</code><br><br>The error reported by MySQL is:<br><br><code>(1064, &quot;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;s FROM       core_user WHERE subscription_status = subscribed WHERE first = &#39;Jef&#39; at line 1&quot;)</code>",
    "params": {
        "email": "",
        "format": "json",
        "name": "Jeff",
        "report_id": 407,
        "report_name": "named",
        "use_email": false,
        "zip": false
    },
    "resource_uri": "/rest/v1/backgroundtask/9740/",
    "updated_at": "2012-06-14T12:39:11"
}

A report that has successfully run will have a status of complete in the details. If the requested format was json or html, the results will be in details. For example,

$ curl -u user:password -X GET https://docs.actionkit.com/rest/v1/backgroundtask/9739/ | python -mjson.tool
{
    "completed": false,
    "created_at": "2012-06-14T12:28:06",
    "details": {
        "results": [
            [
                5477
            ]
        ],
        "status": "complete"
    },
    "error": "",
    "id": 9739,
    "message": "",
    "params": {
        "email": "",
        "format": "raw",
        "page_id": 1162,
        "report_id": 62,
        "report_name": "action_takers",
        "use_email": false,
        "zip": false
    },
    "resource_uri": "/rest/v1/backgroundtask/9739/",
    "updated_at": "2012-06-14T12:39:31"
}

However, if the requested format was csv or tsv, then the results will be available as a download. The link to the download is in the details dictionary with the key of "download_uri".

For example,

$ curl -i -u user:password https://docs.actionkit.com/rest/v1/backgroundtask/9755/
HTTP/1.1 200 OK
Date: Thu, 14 Jun 2012 16:05:43 GMT
Vary: Cookie,Accept-Encoding,User-Agent
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked

$ curl -u user:password https://docs.actionkit.com/rest/v1/backgroundtask/9755/ | python -mjson.tool
{
    "completed": true,
    "created_at": "2012-06-14T16:05:32",
    "details": {
        "download_uri": "/rest/v1/report/download/9755/",
        "filename": "action_takers-20120614-160532.csv",
        "hostname": "dev.actionkit.com",
        "status": "complete"
    },
    "error": "",
    "id": 9755,
    "message": "",
    "params": {
        "email": "",
        "format": "csv",
        "page_id": 1162,
        "report_id": 62,
        "report_name": "action_takers",
        "use_email": false,
        "zip": false
    },
    "resource_uri": "/rest/v1/backgroundtask/9755/",
    "updated_at": "2012-06-14T16:05:42"
}

Running An Ad-hoc Query¶

You can also run SQL without creating a report. To run an ad-hoc query, use the URL:

https://docs.actionkit.com/rest/v1/report/run/sql/

You'll need to send at least the query parameter. Parameters:

query - A SQL query

$ curl -X POST -u user:password -H 'Content-type: application/json' https://docs.actionkit.com/rest/v1/report/run/sql/ -d '{ "query": "SELECT COUNT(*) FROM core_user WHERE state = \"NY\"" }'
[[59261]]

Just like reports, your query can use parameters using the double curly bracket syntax, e.g. SELECT COUNT(*) FROM core_user WHERE state = {{ state }}. You send values for your query parameters with the other request parameters.

$ curl -X POST -u user:password -H 'Content-type: application/json' https://docs.actionkit.com/rest/v1/report/run/sql/ -d '{ "query": "SELECT COUNT(*) FROM core_user WHERE state = {{ state }}", "state": [ "ny", "nj", "va" ] }'
[[99418]]

You can use the Accept header to control the format of the results. Or a format argument in the query string.

Ad-hoc queries have a 30 second timeout.

Ad-hoc queries are cached for 600 seconds by default. You can control the cache using two parameters:

cache_duration - How long to cache the results, defaults to 600s

refresh - Forces a re-run of the query if cached, defaults to False

For example, to apply a cache of 2 hours to the query we just ran, we would add a cache_duration key-value pair to our JSON, like below:

$ curl -X POST -u user:password -H 'Content-type: application/json' https://docs.actionkit.com/rest/v1/report/run/sql/ -d '{ "query": "SELECT COUNT(*) FROM core_user WHERE state = {{ state }}", "state": [ "ny", "nj", "va" ], "cache_duration": 7200 }'
[[99418]]

Note

We're trusting you to use the cache settings wisely. We think it's a pretty cool tool. But if necessary we will set a minimum cache time for all ad-hoc queries.

Caching¶

When developing reports you need to keep caching in mind. Reports may not update immediately when the database changes. That's a good thing, because it allows you to use reports in a scalable way. But, you'll need to understand the caching and how to control it.

All reports results are cached. By default, they are cached for 10 days.

Caching parameters:

refresh - Set to a True value to update the cached results.

cache_duration - Set to the number of seconds to cache the results. Defaults to 864000 for reports and 600 for ad-hoc queries.

For reports that use queries with {last_run} and {now}, you can use:

full_recalc - Set to a true value to recalculate from the beginning of the unix epoch.

Note

We'd love to add HTTP level caching for reports run via the REST API. We just haven't gotten there yet!