In this blog post, I aim to demonstrate a simple yet effective technique to display the members returned by a set as a list in Excel. This serves as a quick and efficient method for analysing the results from your static or MDX-based sets.
This approach offers an alternative to using the TM1ELLIST function in Planning Analytics for Excel (PAfE), which also returns an array of members based on MDX expression or a set. However, the TM1ELLIST function requires the user to manually enter the formula to view the results and it does not support hierarchies.
When you add a SUBNM or TM1Set function in PAfE, you may have an automatic addition of data validation to the cell, allowing for selection from a dropdown list.
Upon navigating to the Data Validation option in Excel, you will discover a hidden named range called “cafe_validation_temp” added under the “Source” field.
This is a generic named range that is applied to all cells containing SUBNM and TM1SET functions.
The key to this technique is to reference the “cafe_validation_temp” named range in a cell that triggers automatic display of members as a spilled list in Dynamic Array compatible excel worksheets.
Doing so will automatically display the members of the active cell that contains SUBNM or TM1SET functions. As you select a different cell containing these functions, the list will automatically update to show the members returned from those functions.
Furthermore, as TM1SET is hierarchy-aware, it seamlessly returns results from hierarchies, enhancing the flexibility and depth of analysis.
For training and support please feel free to email media@octanesolutions.com.au