The FilterValue function may take up to three arguments:
- Required: the filter’s index in the list of filters
- Optional: the sub-index, starting with 1 for filters that contain multiple values (e.g. filters with an Is Between, Is Not Between, Is One Of or Is Not One Of operator). If omitted, filters with multiple values will return a comma-separated list of values.
- Optional: a boolean to determine if the value should be formatted following the user's culture setting
For example, consider a report with three filters:
Categories.CategoryNameIs One Of ('Confections', 'Dairy Products', 'Condiments', 'Beverages')Products.UnitPrice> '7.75'Orders.OrderDateIs Between '12/01/2020' And '12/31/2020'
FilterValue(1) will return Confections, Dairy Products, Condiments, Beverages
FilterValue(2) will return 7.75
FilterValue(3,1) will return 2020-12-01
The third argument determines if the return value is formatted.
FilterValue(3,2,1) will return 12/31/2020
Most of the time, the third argument should be used when concatenating strings with the return value of FilterValue(). For example:
=Concatenate("Last order placed on", FilterValue(3,2,1))
General Formatting is the default cell format in advanced reports. If FilterValue() has to be used as output into a cell, it is recommended that either the cell format be changed to Text and the optional parameter is used.