Let us know how we can make Jedox even better!
For every cube rule based on a list of values, allow a user to specify either the immediate children or the base level children of each parent dimension member in the cube slice to define the entire list of values. The current alternative is to force a hard-coded list of values into the cube rule, which is generally not realistic or feasible. The primary use case for this functionality would be in existing statistical cube rule functions, such as MIN, MAX, COUNT, FIRST, LAST, MEDIAN, AVERAGE, SUM, ES and PERCENTILE, all of which generally require a large list of values. A further use case would be using this subset of cube values functionality as the foundation for additional statistical cube rule functions, such as STDEV, RANK, and a whole host of others. For more possibilities, see those listed for Excel here:
Probably the best way to implement this suggestion is to migrate the Jedox spreadsheet supporting functions PALO.COORD() and PALO.EXPANDTYPE() to be arguments in existing and future statistical cube rule functions. These functions could be inserted into any of the above cube rule functions in place of the list of values. Currently, these cube rule functions are effectively useless in the real world, since the list of values in these cube rule functions will almost always be very large and constantly variable.
Another way to implement this suggestion is simply to migrate the various statistical Jedox spreadsheet functions ("PALO.DATA.????) as is to cube rule functions. For example, see the spreadsheet function for PALO.DATA.AVG(), which also explains how the two supporting functions PALO.COORD() and PALO.EXPANDTYPE() are used:
https://knowledgebase.jedox.com/knowledgebase/jedox-olap-functions/#PALO.DATA.AVG
Unfortunately, there are only five of these Jedox spreadsheet functions (PALO.DATA.AVG/CNT/MAX/MIN/SUM), so this latter approach would still fall short of fixing the problem for all the existing statistical cube functions, let alone the many more possible statistical functions.
Thanks for the idea. You can now achieve something similar in 2021.4 by using subsets in rules. Using a (stored) subset within a cell reference in a rule will produce an array of values that could be used in those statistical functions, for instance. In those subsets, hierarchy filters can be set appropriately to achieve the functionality of PALO.EXPANDTYPE.