Report API: Setting up the API

Calling the API

Each API call is linked to a specific report that needs to be in the system. If the Report API feature is enabled, the API Link button will appear when you generate a report.

Below this is all the information you need to make a call (including ID for the report and authentication key) and retrieve the data for the report via the API with the same permissions as the logged-in user who retrieves the link.

What is the format of the call and the retrieved data?

Here is an example of what the body of a call might look like and what changes you could easily make to get the right data from your system. A report consists of data from one or more data providers, which are called with the parameters from the report. In this case, the current report takes the input parameters period_and _station, and these parameters are thus set for both data providers in the report as below.

We have tried to make the call as understandable as possible, and instead of explaining all the parts, we’ll mention some changes you could make to get the correct data:

"dimensionConstraints": {
    "SalesDataProvider": [
      {
        "id": "dimension.period",
        "constraintType": "Range",
        "valueType": "DateTime",
        "values": [
          "2021-05-01 00:00",
          "2021-06-02 00:00"
        ]
      },
      {
        "id": "dimension.station",
        "constraintType": "List",
        "valueType": "String",
        "values": [
          "<all>"
        ]
      }
    ],
    "EmployeeCostStatisticsDataProvider": [
      {
        "id": "dimension.period",
        "constraintType": "Range",
        "valueType": "DateTime",
        "values": [
          "2021-05-01 00:00",
          "2021-06-02 00:00"
        ]
      },
      {
        "id": "dimension.station",
        "constraintType": "List",
        "valueType": "String",
        "values": [
          "<all>"
        ]
      }
    ]
  },
  "aggregations": [
    {
      "dimensionId": "dimension.station",
      "aggregationType": "None",
      "aggregationFilter": []
    }
  ]
}

Change periods
Enter the new dates in the values property for the id type dimension.period.

Change stations
If you only want data for specific stations, change from [“<all>”] to e.g. [“BBQ (10)”,“Disk (11)”].

The response will be in the below format, broken down by column specifications and rows in the report. The format is a grid with rows and values in JSON format, and the response should reflect the report displayed in the GUI as much as possible.

{
  "name": "Utfall",
  "columns": [
    {
      "columnName": "period",
      "columnType": "DateTime"
    },
    {
      "columnName": "station",
      "columnType": "String"
    },
    {
      "columnName": "sales",
      "columnType": "Numeric"
    },
    {
      "columnName": "employeeCost",
      "columnType": "Numeric"
    },
    {
      "columnName": "employeeOtherCost",
      "columnType": "Numeric"
    },
    {
      "columnName": "employeeHours",
      "columnType": "Numeric"
    }
  ],
  "rows": [
    ["4/8/2021 12:00:00 AM", "Kök (2)", "33279.496", null, "258.372000", null],
    ["4/8/2021 12:00:00 AM", "Servering (3)", "1183.968", "11620", "754", "50"],
    ["4/8/2021 12:00:00 AM", "Administration (5)", null, "166", null],
    ["4/12/2021 12:00:00 AM", "Kök (2)", "22211.639", "1532", "258.372000", "6."],
    ["4/12/2021 12:00:00 AM", "Servering (3)", "520.532", "9470", "754", "40"],
    ["4/12/2021 12:00:00 AM", "Administration (5)", null, "166", null],
    ["4/15/2021 12:00:00 AM", "Kök (2)", "30919.857", "2545", "258", "9."],
    ["4/15/2021 12:00:00 AM", "Servering (3)", "833.031", "7424", "754", "33"],
    ["4/15/2021 12:00:00 AM", "Administration (5)", null, "166", null],
    ["4/23/2021 12:00:00 AM", "Kök (2)", "42550.493", "6484", "258", "24"],
    ["4/23/2021 12:00:00 AM", "Servering (3)", "1455.923", "6677", "754", "30"]
  ]
}

The result can easily be processed e.g. as follows (C#):

string filename = @"C:\\temp\\response1.json";
ReportDoc doc = JsonConvert.DeserializeObject<ReportDoc>(File.ReadAllText(filename));

// Parse result into a list of dynamic objects
var stations = new List<object>();
foreach (var row in doc.rows)
{
    var index = 0;
    var station = new ExpandoObject() as IDictionary<string, Object>;
    foreach (ColumnDef col in doc.columns)
    {
        station.Add((col.columnName), row[index++]);
    }
    stations.Add(station);
}

// Access the values
foreach (var station in stations)
{
    var s = station as IDictionary<String, object>;
    Console.WriteLine($"The station {s["station"]} has a sales of {s["sales"] ?? "(null)"} and an employee cost of {s["employeeCost"] ?? "(null)"}");
}

// Structure of Json
class ColumnDef
{
    public string columnName;
    public string columnType;
}

class ReportDoc
{
    public string name;
    public List<ColumnDef> columns;
    public List<List<string?>> rows;
}

Authentication

Each access key will belong to you personally and will also only be able to retrieve data for the specific report for which it was created. It is therefore important to save the key somewhere safe. It is possible to create a new key for the same report, but remember that the first key you created will then be invalid.

How can I set up a report link inside Caspeco Cloud?

The following demonstrates how you can set up the report link inside the system