Scroll

Filter By Formula

As of v2018.2+, Exago supports using filters based on a formula. There is not a flag to enable this in the Admin Console, and will appear by default.

With Filter By Formula, there is an added flag in the Admin Console entitled Convert Formula Filters to SQL. By default, any filters that contain a formula are applied in memory during report execution. Setting this option to True will convert any formula filter strings to SQL to be applied in the database that is being queried instead of in memory. For any NoSQL Sources or Assembly Data Sources, this will always default back to the formula filter being applied in memory.

Warning: While enabling this option can provide a performance increase, formula filters can open up your database to exploitation. See Database Formulas for security considerations of formulas on the database before enabling this option.

Advanced Report Designer

In the Advanced Report designer, formula filters are available in the Filters section of the Report Options. Clicking the Add Formula button in the lower left will open a Formula Editor window to allow the creation of a formula to use in the filter.

reportfilters_addformulabutton.png

Report Filters window in Advanced Report designer with the Add Formula button

Any appropriate custom functions will also be available to use in the Formula Editor window for the formula filter.

Example:

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.

formulaeditor_quarternumber.png

Click Okay to save the formula and exist the Formula Editor. Notice that the formula has been added to the Filter By section of the Report Filters menu.

filterbyformula_quarternumber_added.png

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.

formulaeditor_year.png

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

filterbyformula_year_added.png

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.

Note: The Report Filters menu may be different depending upon the value of the Default Filter Execution Window setting in the Filter Settings of the Admin Console. In this report, the setting is set to Standard.

Set the QuarterNumber() and Year() values you want to filter the report data by and click Okay.

 

ExpressView Designer

In the ExpressView designer, adding a formula filter requires that the formula exist as a column on the report.

Note: Any objects needed for a formula in an ExpressView must also exist as their own column on the report as well.

expressview_addformula.png

Add Formula button in the ExpressView designer

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, see the Support Site article on Formulas in ExpressViews.

Caution: 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().

Linked Reports

Currently, formula filters are not supported in a report serving as a Drilldown in a Linked Report. This behavior is undefined.


Hidden Article Information

Article Author
Matthew Cherny
created 2018-08-21 15:25:18 UTC
updated 2019-05-16 21:00:00 UTC

Labels
no labels yet!
Have more questions? Submit a request