Tag Archives: Jedox Subsets

Subsets : Data Filters

The Jedox Data Filter (DFilter) is one of the more important filters that you can use in subsets. The Data Filter allows you to query the cube dynamically query a cube and order/filter your dimension based on this query. This is really handy  when you need to build a report based upon data, such as a top/bottom element listing, a zero suppressed report, or sorting your report based on the data.

When setting up this subset, you need to select the cube and dimensions that the dfilter will be based on. Make sure you select the right one, or you may not get the result you expect…


After you have selected the cube, the next thing to do is to select the dimension elements from each dimension for your dfilter.

To optimise performance of the data filter, ensure you specify an element for each of the dimensions . Like all filters, you can make your selections dynamic based on cells on your report:


Finally, you can select calculation options (sum, count the result, etc), specify the top or bottom count or choose how to sort the result. A completed report performing a top/bottom filter and sorting is here.

Data filters can really improve the dynamism of your reports and once you start using them , you will find that a lot of reports can benefit by the inclusion of this filter in your subsets.

Subsets : Picklists

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

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.

Jedox Web Subset

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.

Jedox Web Subset Report

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.