Data Reporting Server

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 getsql, which will return the actual SQL used by the API to make the query. This can provide a good starting point for writing your own queries.

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

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