Reports
SQL views#
The SQL views resource allows you to create and retrieve the result set of SQL views. The SQL views can be executed directly against the database and render the result set through the Web API resource.
SQL views are useful for creating data views which may be more easily constructed with SQL compared combining the multiple objects of the Web API. As an example, lets assume we have been asked to provide a view of all organization units with their names, parent names, organization unit level and name, and the coordinates listed in the database. The view might look something like this:
We will use curl to first execute the view on the HRHIS4 server. This is essentially a materialization process, and ensures that we have the most recent data available through the SQL view when it is retrieved from the server. You can first look up the SQL view from the api/sqlViews resource, then POST using the following command:
The next step in the process is the retrieval of the data.The basic structure of the URL is as follows
The {server} parameter should be replaced with your own server. The
next part of the URL /api/sqlViews/ should be appended with the
specific SQL view identifier. Append either data for XML data or
data.csv for comma delimited values. Support response formats are
json, xml, csv, xls, html and html+css. As an example, the following
command would retrieve XML data for the SQL view defined above.
There are three types of SQL views:
SQL view: Standard SQL views.
Materialized SQL view: SQL views which are materialized, meaning written to disk. Needs to be updated to reflect changes in underlying tables. Supports criteria to filter result set.
SQL queries: Plain SQL queries. Support inline variables for customized queries.
Criteria#
You can do simple filtering on the columns in the result set by appending criteria query parameters to the URL, using the column names and filter values separated by columns as parameter values, on the following format:
As an example, to filter the SQL view result set above to only return organisation units at level 4 you can use the following URL:
Variables#
SQL views support variable substitution. Variable substitution is only available for SQL view of type query, meaning SQL views which are not created in the database but simply executed as regular SQL queries. Variables can be inserted directly into the SQL query and must be on this format:
As an example, an SQL query that retrieves all data elements of a given value type where the value type is defined through a variable can look like this:
These variables can then be supplied as part of the URL when requested through the sqlViews Web API resource. Variables can be supplied on the following format:
An example query corresponding to the example above can look like this:
The valueType variable will be substituted with the int value, and the query will return data elements with int value type.
The variable parameter must contain alphanumeric characters only. The variables must contain alphanumeric, dash, underscore and whitespace characters only.
SQL Views of type query also support two system-defined variables that allow the query to access information about the user executing the view:
| variable | means |
|---|---|
| ${_current_user_id} | the user's database id |
| ${_current_username} | the user's username |
Values for these variables cannot be supplied as part of the URL. They are always filled with information about the user.
For example, the following SQL view of type query shows all the organisation units that are assigned to the user:
Filtering#
The SQL view api supports data filtering, equal to the metadata object filter. For a complete list of filter operators you can look at the documentation for metadata object filter.
To use filters, simply add them as parameters at the end of the request url for your SQL view like this:
This request will return a result including org units with "bo" in the name and which has org unit level 2.
The following example will return all org units with orgunit_level 2 or
4:
And last, an example to return all org units that does not start with "Bo"
Dashboard#
The dashboard is designed to give you an overview of multiple analytical items like maps, charts, pivot tables and reports which together can provide a comprehensive overview of your data. Dashboards are available in the Web API through the dashboards resource. A dashboard contains a list of dashboard items. An item can represent a single resource, like a chart, map or report table, or represent a list of links to analytical resources, like reports, resources, tabular reports and users. A dashboard item can contain up to eight links. Typically, a dashboard client could choose to visualize the single-object items directly in a user interface, while rendering the multi-object items as clickable links.
Browsing dashboards#
To get a list of your dashboards with basic information including identifier, name and link in JSON format you can make a GET request to the following URL:
The dashboards resource will provide a list of dashboards. Remember that the dashboard object is shared so the list will be affected by the currently authenticated user. You can retrieve more information about a specific dashboard by following its link, similar to this:
A dashboard contains information like name and creation date and an array of dashboard items. The response in JSON format will look similar to this response (certain information has been removed for the sake of brevity).
A more tailored response can be obtained by specifying specific fields in the request. An example is provided below, which would return more detailed information about each object on a users dashboard.
Searching dashboards#
When a user is building a dashboard it is convenient to be able to search for various analytical resources using the /dashboards/q resource. This resource lets you search for matches on the name property of the following objects: charts, maps, report tables, users, reports and resources. You can do a search by making a GET request on the following resource URL pattern, where my-query should be replaced by the preferred search query:
For example, this query:
Will search for the following:
- Analytical object name contains the string "ma"
- Return up to 6 of each type
- For CHART and MAP types, return up to 20 items
JSON and XML response formats are supported. The response in JSON format will contain references to matching resources and counts of how many matches were found in total and for each type of resource. It will look similar to this:
Creating, updating and removing dashboards#
Creating, updating and deleting dashboards follow standard REST
semantics. In order to create a new dashboard you can make a POST
request to the /api/dashboards resource. From a consumer perspective
it might be convenient to first create a dashboard and later add items
to it. JSON and XML formats are supported for the request payload. To
create a dashboard with the name "My dashboard" you can use a payload in
JSON like this:
To update, e.g. rename, a dashboard, you can make a PUT request with a similar request payload the same api/dashboards resource.
To remove a dashboard, you can make a DELETE request to the specific dashboard resource similar to this:
Adding, moving and removing dashboard items and content#
In order to add dashboard items a consumer can use the
/api/dashboards/<dashboard-id>/items/content resource, where
\<dashboard-id> should be replaced by the relevant dashboard
identifier. The request must use the POST method. The URL syntax and
parameters are described in detail in the following table.
A POST request URL for adding a chart to a specific dashboard could look like this, where the last id query parameter value is the chart resource identifier:
When adding resource of type map, chart, report table and app, the API will create and add a new item to the dashboard. When adding a resource of type users, reports, report tables and resources, the API will try to add the resource to an existing dashboard item of the same type. If no item of same type or no item of same type with less than eight resources associated with it exists, the API will create a new dashboard item and add the resource to it.
In order to move a dashboard item to a new position within the list of
items in a dashboard, a consumer can make a POST request to the
following resource URL, where <dashboard-id> should be replaced by the
identifier of the dashboard, <item-id> should be replaced by the
identifier of the dashboard item and <index> should be replaced by the
new position of the item in the dashboard, where the index is
zero-based:
To remove a dashboard item completely from a specific dashboard a
consumer can make a DELETE request to the below resource URL, where
<dashboard-id> should be replaced by the identifier of the dashboard
and <item-id> should be replaced by the identifier of the dashboard
item. The dashboard item identifiers can be retrieved through a GET
request to the dashboard resource URL.
To remove a specific content resource within a dashboard item a consumer
can make a DELETE request to the below resource URL, where
<content-resource-id> should be replaced by the identifier of a
resource associated with the dashboard item; e.g. the identifier of a
report or a user. For instance, this can be used to remove a single
report from a dashboard item of type reports, as opposed to removing the
dashboard item completely:
Visualization#
The Visualization API is designed to help clients to interact with charts and pivot/report tables. The endpoints of this API are used by the Data Visualization application which allows the creation, configuration and management of charts and pivot tables based on the client's definitions. The main idea is to enable clients and users to have a unique and centralized API providing all types of charts and pivot tables as well as specific parameters and configuration for each type of visualization.
This API was introduced with the expectation to unify both charts and reportTables APIs and entirely replace them in favour of the visualizations API (which means that the usage of charts and reportTables APIs should be avoided). In summary, the following resources/APIs:
are being replaced by
Note
New applications and clients should avoid using the
chartsandreportTablesAPIs because they are deprecated. Use thevisualizationsAPI instead.
A Visualization object is composed of many attributes (some of them related to charts and others related to pivot tables), but the most important ones responsible to reflect the core information of the object are: "id", "name", "type", "dataDimensionItems", "columns", "rows" and "filters".
The root endpoint of the API is /api/visualizations, and the list of current attributes and elements are described in the table below.
Retrieving visualizations#
To retrieve a list of all existing visualizations, in JSON format, with some basic information (including identifier, name and pagination) you can make a GET request to the URL below. You should see a list of all public/shared visualizations plus your private ones.
If you want to retrieve the JSON definition of a specific Visualization you can add its respective identifier to the URL:
The following representation is an example of a response in JSON format (for brevity, certain information has been removed). For the complete schema, please use GET /api/schemas/visualization.
A more tailored response can be obtained by specifying, in the URL, the fields you want to extract. Ie.:
will return
As seen, the GET above will return only the interpretations related to the given identifier (in this case hQxZGXqnLS9).
Creating, updating and removing visualizations#
These operations follow the standard REST semantics. A new Visualization can be created through a POST request to the /api/visualizations resource with a valid JSON payload. An example of payload could be:
To update a specific Visualization, you can send a PUT request to the same /api/visualizations resource with a similar payload PLUS the respective Visualization's identifier, ie.:
Finally, to delete an existing Visualization, you can make a DELETE request specifying the identifier of the Visualization to be removed, as shown: