This article describes the Subset Editor in Jedox. For an introduction to the topic of subsets and Dynamic Reporting, please see the article Dynamic Reporting – Introducing Subsets from our Business Intelligence Blog.
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 are the areas Stored Subsets and Preview. You can choose whether to show or hide these areas by clicking on their respective pin 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 (see image below).
With the Paste button, the chosen subset function will be inserted into the spreadsheet.
Using the filter tabs at the top of the Subset Editor, you can sort and filter your element list dynamically. Click on the tabs to enter arguments or expressions for the individual filters.
General and Sort filters take effect immediately; other filters have to be activated explicitly (right red arrow 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, it is possible to 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 will be 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 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.
As of Jedox 7.0 the “Don’t use alias” checkbox is also implemented in Jedox Web.
Note: the “Ignore case” checkbox is not yet implemented in the Jedox Web Subset Editor. This parameter must be changed 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.