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.
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.
Pasting a Jedox Web subset in Excel Add-in
You can enable Jedox Excel Add-in to paste a subset from Jedox Web. To do this, you must set a DWORD 32 registry key in the Registry Editor, in
Computer\HKEY_Current_User\Software\Jedox\Palo\XLAddin. Name the key EnableNewSubsetEditor. If the value=1, the Jedox Web Subset Editor is enabled.