topic How to create a bookmark that does not affect current selections - is it possible? in App Development

How to create a bookmark that does not affect current selections - is it possible?

mikegrattan — Thu, 12 Dec 2024 22:09:56 GMT

Here's the scenario:

I am creating multiple bookmarks for the same sheet; the only difference for each bookmark is that the layout is changed for a pivot table and the bookmarks are meant to be an easy way for users to get the drill-down order they desire for the pivot table.

Because the user may have already selected other filters, I do not want those filters to be cleared when they select a bookmark to change the layout of the pivot table. Is it possible to do this?

Re: How to create a bookmark that does not affect current selections - is it possible?

nhenckel — Fri, 13 Dec 2024 04:43:47 GMT

To my knowledge it’s not possible to exclude selections from bookmarks. However, an alternative solution is to use variables to control the pivot table's layout. Instead of relying on bookmarks, users can select a scenario from a variable dropdown, which dynamically updates the pivot table dimensions.

Here’s how this can be achieved:

Variable Dropdown:
Use a variable dropdown selector in the UI to let users choose a scenario. The dropdown can include options like "scenario1" "scenario2," and "scenario3."
Dynamic Dimension Expression:
Use the following expression in the pivot table's dimension to update its "order" based on the selected scenario:
=pick(Match('$(var)','scenario1','scenario2','scenario3'),Dim1,Dim2,Dim3)
- Replace Dim1, Dim2, and Dim3 with the actual dimensions you want for each scenario.
- The Match function evaluates the selected scenario, and the pick function dynamically assigns the dimension.

This approach ensures:

User selections remain unaffected.
The pivot table layout adjusts dynamically based on the scenario selected in the dropdown.

Re: How to create a bookmark that does not affect current selections - is it possible?

mikegrattan — Fri, 13 Dec 2024 21:59:01 GMT

Thank you for this suggestion; it looks very promising. I'm struggling with it a bit, however, and have some questions. Here's what I've done so far:

Created a variable called vCustLevel
Created a variable drop-down with multiple choices (see screen shot)
Edited the Row dimensions with just a couple of choices for testing - sample formula for first dimension is
=pick(Match('$(vCustLevel)','ParentCust','ShipTo','Salesperson','ItemGroup','PriceSegment','Commodity','OutsideFlexReason'),ParentCust,SoldTo,ShipTo,Item)

Based on my understanding, I should be able to choose ParentCust from the drop-down, and the dimensions should dynamically be selected as ParentCust,SoldTo,ShipTo,Item. It's not working quite that way, however, and it only shows ParentCust and ShipTo when I drill down.

I have added a second dimension with the following formula:
=pick(Match('$(vCustLevel)','ParentCust','ShipTo','Salesperson','ItemGroup','PriceSegment','Commodity','OutsideFlexReason'),ShipTo,Commodity,Item) - this is intended to have a drilldown for ShipTo, Commodity, and Item, when vCustLeve = ShipTo.

The result of selecting ShipTo, however, only has a drilldown for ShipTo and Commodity, but not Item. I'm guessing I have to keep adding the dimensions to the pivot table with formulas for every scenario, but I want to make sure I'm on the right track.

Thanks!

Re: How to create a bookmark that does not affect current selections - is it possible?

nhenckel — Sat, 14 Dec 2024 16:57:45 GMT

It looks like the issue with your current setup is that the number of options in the Match function doesn’t align with the number of dimensions in the Pick function. For Pick to work correctly, the index returned by Match must correspond directly to the dimensions in Pick. Each scenario in Match needs a matching dimension in Pick. Also, since you’re building a drilldown, you’ll need separate Pick expressions for each dimension in the pivot table to handle the hierarchy properly.

Alternative Approach: Instead of dynamically building the pivot table dimensions with Pick and Match, you could simplify the process by creating a separate pivot table for each dropdown option. Place these pivot tables in a container object and use the Show Condition property on the container to display the correct pivot table based on the selected dropdown value. This avoids the complexity of writing Pick and Match expressions and lets you design each pivot table exactly how you want.

Re: How to create a bookmark that does not affect current selections - is it possible?

mikegrattan — Mon, 16 Dec 2024 15:40:09 GMT

Thanks for the additional info. I'll continue to play around with it some more; I think both approaches could work, but it looks like the Pick and Match approach has an unexpected side-effect...the drill-downs will probably work when set up correctly, but the dimensions aren't represented in the correct order in the Rows selection area, so that could be confusing for users.

I think I'll probably end up going with the alternative approach, since that will likely give the users a more organic experience.