Subsets Overview

The Subset Editor can be accessed across various contexts, including within a spreadsheet, in the Modeler interface, and directly from Excel. To open the Subset Editor in Jedox Web Spreadsheet, select Query and then click on Paste Subset. To access the Subset Editor in the Modeler, double-click on any dimension, then select either a Stored Subset or create a new Subset from the drop-down menu. To open the Subset Editor in Excel, navigate to Jedox Excel Add-in and click on Paste Subset.

The Subset Editor in Jedox allows you to filter data according to specific criteria. In most sections of Subset filters, you also have the flexibility to utilize various types of inputs. These include named ranges, which offer a convenient way to reference a set of cells by name; ranges, allowing you to specify a continuous series of cells; static values, providing fixed data entries; and variables, offering dynamic options for parameterization.

When selecting a parameter for the 'Subset' option in the Subset Editor (by checking the 'variable' checkbox), you can utilize cell references, named ranges, or (in Web) @variables. However, directly entering and using a formula expression (e.g., an IF formula) for this variable in the Subset Editor is not supported.

Also, it is important to note that the "types" of variables you can select may vary depending on the context (Web Spreadsheet, Modeler, and Excel) in which the Subset Editor is opened.

How to use a Subset

When using a Subset, mark a sufficiently large area so that the display of the Subset does not get truncated. Make sure you also select at least two columns. If you paste on three columns, you can display indents.

The Preview area displays all Subset modifications simultaneously, provided that the Auto (see 1 below) option is activated. If the "Auto" option is not activated, you can update the preview by clicking the Refresh button. (2)

In the lower-left corner (3), you can see the PALO.Subset function and its sub-functions, and how they change during the creation of a Subset.

Clicking Paste inserts the Subset into the selected area of the spreadsheet. (For more information on the Paste stored button, see Stored Subsets.)

You can dynamically sort and filter your element list by using the filter tabs at the top of the Subset Editor. Click on the tabs to enter arguments or expressions for the individual filters. The General and Sort filters take effect immediately, but for other filters, you must set up one or multiple criteria (see below). Checkmarks in the tabs indicate which filters are active.

General tab

In the General tab, you can select a database and a dimension. The following options are available in the General tab:

Server: by checking the variable option, you can define/change the Server/Database settings through an external variable (a named range in the spreadsheet). This is useful if a Jedox application is built in a development environment and later used in a production system with a different server name.

First alias / Second alias: here you can specify one or more aliases (based on the attributes of the dimension) to use in the Subset. Choosing an alias will have two effects:

1. If used, a Text Filter expression will, by default, operate on the chosen alias instead of element names. This behavior can be disabled in the Text Filter settings.

2. The Subset result returns the element name and alias (i.e., attribute values), allowing the alias to be displayed in, for example, a Subset-based Combobox.

When two aliases are defined, the second has priority over the first for both the Text Filter operation and the returned result. The first alias is only used when the second alias value is empty for a specific element.

If you want to display the alias entries, use two columns for the Subset. In the example below, the alias has been set to SalesPerson, which appears next to Customers in the result that follows:

Note that when checking the "variable" checkbox for both aliases (i.e. when a cell reference, named range, or @variable is used as source), the Subset Editor will generate an array expression containing the two variables in the PALO.Subset formula (for example, {A1,A2}). This expression is valid in Jedox Web, but not supported by Excel Add-in. For having two variables in a Subset in Excel, use the Excel CHOOSE() function in this way: CHOOSE({1,2},A1,A2). This formula can then be used as a variable for the first alias; the "variable" checkbox for the second alias does not have to be set in this scenario.

Layout: influences arguments of PALO.SORT(), see further description in Sort Tab.

Enumeration: helps you select the numbering of the hierarchy level. This affects the Subset, where the hierarchy level is specified in the filter.

Optional Settings: influence arguments of PALO.SORT(), see further description in Sort Tab.

Text tab

To select options in the Text tab, you must first add a text filter.

Any element name or alias name (if used) that does not match at least one of the regular expressions will be excluded from the Subset. You can enter standard text patterns using the wildcards * and ?. You can opt to use PERL regular expressions; some examples are provided. The Don't use alias checkbox allows you to opt out of using an alias.

Picklist tab

To select options in the Picklist tab, you must first add a picklist filter.

You can add elements to your Picklist filter. You can also change the order of the selected elements by using the arrow buttons to move them. You can select the way the elements are inserted in the list from the Behavior section. The following options are available:

Add in front of the list	Adds the elements you selected in the picklist at the top of the Subset.
Add at the end of the list	Adds the elements you selected in the picklist at the end of the Subset.
Merge in the list	Merges the selected elements with the ones already existing in the list that have the same name.
As a preselection for the Subset	Allows you to insert the elements which you have selected in the picklist. Other filters in the Subset may further modify the result.
As preselection in sort order	Allows you to insert only the elements you have selected in the picklist, in the order which you have defined. Elements added from other filters, such as Hierarchy filter, will be ignored.

In addition, you can insert empty element(s) by clicking the Edit (...) option on an added element.

These empty elements can then be used, for example, to have empty rows in a View when the Subset is used as the dimension for the View. Note that empty elements are always at the "root" level of the Subset and are not supported in hierarchical structures.

The elements you add in the Picklist filter are not affected by the filters in the other Subset Editor tabs unless you define some elements as a preselection.

Other tabs

The other Subset Editor tabs are described in their own articles: Hierarchy Tab, Attribute Tab, Data Tab, Sort Tab.

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 December 9, 2024