Filter Value and General Format Best Practices

The FilterValue function has two required arguments: the filter’s index in the list of filters and the index of the filter’s value. If a report has two filters, “Order Detail.UnitPrice > '3.6' and Products.ProductName is one of ('Boston Crab Meat', 'Tofu')”. FilterValue(2,2) will return “Tofu.”

There is also an optional 3rd argument which dictates whether or not the output of the FilterValue function is going to be formatted.

For Example, consider the following filter setup with MM/dd/yyyy as the active global date format

FilterValue(3,1) will select the value for the 3rd filter and return “2014-07-10 00:00:00”, the globally invariant format. FilterValue(3,1,1), however, will return “07/10/2014”.

Most of the time, the optional parameter should be used in string concatenation (i.e. having a cell show the text “Order placed on: ‘21.03.2018’”) not as direct output because of the interaction with General Formatting.

General Formatting is the default cell format in advanced reports. RTA Reporting will do its own parsing on cell outputs for display purposes to the admin configuration unless the user specifically changes it. The string “08/19/2015” typed into a cell with general formatting will be interpreted and parsed as the global date format.

Returning to the example, if a user uses FilterValue(3,1,1) in a cell with general formatting, the result will be doubly formatted. FilterValue(3,1,1) will parse “2014-07-10 00:00:00” to “07/10/2014”. That output will then be taken by general formatting and parsed to the MM/dd/yyyy format again. This can cause some differing results depending on the date format in the admin console.

If the date format changes from “MM/dd/yyyy” to “dd/MM/yyyy”, FilterValue(3,1,1) will return “10/07/2014”. The underlying day is still July 10th, 2014 but if placed in a cell with general formatting, the output will be doubly formatted to “07/10/2014” or October 7th, 2014.

If FilterValue() has to be used as output into a cell, it is recommended that either the cell format is changed to Text and the optional parameter is used or the optional parameter is NOT used and the cell formatting is kept as general.