This API allows you to request a specific report or data extract that might not otherwise be available from general purpose APIs. This can be used to extract data that has site specific requirements or are too specialised to be added to generic APIs.

There are two ways reports can be created, either via the API supplying the definition or using a server side pre defined command set. The API format is more limited in what it can do, while the server side has additional options available.

Performance. The risk with any general purpose API allowing free form queries is that those queries can run wild and blow out server capacity. Most report definitions will internally construct a SQL query and send this to the database, and then process the result set. Performance is therefore largely driven by the underlying database.

Security. There are a number of options for administrators to restrict this API. It is possible to apply automatic hidden predicates to many queries so that callers cannot escape to all data. For example, a common rule might be to block all sales older than 2 years.

Simple Examples

List products by Custom Field value

Your database has a custom field and you wish to retrieve all products with a certain value. Simply issue the following GET request

/OpenApi.../ReportRequest?from=products&filter=uCustomField=6

The server will respond with

{
  "data": {
    "ReportReply": [
      {
        "FinalResultCount": 1,
        "ReportRow": [
          {
            "Pid": 98,
            "Description": " BYBLOS - MISC",
            "ShortName": null,
            "Price": 99.90,
        ....
}

Server Side with SQL - Precanned report

Consider a retailer that has custom fields on locations and they wish to extract sales for some customers who used those stores recently. That is, list sales by (selected) stores and (selected) customers in the past 7 days

On the server, edit cannedreports.txt and add the following definition

#begin######
reportid  site.gold-customers-in-agencies

sql
select s.location as [loc],s.cid as [cid],count(*) as [cnt],sum(s.saletotal) as [totrev]
from sales s, locations l,customers c
where s.completeddt >= fdl$date(today-7)
and s.location=l.location
and s.cid = s.cid
and l.uMyStoreType = 'agency'
and c.uCustomerType = 'gold'
and s.phase=1
group by s.location,s.cid

meta
f10(100) f162(LocationId) f163(loc)
f10(101) f162(CustomerId) f163(cid)
f10(102) f162(NumberOfSales) f163(cnt)
f10(103) f162(TotalRevenue) f163(totrev)

#end######

This report can now be called with /OpenApi.../ReportRequest?canned=site.gold-customers-in-agencies and will return results as follows

{
  "data": {
    "ReportReply": [
      {
        "FinalResultCount": 2,
        "ReportRow": [
          {
            "LocationId": 123,
            "CustomerId": 1456,
            "NumberOfSales": 2,
            "TotalRevenue": 231.90
          },
          {
            "LocationId": 444,
            "CustomerId": 222,
            "NumberOfSales": 12,
            "TotalRevenue": 18265.10
          }
        ]
      }
    ]}
}