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 Excel, click Paste Subset in the Jedox Ribbon; in Jedox Web, go to Query > Paste Subset.
Note: be sure to mark a sufficiently large area when you begin, so that the display of the subset will not get truncated. Make sure you also select at least two columns. If you paste on three columns, you can display indents.
To the left and the right of the Subset Editor you can see the Stored Subsets and Preview areas. You can choose whether to show or hide these areas by clicking on their respective arrow icons, as indicated in the screenshot below.
The Preview area displays all subset modifications at once, provided that the Auto option is activated (see image below). If the “Auto” option is not activated, then you can update the preview by clicking on the refresh icon to the right of the Auto checkbox.
In the lower-left corner, you can see the PALO.SUBSET function and its sub-functions, and how they change during the creation of a subset.
Click Paste. The chosen subset function is inserted into the spreadsheet. Alternatively, if you choose to paste a function from a stored subset, click Paste stored. This button inserts a PALO.SERVER_SUBSET() formula that is linked to the stored subset. If you modify the definition of a stored subset on the server, all the reports using this formula get the new results.
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.
General and Sort filters take effect immediately; you must activate the other filters explicitly (see below). Checkmarks in the tabs indicate which filters are active.
Most entries in the filter tabs are self-explanatory. More sophisticated entries are explained here in more detail.
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 in the filter operation. The filters will then use the aliases defined in the attribute cube instead of the original names of the elements for their operations.
Note: If you use two aliases, the second one has priority over the first one. The first one is used only when the second one is empty.
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:
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.
To select options in the Hierarchy tab, you must first activate the Hierarchy Filter by marking the checkbox in the upper-right corner of the tab window. Most entries in this tab are self-explanatory.
Note: the following options are not yet implemented in the Jedox Web Subset Editor, only in Jedox Excel Add-in Subset Editor. In JedoxWeb the parameters for Siblings and Relative level must be changed manually in the formula if you don’t want to use the default values.
|Siblings||If selected, the siblings of the selected element are returned. In parallel hierarchies, if a selected element is not specified with a path, siblings for all parents are returned.|
|Relative level||If checked, the “start” and “end” levels are not absolute to the hierarchy but relative to the “bound” element (first argument of function PALO.HFILTER) with level 0. If no bound element is selected, only top elements are returned.|
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.
Note: the Ignore case checkbox is not yet implemented in the Jedox Web Subset Editor. You must change this parameter manually in the formula if you don’t want to use the default value.
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||Add the elements you selected in the picklist at the top of the subset.|
|Add at the end of the list||Add 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 only the elements you selected in the picklist.|
|As preselection in sort order||Allows you to insert only the elements you selected in the picklist, in the order you defined.|
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: The spacer element is not displayed when you use spacers in integrator projects.