Adding Filters to Report URL called by the API

Our reporting API supports a number of parameters added to the URL. These can be used to add filters or rows to the report. They can also be used to specify the format, data source, name, layout, and template of the report. This API endpoint cannot be used to create new page templates or layouts directly (users should create these templates/layouts in the UI for use in direct calls to the API).

This article focuses on the calculated values in the query URL, specifically rowDimensions and filters. Before reading, please familiarize yourself with Getting Started and Authentication as this request requires authentication.

Breaking down a report query

When requesting a report from the UI, the URL of the progress screen will have details about the report. This contains both plaintext and base-64 encoded data for the GET request.

https://app.finaleinventory.com/ACCOUNTPATHCOMPONENT/doc/report/pivotTableStream/1536103912671/Report.pdf?format=pdf&data=product&attrName=%23%23user042&rowDimensions=~kpjNAf7AzQExwMDAwMCYsHByb2R1Y3RGdXNlRGVsYXnAzQIvwMDAwMA&filters=W1sicHJvZHVjdFByb2R1Y3RVcmwiLCIvZGVtb3NoaXBzdGF0aW9uL2FwaS9wcm9kdWN0LzR4OCUyMERKJTIwMTUiXSxbIkZvcm11bGFGaWx0ZXIiLCJsb29rdXAoXCJwcm9kdWN0U3RvY2tBdmFpbGFibGVUb1Byb21pc2VDYXNlRXF1aXZhbGVudHNcIikgPiAxMCJdXQ%3D%3Dd&reportTitle=Brian+test

Fields

• plaintext fields
  • format: output format of report [xls|pdf|word|csv|tsv|json]
  • data: short for the dataset of the report (see Dataset in User Interface)
  • attrName: internal name for the report
  • pageTemplate: layout of the page to use (see Formatting in User Interface)
  • reportTitle: visible name of the report (affects output filename) base-64 encoded fields
  • rowDimensions: description of dimensions used in the report (u8int array)
  • filters: description of filters applied to report (JSON array/string)

You can read more about basic reporting API requests here.

rowDimensions

rowDimensions is an extremely concise description of the row dimensions used in the report. This is still encoded base-64 for the GET request, but it is used by our report engine directly and is not easily modified. Users of the reporting API should use dimension values generated by the User Interface.

filters

filters are the bread-and-butter of reports. Our API is built to allow easy changes to the value of filters when making requests to the reporting API. The above example is decoded below:

[
  [ "productProductUrl",
    "/ACCOUNTPATHCOMPONENT/api/product/4x8%20DJ%2015"
  ],
  [ "FormulaFilter",
    "lookup(\"productStockAvailableToPromiseCaseEquivalents\") > 10"
  ]
]

Filters are always defined in an array. The first value is the field/type and the second value is the filter value itself. There are two main categories of filters: built-in and formula filters.

Built-in filters

It is to your advantage to use built-in filters. They may take advantage of internal data structures and are much more efficient at filtering data. These can be identified by the filters that exist in the User Interface (everything except "Formula Filter" in the Filters section, see image below).

There are several types of filters built-in for our system, and we add/optimize more fields every day. The above filter for productProductUrl accepts a string as the filter value. Other string values may be in the form of a single-element array. Numeric filters are usually in the form of an array of two values ([lower_bound, upper_bound]).

For example, a date range filter on order date on the order dataset (orderOrderDate) can be expressed as a type of range filter using the ISO8601 date format:

["orderOrderDate",["2018-08-06T16:00:00.000Z","2018-09-20T16:00:00.000Z"]]

Date range filters can also be expressed as a range object, specifying a duration, offset, and length:

["orderOrderDate",{"duration":"isoweek","offset":-1,"length":1}]

This is relative to the current UTC date. The available values for duration are [week|isoweek|month|year]. This value defines the units for the offset and length. The above example calls for the last ISO week (Monday through Sunday). If it used week instead, it would be Saturday through Sunday.

To find the format of a specific built-in filter, we recommend using the filter in an existing report in the User Interface and decoding the string. This can be done quickly in the browser using the developer console (pressing F12) and parsing the URL using the following snippet:

JavaScript (run in a browser)

var filters = "... paste filters value here from URI ...";
console.log(window.atob(window.decodeURIComponent(filters)));

Note that I use window.decodeURIComponent() before decoding from base-64 to a binary string. This is due to characters allowed in base-64 encoding but not allowed in HTTP URIs. Most libraries will handle this when making programmatic GET requests.

Formula Filters

Formula filters offer the ability to filter values not currently optimized/built-in to our system. These can add impressive functionality to the reporting API, but may not be as efficient on larger data sets (leading to timeouts for requests).

Fields available to use depend on the data set. Refer to the User Interface on which fields can be used by creating a "Formula filter" in the Filters section and clicking on the drop-down to the right of the formula entry.

Formulas that evaluate to true for a row are included. These formulas can contain logical expressions, math operators, basic functions, and data type conversions. Here is a complete reference.

Note that double-quotes inside of the formula string have to be escaped by a \backward slash.

Encoding filters

Filters are encoded using base-64 and sent as parameters of the GET request in the URI. Here are some examples of encoding in base-64 in a few popular languages:

JavaScript (browser)

window.btoa('Hello World!')

reference

JavaScript (NodeJS)

Buffer.from('Hello World!').toString('base64')

reference

Java 7/8

See Stackoverflow question on this topic

PHP

base64_encode('Hello World!');

reference