Below are some useful tips and key information to help you work more efficiently with subsets. From applying multi-filters in the same tab to adding indentations, enabling formulas, and managing constraints, these insights will help you customize and optimize your subsets to better suit your needs.
Multiple filters in the same tab
The Hierarchie tab, Picklist tab, and Data tab allow multiple filters. In all three tabs, multiple filters are connected with a logical OR, meaning the resulting elements of the multiple filters will be added. In the Data tab, you can check the option Intersection from the second added filter. The filter will then be connected to the first filter with a logical AND, and the resulting elements will match both filters.
Enable formula validation before paste in Excel
When you press "Paste" in the Paste Subset dialog, Jedox Excel Add-in receives the formula to paste from the Web part. If the formula from the Web-side is broken or corrupted, it can provoke an Excel crash. The Enable formula validation before paste option in the Jedox Excel Add-in's Options Dialog checks the validation of Subset formulas before pasting, preventing the Excel crash.
This option activates a sophisticated formula validation procedure where all arguments will be extracted from the initial formula. Arguments are divided into two groups, simple arguments and complex arguments. A simple argument can be a value (such as number, text, etc.), a logical value (such as TRUE or FALSE), an error value (such as #N/A, etc.), an array, a cell reference, or a named range. A complex argument can be a nested formula. Each complex argument will be split into simple arguments recursively up to 5 times. Then each simple argument will be validated via internally used Microsoft Excel's formula validator. If each simple argument is valid and not longer than it can be accepted by the used validator (Microsoft Excel's formula validator only accepts arguments that are not longer than 253 characters), then the whole initial formula will be deemed as valid. Otherwise, you will encounter an error.
Constraints
- In contrast to Web Spreadsheets, where you can set an operator (e.g., ">") and utilize a reference or variable for the value, Excel doesn't allow direct usage of references within array expressions like {....}. This restriction particularly impacts the "Slice operators" section in Dfilter. To work around this limitation in Excel, you can use a static value or a custom expression for the entire “Slice operators” section and refer to a range of cells (e.g. two cells) via a reference or a named range. In this case, the first cell contains the operator, and the second the value.
-
Editing a Subset that relies on a variable for its argument, which affects other aspects of the Subset definition (such as Server/Database in the "General tab", or cube name in DFilter), is only supported in contexts where the variable value can be resolved.
-
In Jedox Web, the maximum result size of Subsets, such as in Comboboxes, is limited to 65,336 elements by default. This limit can be changed by adding a new entry
max_result_size. For example:<max_result_size>256000</max_result_size>. See also: Additional Spreadsheet Server Configuration. -
In the Excel COM Add-in, the Subset Editor only allows the use of two variants, static value or custom expression, to manually define a range or named range. Variables in Excel are also known as named ranges or defined names and can be used in the "Custom expression" field.
Updated April 11, 2025