As of v2018.2+, the application supports using filters based on a formula.
Advanced Report Designer
- Open the Report Filters dialog by clicking on the corresponding icon in the toolbar.
- Click the Add Formula button in the lower left to open the Formula Editor dialog.
Any appropriate custom functions will also be available to use in the Formula Editor window for the formula filter.
Let's build a Filter By Formula that will easily allow us to filter by the quarter number and year of a sales report without prompting us to scroll through a calendar in order to select date values.
First, open the Filters section of an Advanced Report, and click the Add Formula button. This will open the Formula Editor.
Next, apply the QuarterNumber() function, which will return the fiscal quarter of a date as a number, and a date value as a parameter.
Click Okay to save the formula and close the Formula Editor. Notice that the formula has been added to the Filter By section of the Report Filters menu.
Select the filter that was just created and select the Prompt for Value checkbox. This will allow us to dynamically state which quarter we want to filter by when the report is run.
Select the AND With Next Filter option in the filter relationship dropdown to ensure that the report returns data that is specific to both the quarter number and year, then click the Add Formula button.
Now, let's add the Year() function as a filter formula.
As with QuatertNumber(), add a date value as the parameter for Year(), which will return a four digit number representing the year of the date value.
Next, click Okay to save the formula and exist the Formula Editor.
Select the filter that was just created and select the Prompt for Value checkbox.
Finally, click Okay to save and close the Report Filters menu.
Upon report execution, the Report Filters menu will prompt for values for both the QuarterNumber() and Year() functions.
In the ExpressView designer, adding a formula filter requires that the formula exist as a column on the report.
Any objects needed for a formula in an ExpressView must also exist as their own column on the report as well.
Once the formula is created as a column, select the Filters pane and drag the formula column over to the Filters pane to begin creating a filter on the formula. For more information about building formulas in an ExpressView, review the ExpressView: Formula Columns article.
Certain function are available in the Formula Editor that are not compatible with the context of formula filters, such as CellValue(), FilterValue(), Hyperlink, LoadImage(), PageNumber().