Multiple selection is often requested by clients and unfortunately, it's not a standard feature of Jedox. But it can be used in reports – in this post I’ll go some tips and tricks for getting multi-select working.
Multi-select can be done by using a listbox instead of a combobox, then using the formula CONCATENATE.RANGE to create a list the subset editor filter can read.
CONCATENATE.RANGE(Value,"|") creates a list that reads like Value1|Value2|Value3 - which is a regular expression. Then it can be used to filter a dynarange.
Multi-Select Filter on Attribute in Dynarange
To see how to get a list of unique attributes, read Filter a Report with a Unique List of Attributes (sesquian.co.nz)
Use a listbox, rather than a combobox to create a dropdown list of attributes.
Make the listbox a named range, then in a cell in the report's hidden area, type the formula =rngNamedRange
Then in another cell, write the CONCATENATE.RANGE formula pointing at that cell, and use the pipe symbol | as the delimiter. This creates a list that is a valid regular expression.
To create the range that will work as the attribute filter, in a cell type the name of the attribute, i.e., Color, then in the cell underneath enter an IF formula that will show the attribute (If the default value is something like “Show All”, you will also need to make sure the cell is blank when that’s selected).
Something like:
=IFERROR(B22,IF(B21="Show All","",B21))
Create the attribute filter in the subset and ensure PERL is ticked.
Now when the report is fired up a user should be able to multi-select:
Multi-Select Filter for an Element in a Dynarange
Filtering on elements isn’t as straightforward as attributes, what we're able to do depends on how the report is set up. The text or picklist filter will both work, finding out what works for your report can involve experimenting a bit.
Two examples below
Nested dynarange filtered by product element:
For this example, my outer dynarange lists products at the level in the hierarchy directly under All Products. Inner dynarange uses the outer dynarange as a hierarchy filter and shows products directly below.
Create a multi-select list of products showing All Products and everything directly below in the hierarchy. Same as with the attribute filter, used the CONCATENATE.RANGE formula to create a list delimited by |. Then use an IF formula on the spreadsheet to set the filter blank when All Products is selected.
Value | Formula |
Mountain Bikes | =lstProduct |
Handlebars|Touring Bikes|Mountain Bikes | =CONCATENATE.RANGE(B11,"|") |
Handlebars|Touring Bikes|Mountain Bikes | =IFERROR(B12,IF(B11="All Products","",B11)) |
Make the filter a named range, then feed it into the text filter in the subset editor for the outer dynarange.
When the reports fired up, a user can see all the products they've selected.
Example using picklist filter:
The range created by the multi-select listbox can be fed straight into a picklist filter (i.e., concatenate.range formula is not required).
User will be able to multi-select products.
That's all, thanks for reading.