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.