/api/statistics

The statistics endpoint is used to retrieve data that is not directly available through regular GET queries on entities. The typical use case is retrieving data for reports or dashboards where you need to apply an aggregate function on the data.

The endpoint uses regular ODATA syntax most of the time, but has been expanded to provide more functionality. The endpoint is used by specifying:

• what model you want to retrieve data from: model=AuditLog
• what columns you want to select: select=entitytype,entityid,field,displayname,createdat,updatedat
• optionally what filters you want to apply: filter=field eq ‘updatedby’ and ( not contains(entitytype,’item’))
• optionally what properties you want to expand: expand=Customer
• optionally how you want to join in other entities (when expand is not possible): join=auditlog.createdby eq user.globalidentity
• optionally what sorting you want: orderby=id desc
• optionally how many records to retrieve: top=50
• optionally how many records to skip: skip=50
• optionally if you want hateoas for the records: hateoas=true (important: if you want hateoas=true you need to select the ID and the StatusCode for the main model)
• OPTIONAL NOT DOCUMENTED FEATURES: HAVING, PIVOT, DISTINCT, RANGE

Hateoas

Retrieving hateoas through the statistics endpoint uses the same analysis as the regular controllers, but to be able to retrieve hateoas data you need to retrieve the ID and StatusCode of the main model being used. I.e. you cannot retrieve a summary of invoices summed by customer, and also expect to get hateoas data for the invoices.

Examples

Retrieve latest auditlog entries, and join in user

• /api/statistics?model=AuditLog&select=entitytype,entityid,field,displayname,createdat,updatedat&filter=field eq ‘updatedby’ and ( not contains(entitytype,’item’))&join=auditlog.createdby eq user.globalidentity&top=50&orderby=id desc

Retrieve all JournalEntryLines, expand Account and Account.AccountGroup

• /api/statistics?model=journalentryline&select=Amount,Account.AccountName,AccountGroup.Name&top=50&filter=account.accountnumber gt 1&expand=Account,Account.AccountGroup

Retrieve all JournalEntryLines, expand Account and sum Amount debit, Amount Credit and Amount total

• /api/statistics?model=journalentryline&select=Account.AccountName,sum(casewhen(Amount gt 0\, Amount\, 0)),sum(casewhen(Amount lt 0\, Amount\, 0)),sum(amount)&top=50&filter=account.accountnumber gt 1&expand=Account Note that commas in the parameter list is escaped with a backslash

Retrieve sum amount from JournalEntryLines grouped by accountnumber. Specify “MySpecialSum” as an alias for the sum

• /api/statistics?model=journalentryline&select=Account.AccountNumber,sum(amount)) as MySpecialSum&expand=Account

If you do not specify an alias for a field/function, an alias will be created automatically. For fields, the alias will be “EntityNameFieldName”, e.g. AccountAccountName in the previous example. For functions, all none alphanumerical characters will be removed, so the name in this example would be sumamount. For more complex functions you might end up with something like “sumcasewhenPeriodAccountYearlt2016amount0”. So use aliases (smil) Retrieve list of suppliers and their bankaccounts shown in a stuffed list in one column

• /api/statistics?model=Supplier&select=Supplier.ID,Info.Name as SupplierName,stuff(BankAccounts.AccountNumber,’,’) as BankAccounts&expand=Info.BankAccounts

When using the stuff function you need to add the ID of the main model to the select, because the stuff function will cause a grouping and a subselect using this ID Using functions in select/filter

As the examples above demonstrate, you can also use functions to aggregate data. If a summary function is used (i.e. a function that requires that a group by is applied), this is automatically added. For functions with multiple parameters, remember to escape the “,” with a backslash, e.g. “casewhen(amont gt 0\, amount\, 0)” These are the features that are currently supported:

• count(value)
• max(value)
• min(value)
• avg(value)
• sum(value)
• casewhen(expression, value-true, value-false)
• stuff(field,separator). The ID of the main model needs to be included in the select for this function to work.
• startswith(value)
• endswith(value)
• contains(value)
• isnull(value, valueifnull) - e.g. isnull(amount,0)
• setornull(value)
• length(value)
• year(value)
• month(value)
• day(value)
• now
• getdate
• thisyear
• thismonth