Subsets Overview

A subset is a set within a dimension. With a subset, you can select elements or sets of elements and sort them according to certain criteria. You can, for example, display the Top 10 of a product list; or you can display the list of products above or below a defined threshold.

The Subset Editor in Jedox allows you to filter data from a database with your criteria. To open the Subset Editor in Jedox Web, go to Query > Paste Subset.

When you are using a Subset, make sure to 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 at once, provided that the Auto (see 1 below) option is activated. If the "Auto" option is not activated, then you can update the preview by clicking on 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 Paste stored button, see Stored Subsets.)

You can sort and filter your element list dynamically 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 activate them explicitly (see below). Checkmarks in the tabs indicate which filters are active.

After checking a "Variable" checkbox in a tab, the corresponding user interface for manual selection is visibly disabled (greyed out).

Most entries in the filter tabs are self-explanatory. More sophisticated entries are explained here in more detail.

General tab

In the General tabs, items are ordered alphabetically in the pulldown menus for selecting 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.

Alias: here you can specify one or more aliases to use in the subset, based on the attributes of the dimension. Choosing an alias will have two effects:

  1. A Text Filter expression, if used, will by default operate on the chosen alias instead of element names. This behavior can be disabled in the Text Filter settings.
  2. The returned subset result will return both element name and alias (i.e., attribute values), which allows the alias to be displayed in, for example, a subset-based ComboBox.

When two aliases are defined, the second one has priority over the first one for both the Text Filter operation and the returned result. The first alias is used only 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 on variables: when ticking 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 Options and Optional Settings: these selections influence arguments of PALO.SORT().

Hierarchy enumeration options: here you can select the numbering of the hierarchy level. This affects the subset, where the hierarchy level is specified in the filter.

Text tab

To select options in the Text tab, you must first activate the Text Filter by marking the checkbox in the upper-right corner of the tab window.

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 activate the Picklist Filter by marking the checkbox in the upper-right corner of the tab window.

Here you can either add elements to the subset which will not be deleted by other filters, or you can define some elements as a preselection, to which the other Subset Editor tab options will be applied.

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.

Additionally, you can add a spacer element by clicking the + button

This adds an empty row or column in your Views or DynaRanges. The spacer elements are not available with the following behavior modes:

  • Merge in the list
  • As preselection for the subset

Note that the spacer element is not displayed when you use spacers in integrator projects.

Other tabs

The other tabs are described in own articles: Hierarchy Tab, Attribute Tab, Data Tab, Sort Tab

Enable formula validation before paste

When you press "Paste" in the Embedded Paste Subset Dialog, Jedox Excel Add-in receives the formula to paste from the Web part. There can be scenarios when the formula from the Web-side is broken or corrupted. When Jedox Excel Add-in tries to paste the broken or corrupted formula onto the worksheet, it can provoke an Excel crash. To prevent this problem, you can activate validation of subset-formulas before pasting. To do this, use the Enable formula validation before paste option in the Jedox Excel Add-in's Options Dialog. It 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. In this case, if you are pasting in a cell without PALO.SUBSET formula, a pop-up message shows up with three options:

  • Paste anyway

  • New formula as text

  • Cancel

And if you are pasting in a cell with PALO.SUBSET formula, a pop-up message shows up with three options:

  • Paste anyway

  • Paste formula as text

  • Previous formula

Subsets stored with View XML schema

The Subset Editor stores the subsets using the View XML schema rather than the legacy Subset XML schema. The View XML schema offers several advantages; for example, the stored subset remains stable when attributes are recreated while using AFilter.

Subset in Excel

When you are using the data filter and criterium “>” in the Excel Add-in, it is not possible to use any variables because the subset formula is displayed incorrectly.

In this case, a namedRange must be defined.

Constraints

  • You cannot insert the formula if you use the variable.

  • When using a parameter for a subset option in the Subset Editor (i.e. checking a "Variable" checkbox), cell references, named ranges or (in Web) @variables can be used. However, entering and using a formula expression (e.g. an IF formula) directly for this variable in the Subset Editor is not supported.

  • 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 in the file ..\core\palo_config.xml under the section <palo_configuration>. For example: <max_result_size>256000</max_result_size>. See also: Additional Spreadsheet Server Configuration.

Updated September 27, 2022