Tag Archives: Jedox Subsets

Subsets : Data Filters

Subsets : Picklists

A Picklist filter helps you choose and order specific elements and is especially helpful in a combo box. You can manually add elements from the element picker on the filter, or refer to spreadsheet or variable values for the elements:

The above selection will deliver a combo box with 3 elements: 2008, 2009, 2010. I could also have achieved the same by using a formula in the combo box properties:

This is one usage of Picklists. Another use is to position specific elements in the combo box. Check out the example here. You can see that on the first worksheet, we have a grid of 4 cells where you can enter in valid elements. The drop list will be filtered based on the pick list behavior that you select. The picklist behavior allows you to insert the elements at the front, back, merge (their normal position in the dimension) or subtract (show only those elements you have specified).

This enables you do things like selecting a default element that you want users to see when they open the report:

A more common approach is to use picklist to insert a default element into a combo box. For example, you may want the current month to always show up as the first element in the list. See the second worksheet in the example you downloaded. Here, we calculate the current month (using a now() function wrapped in a text() function) and pass this value into the picklist subset that dives the combo box. The result is that the user always sees the current month as the first element in the drop list when they open the report.

There one other trick required to get this to work. If you want the report to default to this value, you need to pass it into a variable that you can set when the report is opened. This requires a small macro on the __open function of the report:

function __open() {
$value = ActiveSheet()->Range('D10')->value;
define_variable('Month', $value);
}

Dynamic Reporting – Introducing Subsets

1 Reply

Jedox has a quite a unique way of building dynamic reports. In either Excel or File Manager on the web you can take advantage of Subsets. Subsets are what they say they are: a sub-set of elements from a particular dimension (set). They are dynamic and executed when the user runs the report. A couple of examples of what you can achieve with Subsets:

Hide empty rows on a report (zero suppress)
Show a dynamic Top 20 report, based on users selections
Display children of a parent (that the user selects) as rows on a report
Filter Elements on rows by their attributes

You can pretty much go crazy with Subsets. You can make a report completely dynamic based on what the user selects. Technically, subsets are spreadsheet array formulas, which means that you have to use the ctrl+enter to apply the subset to a range and shift+ctrl+enter to un-apply it (or just delete it).

You have the option of using the Subset Editor to develop the Subset formula, or you can have a go at writing it yourself (not recommended for young players). The great thing is, that because the wizard generates a formula, you can use the wizard to generate the formula and then modify it once you paste it out.

The subset Editor allows you to sort and filter your element list dynamically based on the following filters:

Hierarchy Filter
Text Filter
Picklist Filter
Attribute Filter
Data Filter
Sort Filter

or, a combination of any of these.

A lot of the components of the various filters accept ‘variables’. This allows you to specify cells, named ranges or Jedox variables on a spreadsheet to dynamically pass through into the subset, greatly expanding the functionality and flexibility.

When you click paste, it writes out the subset formula that you can then use for your row elements.

Check out this example here. It is a .wss file that you can import into Jedox Spreadsheet Manager. This is a very basic example, which allows you to double click on cell F8 (Region) and the children of this element will be displayed in Column A.

Subsets can be saved as either Private or Global. Global subsets can be shared with everyone with access to subsets, and Private are only available to the person who created them.

There are some other subset related functions available, including SUBSETSIZE (which returns the number of elements in a subset ) and SERVER_SUBSET (which allows you to refer to an existing subset and its elements).

The one thing to consider with Subsets, is that you need to define the maximum range of where the subset ends. In some situations this is fine and not an issue; but it can become messy if you need to format all the rows using conditional formatting to hide the fact you have some blank space under the returned values. The solution to this is to employ Dynaranges. Subsets work really well with Dynaranges and we will convert of a bunch of the different types of Subset filters in the context of Dynaranges.