Listboxes are an essential feature in Jedox, a powerful business intelligence and performance management solution. They allow users to select multiple elements simultaneously, enabling dynamic analysis and reporting. This article will explore how multi-selected elements from Listbox can be effectively used in dynaranges, subsets, and Palo formulas. We will also demonstrate how formulas can automatically sum the values from the selected elements, providing enhanced functionality for data analysis.
Understanding Multi-Selection Listboxes
Listboxes in Jedox provide a dropdown list of elements that users can choose from. By enabling the multi-selection feature, users can select multiple elements simultaneously, offering flexibility in data analysis. This allows for creating dynamic reports and calculations based on specific selections.
We will present 3 use cases where you can see how Multi-Selection elements can be utilized:
Leveraging Palo Formulas for Automatic Summation
Palo is the powerful multidimensional database engine that underlies Jedox. With multi-selected elements from a Listbox, you can enhance Palo formulas to automatically sum the values from the selected elements. This functionality streamlines calculations and reduces manual effort, providing accurate and efficient results.
In order that data is the sum of chosen elements from Customers and Year we need to use PALO.DATAV function. SUM and PALO.DATAV will be sufficient for one element chosen from the list box. If we want to add an additional filter for Years, the TRANSPOSE function should be written.
Enhancing Dynaranges with Multi-Selected Elements
Dynaranges in Jedox are dynamic ranges that can adapt to changes in data or user selections. By incorporating multi-selected elements from a Listbox, you can create dynamic dynaranges that automatically update based on the chosen elements.
In Dynarange subset, we will choose the Picklist filter and then choose the listbox variable. It is important to set the “As preselection for the subset” behaviour.
Creating Nested Dynaranges with Multi-Selected Elements
To further enhance the flexibility and granularity of data analysis, Jedox allows for the creation of nested dynaranges using multi-selected elements from Listbox. With this approach, users can drill down into specific subsets of data by selecting multiple elements from the Listbox. These selected elements can then be used to dynamically filter and organize the data within nested dynaranges.
Utilizing multi-selection elements from Listbox in Jedox opens up a world of possibilities for dynamic analysis, reporting, and calculations. By incorporating these elements into dynaranges, subsets, and Palo formulas, users can create flexible and interactive reports that adapt to their specific needs. The automatic summation feature further enhances the efficiency of data analysis, allowing for quick and accurate calculations.
Examples can be downloaded here. Just a reminder that you need to have a BikerBest Database first.
You can also use lists within an attribute filter of a subset to retain elements matching several values of an attribute:
– set “listName” as name of the list
– pick a cell (e.g. E7) and type the formula “=listName”
– pick another cell (e.g. E8) name it “rngAFilter” and type the formula ‘={“AttributeName”;IFERROR(CONCATENATE.RANGE(“|”,E7),E7)}’
– in the subset editor let the named range “rngAFilter” be the variable for the attribute filter
– do not forget to set “PERL regular expressions” to true
Thanks for your contribution Felix.
Love it you genius!!
Hi!
Is it possible to do these calculations (specially tha palo.data application) with more than 2 lists? Everything looks good until I add the third list. Then, my function returns #VALUE.
Thanks!
Hi ENrique,
Unfortunately, there is hard limitation on 2 lists for time being. What is your use case? Maybe there is a workaround.
I’m just trying to create a report with 4/5 parameters in a multi list, and the idea would be to have the capabilities to retrieve a cube cell based on the selections, without the need of an explicit display of the selections in a dynarange to get those numbers.
Thanks anyway!
Unfortunately, this will be difficult. But maybe you can check on the Lists : https://knowledgebase.jedox.com/jedox/in-memory-db/lists.htm?Highlight=lists