Running a Report
Important
The results of running a report are paginated. This means that the results may be split into multiple pages. For more information about how Ardoq handles pagination see this guide.
Let us assume that we have created a report consisting of three columns labelled "A" , "B" and "C". The report has the following data (note the missing value in the second row of column "B"):
| A | B | C |
|---|---|---|
| one | 1 | true |
| two | true | |
| three | 3 | false |
The API provides two ways to view the results of a report. The objects view returns a JSON representation of the report where each row is an object whose keys are the column names. The tabular view returns a JSON representation of the report where each row is encoded as a list of values. In both cases, the underlying data is the same. These different views are intended to support different use cases.
Objects View
/api/v2/reports/{id}/run/objects
View the full schema here.
The objects view returns a JSON representation of the report where each row is encoded as an object whose keys are the column labels.
{
"_meta": {"columns": [{"label": "A"}, {"label": "B"}, {"label": "C"}]},
"values": [{"A":"one", "B":1, "C":true},
{"C":true, "A":"one"},
{"A":"three", "B":3, "C":false}],
"_links": {}
}
Important
When working with the objects view, the absence of a value for a column is indicated by the absence of the corresponding key from the object. The
the order of the keys in an object is NOT guaranteed to match the order of column labels in the _meta.columns list.
Objects view use cases
- Data exploration using Postman, curl, etc.
- Easy access to values by column name.
- Working with large amounts of very sparse data (ie data with many missing values).
- One off scripting tasks.
- Comfortable writing custom code to order the values by column "label" if required.
Tabular View
/api/v2/reports/{id}/run/tabular
View the full schema here.
The tabular view returns a JSON representation of the report where each row is encoded as a list of values. This view closely resembles the tabular view in the Ardoq UI.
{
"_meta": {"columns": [{"label": "A"}, {"label": "B"}, {"label": "C"}]},
"values": [["one" , 1 , true],
["two" , null, true],
["three", 3 , false]],
"_links": {}
}
Important
When working with the tabular view, null indicates the absence of a value. This is required to ensure that each row has the same number of values as the number of columns.
Tabular view use cases
- Working with large amounts of data.
- Importing into Excel, Power BI, etc.
- Ordering of columns is important.
- Comfortable writing custom code to access values by "label" if required.
Comparing Views
Both the objects and tabular views use the same underlying data. While your choice of view is largely a matter of personal preference, there are some important differences between the two views that you should be aware of:
| Property | Objects View | Tabular View |
|---|---|---|
| Missing row value indicated by | key not present in object | null value |
null row value indicated by |
key not present in object | null value |
| Every row preserves ordering of columns | no | yes |
| Every row has same number of values | no | yes |
Integrations
The following sections provide examples of how to use the Ardoq API to run a report from various tools and services.
It is assumed that you have both a valid API token ARDOQ_API_TOKEN for your Ardoq organization and the Ardoq OID ARDOQ_REPORT_ID of the report you want to run. The report ID can be found in the URL when viewing the report in the Ardoq UI or by using the List Reports endpoint.
Power BI & Excel
Reports can be loaded directly into Microsoft Power BI and Excel by creating a "Blank Query" and using the following Power Query M formula language snippet. You must update the variables Domain, Token, ReportId and OrgLabel with appropriate values.
For more information about Power BI and Ardoq, see here
let
// The Ardoq domain for your organization. If you are using a custom domain, then you must set
// the value to be the URL of your custom domain - for example "https://mycompany.ardoq.com"
Domain = "https://app.ardoq.com",
// The Ardoq API token for your user
Token = "FIX ME",
// The Ardoq Identifier (oid) of the report you want to run
ReportId = "FIX ME",
// if you are not using a custom domain, then you must set the value for `OrgLabel` to be
// the label of your organization - for example:
// OrgLabel = "mycompany",
// if you are using a custom domain, then you may leave the value as `null`.
OrgLabel = null,
////////////////////////////////////////////////////////////////////////////////////////////////
// The following code does not need to be changed. It is used to run the report and return the
// results as a table. If the report is paginated, then the code will automatically fetch all
// pages and combine them into a single table.
Headers = [Authorization = "Bearer " & Token, #"User-Agent" = "Ardoq-Reports-PowerQuery"]
& (if OrgLabel <> null then [#"X-org" = OrgLabel] else []),
ArdoqReport = (optional url as nullable text, optional values as nullable list) as table =>
let
url = if url <> null then url else Domain & "/api/v2/reports/" & ReportId & "/run/tabular",
values = if values <> null then values else {},
Source = Json.Document(Web.Contents(url, [
Headers = Headers
])),
updated_values = values & Source[values]
in
if List.Contains(Record.FieldNames(Source[_links]), "next") then
@ArdoqReport(Source[_links][next][href], updated_values)
else
Table.FromRows(updated_values, List.Transform(Source[_meta][columns], each _[label])),
Report = ArdoqReport()
in
Report
ServiceNow
Reports can be loaded into Service now by creating a "Data Source". The following shows how to create a data source using a script that can handle pagination and load all rows from the report into an import set table.
- Open the "REST Message" page [System Web Services > Outbound > REST Message]
-
Click [New] in top right corner to create a new REST Message with the following values:
Field Value Name Ardoq Endpoint The Ardoq domain for your organization. If you are using a custom domain, then you must set the value to be the URL of your custom domain - for example "https://mycompany.ardoq.com". If you are not using a custom domain then it should be "https://app.ardoq.com" -
Click on the "HTTP Request" tab and add the following row, replacing
ARDOQ_API_TOKENwith your API token:Header Value Authorization Bearer ARDOQ_API_TOKEN -
If you are not using a custom domain, then you must also add the following header:
Header Value X-org The label of your organization - for example "mycompany" -
Click [Submit] to save the REST Message. You should see a
Default GETat the bottom of the page. Click on this link to open and edit the REST Message. Fill in the following and click [Submit] to save the REST Message.Field Value Name The name of the Ardoq report, for example "My Report". We will use this name to reference the HTTP Request Method later. Endpoint The full path to run the report. This should have the same domain as the "Endpoint" in step 2. For example https://mycompany.ardoq.com/api/v2/reports/ARDOQ_REPORT_ID/run/objects. ReplaceARDOQ_REPORT_IDwith your report id. -
Open the "Data sources" page [System Import Sets > Administration > Data sources]
-
Click [New] in top right corner to create a new Data Source with the following values:
Field Value Name The name of your report. You can re-use the same "Name" as in step 5 Import set table label The name of your report. You can re-use the same "Name" as in step 5 Type Custom (Load by script) You can now copy the following code into the online editor. Note that in this example
'Ardoq'is the name of the REST Message created in step 2 and'My Report'is the name of the HTTP Request Method created in step 5.(function loadData(import_set_table, data_source, import_log, last_success_import_time) { var r = new sn_ws.RESTMessageV2('Ardoq', 'My Report'); try { var url = r.getEndpoint(); while (url) { r.setEndpoint(url); var response = r.execute(); var parser = new JSONParser(); var report = parser.parse(response.getBody()); for (var i = 0; i < report.values.length; i++) { import_set_table.insert(report.values[i]); } url = (report._links.next != undefined) ? report._links.next.href : null; } } catch (ex) { var message = ex.message; import_log.info(ex.message); } })(import_set_table, data_source, import_log, last_success_import_time); -
Click on "Test Load 20 Records" to verify that the script works as expected.
Custom
#!/usr/bin/env python3
import json
import os
import urllib.request
def report_fn(report_id, view="objects"):
"""
Generator function that yields items from a report.
use the `view` parameter to specify which view to use.
Valid values are `objects` and `tabular`.
"""
host = os.getenv("ARDOQ_API_HOST", "https://app.ardoq.com")
url = "{}/api/v2/reports/{}/run/{}".format(host,report_id,view)
while url:
req = urllib.request.Request(url)
if os.getenv("ARDOQ_ORG_LABEL"):
req.add_header("X-org", os.getenv("ARDOQ_ORG_LABEL"))
req.add_header("Authorization", "Bearer " + os.getenv("ARDOQ_API_TOKEN"))
with urllib.request.urlopen(req) as resp:
report_data = json.loads(resp.read().decode("utf-8"))
for row_data in report_data['values']:
yield row_data
url = report_data.get("_links", {}).get("next", {}).get("href")
def objects_report(report_id):
return report_fn(report_id, view="objects")
def tabular_report(report_id):
return report_fn(report_id, view="tabular")
report_id = "FIX ME"
for d in objects_report(report_id):
print(d)