Category Archives: Jedox Subsets

Jedox Subsets

Subsets : Data Filters

Leave a reply

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…

d-filter1

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:

d-filter2

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 : Hierarchy Filters

Leave a reply

The Hierarchy filter is a major subset type that  is usually used in Jedox Web. The Hierarchy filters elements in a dimension based upon definitions around a hierarchy.

h-filter3

As the meta-data (dimension structures) change in your system, the changes will be reflected wherever you use the subset. Download the sample file here.

There are 2 examples in this file.  On tab 1, there is a straightforward hierarchy filter. A Hierarchy filter allows you to filter the subset list based on hierarchical arguments. For example, you can specify to only return elements at a certain level, or elements under a certain parent. Hierarchy Filters are fantastic for when you want to display a particular tree of a specific hierarchy on a report:

h-filter1

On the first tab of the examples, I have basically exposed all the variables on the h-filter. Play around and see how they work. You will notice that some of the variables are values in @Value format. These are Jedox Web variables. They are user and session related variables that you can populate and share between reports. More on those later.

h-filter2

The second tab illustrates the flexibility of Subsets and Dynaranges. The ‘Show Parents Beneath Children”  is manually set on the General tab, but it is not a variable option. You can, however, set a flag in the subset formula that gets generated.

What we are doing here is generating the base subset in the Subset Editor, and then changing the DynaRange type to Formula. We can now modify the formula manually to allow the user to toggle between showing parents above and below the rows:

h-filter5

The finished report now allows the user to toggle between having totals at the bottom at the top of the row elements:

h-filter6

Web Reporting with Dynaranges and Attribute Filters

2 Replies

On the Web, with Dynaranges, you can make your report length determined by how the user interacts with the report. Dynaranges allow you to specify a single row of data and then determine what happens to that row when the user interacts with it in report mode.

It is a powerful Web component because you can create completely customised, dynamic reports that allows users to explore your data without restrictions while keeping a structure of a formatted report.

You can use the following as datasources in a Dynarange:

  • Subsets
  • Formulas
  • Relational Queries

a-filter_1

Dynaranges can be on either columns or rows. This means that both axes of your report can be dynamic. In addition, if you select a subset that contains hierarchical structure, the user can double click to drill down to the next level of the data, and the report will expand automatically.

Relational Dynaranges allow you to write a relational (SQL) query via an ODBC connection and return rows of a single column dynamically. You can then use ODBCDATA() functions to pull out the additional columns you require. The report expands and contracts with the data you are returning.

a-filter_2

Everything in the Dynarange range will be calculated. This means that non-subset values (eg spreadsheet formulas) can be included and the results will be calculated at runtime with the Dynarange.

OK. An example.

Attribute Subset in a Dynarange

Download the example here.

This contains a Dynarange and subset based on attributes in the customer dimension. The user selects an attribute from the Customers dimension in the first combo box. This then filters the second combo box to show all the data values against that attribute name. The user can then filter the elements on the dynarow. If you look at the subset on the dynarow, you can see that the attributes are dynamic and pointing to the results of the combo box:

a-filter_3

This is a handy dynarow style for filtering a sorting through a dimension with many attributes. Note: In the example, the elements returned to the second combo box (the attribute data) may be duplicated as it is displaying a value for each element in the dimension. You can remove duplicates only by writing a macro (that is attached to the combo box) to loop through an array of elements and picking out only the unique ones.

Subsets : Picklists

Leave a reply

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:

picklist1

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:

picklist4

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:

picklist2

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.

picklist3

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);
}

Subsets : Text Filters

2 Replies

 The t-filter (or text filter) is the anti-hero of the filter family. Standing in the shadows while the hierarchy and data filters get covered in all the glory, it is not often required to generate dynamic reports. But it is really helpful in adding utility and usability to your reporting suite.

Within a subset, a text filter allows you to select all elements that match a certain string criteria. You can use it to generate a list of rows or columns, but where it is really powerful is in filtering out elements for a combo box. When you are dealing with a large subset (tens or hundreds of thousands of elements), it is sometimes difficult to give the users anything to navigate effectively through a combo box on the web, in order to find the element they are after.

 t-filter1

Using a text filter, the users can type into a cell the values that they would like to search for in the combo box filters down the elements to that list. It does not just return a straight list; it maintains the hierarchy structure and relationships in the combo box of all elements that satisfy the criteria.

The kicker here is the capability to use the default search wildcards (eg *, ?) or use perl expressions. Perl expressions open opened up for me a big world of new type of filters that I previously unaware of. For example you can search for multiple criteria, exclude criteria, etc.

For example, I want to search for all elements that contain either ‘North’ or ‘East’:

t-filter2

This example is located here. Another example can be found on the web security template.

On the report, you can see i have a toggle for users to switch per expressions on or off as they require.  Although powerful, perl expressions can sometime confuse users, so it is best to have an option to turn it off or on.

Pasting Subsets

Leave a reply

Thomas Arampatzis from the Jedox Academy was in town last week for some training. An interesting point that he touched upon with the students in relation to subsets was how the subset array works. Depending on the orientation and dimensionality of the array, you can retrieve different information from the subset. Below is an example of a simple subset using a hierarchy filter.

Single Column

If you paste out a single column, you return the element name:

paste_ss1

Two Columns

If you paste out 2 columns into the spreadsheet, you get the alias as well. Helpful if you want to show an element code plus description in a report.

paste_ss2

Three Columns

..will give you the hierarchy enumeration (in this case, indent)

paste_ss3

This is very handy if you want to test the row for conditional formatting. You can bold and underline totals, for example.

Four Columns (1 row)

An interesting thing happens when you paste out across 4 columns. The Subset array automatically ‘pivots’ and now shows the levels across the columns:

paste_ss4

Four Columns (2 rows)

Now the process starts again, but this time vertically. By pasting 4 columns by 2 rows , you get the element name and the alias :

paste_ss5

Four Columns (3 rows)

Finally we have completely trans-positioned the data with the indent now included:

paste_ss6

To see how it actually works, try it yourself, or download it and check it out here

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.

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.