Learn to use XL3SetProperty in a report.
This example uses a local cube, which holds a measure of value against Products and Regions across time. You need a report that lets the user choose which hierarchies are on rows and then how much detail they want. For example, they want to know the members, children, or descendants of that hierarchy.
You want the user to be able to run the report with either Product or Region on rows. The slicer's choices must change accordingly. If the report runs with Regions on rows, the slicer's choices must only be based on Region. If the report runs with Product on rows, the slicer must only offer Product choices.
The report is shown below:
- Create slicers to change hierarchies and the level of detail
- Create a slicer that'll enable the user to select which hierarchy appears on rows.
- Choose the range K18:L19 as the slicer's source range.
- Update L31 with the value in the first column.
- Create a slicer that enables the user to select a particular region. This will put the user's selection in L29.
- Create a slicer that'll enable the user to select a product.
- Create a slicer that enables the user to select the member level — for example, member, children, or descendants.
- Choose L13:L15 as the source and update L30 with the selection
- Name the object you want to use XL3SetProperty with. For example, the grid has the title GridToSet in the grid properties screen.
- Add selections in the grid for the Region and Product slicers. The selections are then updated by the XL3SetProperty formula.There are several XL3SSetFormula Property statements at the bottom right of the example, updating properties MemberSelectionType, HierarchiesOnRows, and HierarchiesOnHeaders.
- Set MembSelType – the two cells to the right have the XL3SetProperty formulas:
- Cell L21, passes Region as an argument when the user has selected this option from the first slicer:
=XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Region]",IF(L31="[Region]", $L$30, "Member")) - Cell M21, passes Product as an argument when the user has selected this option from the first slicer:
=XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Product]",IF(L31="[Product]", $L$30, "Member"))
- Cell L21, passes Region as an argument when the user has selected this option from the first slicer:
- Set Rows – the cell to the right has the XL3SetProperty formula. It passes the value of L31 as an argument for the hierarchy that should appear on rows of the grid.
=XL3SetProperty("Grid","GridToSet","HierarchiesonRows",$L$31) - Set Header – the cell to the right holds XL3SetProperty formula. It passes the values of cells L32 and L33 as the hierarchies on the headers of the grid:
=XL3SetProperty("Grid","GridToSet","HierarchiesOnHeaders",$L$32,$L$33)
- Set MembSelType – the two cells to the right have the XL3SetProperty formulas:
- L32 contains an IF function to swap the values of Rows and Headers:
=IF(L31="[Product]","[Region]","[Product]")
This allows you to display either Region or Product on rows as required.
