Filter slicing
You can slice hierarchical filters, so they only contain items specified in list of leaf level members.
For example, suppose you have a spreadsheet with data relating to departments and products. Users first filter the spreadsheet based on department, but they want to further filter the spreadsheet on products and product groups. They want the Product filter to contain only the product hierarchies for the products in the spreadsheet, not all products in the Product dimension.
To achieve this, you need to call the SetLeafLevelConstraints(…) and specify a list of product ids in the Load Data action of the filter.
Example
- Create a Workbook variable to store the product ids:
- Create a JavaScript which returns a list of product ids:
- In the spreadsheet calculations, call the JavaScript and assign the result to the Workbook variable:
- In the Load Data action of the (Product) filter, call SetLeafLevelConstraint(…) and pass in the variable.
Note that you need to call Load Data on the filter AFTER the spreadsheet has been loaded, or else the list of product ids has not been created.
