2 Replies Latest reply on Jul 30, 2018 1:29 PM by Bryce Larsen

    NEXT button in a Single Sheet Drilldown

    Bryce Larsen

      Hello! I'm hoping someone out there can figure this out... I've copied methodology from Rody Zakovich (h/t #WorkoutWednesday) and have everything working as planned, except I want to add one more thing: a next button.


      I've made a mock dashboard that people can take and run with that has a simple dataset:

      • State
      • County
      • City


      Clicking on one will drill into the other and so on. What I want to be able to do is allow the user to click on another sheet (forward arrow) that will simply take them to the next level while including what they've already filtered on. Eg. after drilling into "Massachusetts" you see all the Counties. Clicking the 'next' sheet will move them into All cities within Massachusetts.


      Hopefully this makes sense! I've actually gotten it to work (mostly, I think) when you do 'next' once, but unsure if user clicked 'next' twice - which they should be allowed to do.


      I've attached a 10.3 twbx here. Thank you!

        • 1. Re: NEXT button in a Single Sheet Drilldown
          Bryce Larsen

          UPDATE: originally I thought I had it working - and it does for the first 'Next'.


          I needed to change one of the fields it was joining on, but, most importantly, the action filter was on Show All Values instead of Leave the Filter.


          Main issue: once you click 'Next' once, there are multiple 'Current Levels'. In this example, clicking next from the start makes the Current Level become both "Massachusetts" and "Rhode Island". As such, when you click it again, it will pass the first one alphabetically (Massachusetts in this case).


          This is not the desired behavior. I would hope clicking it again would make the 3rd level/view show ALL cities within the data (for both states).


          Any ideas?

          • 2. Re: NEXT button in a Single Sheet Drilldown
            Bryce Larsen

            To demonstrate:

            Main view:

            Next button passes both states (great!):

            Then Next button only passes first value that appears alphabetically (only Massachusetts is passed, hard to see, but trust me! Heh):


            I understand why this is happening ('Current Level DSC' is on detail pane and it's stacked upon itself in the button biz), but curious of a way to pass all rows.


            One solution is multiple sheets that uses a WINDOW calc to determine whether or not to show and each sheet links on something else, or perhaps a LOD expression. However, the former is more work to maintain in my real workbook and the latter can be memory intensive and affect performance. Any suggestions appreciated!