The Data Reporting Server is a read replica of your primary database. It gives you direct access to data, and allows you to run queries without impacting the transactional work of your platform.
How do I get access?
The reporting server isn't part of every platform installation. To take advantage of its capabilities, talk to your account manager.
Once set up, we'll provide you with all of the connection details.
When is it updated?
By default your production database is replicated to your DRS database every 24 hours, soon after midnight.
What can I use it for?
The reporting server is a full replica of your platform database, so you can report on anything you are recording data for. In most instances, that will mean history data, and the most interesting examples we've seen use history data written by Case Management.
As well as a full read replica of your platform database, the reporting server also includes an additional writable database, known as the "scratch database". This can be used to store the outcome of any calculations and queries you make.
History SQL Queries
The getHistory() and getHistories() API calls include a parameter called
Stored Procedures
To make querying history data simpler, the following stored procedures are available. Both have the same parameters.
History Last Summaries
EXEC getHistoriesLastSummary @JsonParams;
This gets the last summary event from each history. This is the recommended call for most data if possible.
History Last Values
EXEC getHistoriesLastValues @JsonParams;
This gets the last value of every field in a history event stream. It's very powerful but a lot more expensive to perform.
Parameters
| Name | Type | Description |
|---|---|---|
| labela | String | A comma separated list of labela values to query. Each labela needs to be placed inside double single quotes (see below for examples) |
| labelb | String | As above, but optional |
| labelc | String | As above, but optional |
| labeld | String | As above, but optional |
| labele | String | As above, but optional |
| CaseSubType | String | Specific to Case Management, a comma separated list of case sub types to query |
| Columns | String | The columns to return. A comma separated list of column names in square brackets |
| StartedBefore | String <datetime> | Histories created before this date, format YYYY-MM-DD |
| StartedAfter | String <datetime> | Histories created after this date, format YYYY-MM-DD |
| UpdatedBefore | String <datetime> | Histories updated before this date, format YYYY-MM-DD |
| UpdatedAfter | String <datetime> | Histories updated after this date, format YYYY-MM-DD |
| Filters | Array <objects> | You can include up to three filters. Each filter is an object where the key is one of your chosen columns (the named columns above), and value a comma separated list of values to include. See below for an example |
| AuditMode | Boolean | If true, the latest values of all events will be returned, even those that have been hidden. This defaults to false, only visible events are considered. See Hiding and Replacing Events for more information about hiding events |
Examples
This first example reports on two Case Management cases, created in the first quarter of 2022. It focuses on seven fields/columns in the case data.
BEGIN
EXEC getHistoriesLastSummary N'{
"labela": "''Case Management - Schools Placing Request'',''Case Management - P1 Registration''",
"Columns" : "[formData.data.CASESUBTYPE],[formData.data.INITUSERNAME],[formData.data.CASECLOSENOTES], [formData.data.BUSINESSKEY],[formData.data.UPRN],[formData.data.SCHOOL], [formData.data.ADDRESS]",
"StartedAfter": "2022-01-01",
"StartedBefore": "2022-03-31"
}';
END
This second example reports on a wider date range and increased number of columns, but applies two filters. The first filter limits the report to two named schools, the second to cases raised by the "anonymous" user (users who weren't signed into their account when they logged the case).
BEGIN
EXEC getHistoriesLastSummary N'{
"labela": "''Case Management - Schools Placing Request'',''Case Management - P1 Registration''",
"StartedAfter": "2022-01-01",
"StartedBefore": "2024-12-31",
"Columns" : "[formData.data.CASESUBTYPE],[formData.data.INITUSERNAME],[formData.data.CASECLOSENOTES], [formData.data.BUSINESSKEY],[formData.data.UPRN],[formData.data.SCHOOL],[formData.data.ADDRESS], [formData.data.INITCASESUBTYPE],[formData.data.INITCASETYPE],[formData.data.INITIATOR], [formData.data.IWOULDLIKETO],[formData.data.KNOWNAS],[formData.data.LANGUAGE], [formData.data.LASTNAME],[formData.data.LASTNAME2]",
"Filters": [
{ "formData.data.SCHOOL": "''Example Primary School'',''Example Secondary School''" },
{ "formData.data.INITUSERNAME": "''anonymous''"}
]
}';
END
Forms with Repeating Pages
Because repeating page data is itself held in an array (each instance of a repeating page is an object of fields in that array), your column names will have to be escaped. This example shows a mix of standard and repeating page data:
"Columns" : "\"formData.data.INVOLVED[0].NAME\",\"formData.data.INVOLVED[1].NAME\",[FormData.data.CASESUBTYPE]",
Note that it is not possible to wildcard or select "all" instances of a repeating page.
Power BI Dashboards
The Power BI Templates section of the Community Library has templates you can use to connect Power BI to the reporting server. We will be adding more over time, and encourage you to contribute any you have written.
