7 Replies Latest reply on Jun 17, 2015 1:27 PM by Joe Oppelt

    Running out of space on sheet selector

    Carlos Albuquerque

      I have 8 sheets that need to be hidden and displayed based on a parameter selection. To do so, I used the "sheet selector" technique, hidding the titles on each sheet, and stacking them on a vertical container. My issue is, since i have 8 sheets, it seems that for every sheet there is a "whitespace" that the following sheet obeys and displays below as you see on the screenshot.

      So I am running out of space when displaying my view, since if i open view 8, it displays way off from where view 1 is.

      Any ideas?



        • 1. Re: Running out of space on sheet selector
          Sankarmagesh Rajan

          Hi Carlos,


          Use the horizontal container and try. if you want to increase the height then use exactly option and increase size.



          • 2. Re: Running out of space on sheet selector
            Carlos Albuquerque

            Hi Sankar,

            Changing it to a horizontal container just changes the direction of my problem.

            Now instead of running out of space vertically, I started to run out of space horizontally.

            My hope is to find a way to remove these placeholder whitespaces so that the view can show exactly on top of the other one.

            • 3. Re: Running out of space on sheet selector
              Joey Minix

              These guys demonstrate a technique to do this kind of thing:


              We made a video of Sheet Swapping and Legend/Filter Popping on a dashboard.

              1 of 1 people found this helpful
              • 4. Re: Running out of space on sheet selector
                Joe Oppelt

                Yup.  It's like 8 or 9 points of placeholder space.


                There are some things you can do.


                This is kind of complicated, but it works:  Make the container taller, and have a sheet both above the 8 main sheets and below the main sheets that alternately grow/shrink based on whichever displayed sheet is in effect.  That is to say when the bottom-most sheet of your 8 sheets is being displayed, the top "padding sheet" is the smallest, and the bottom "padding" sheet is the largest, and when the top-most of your 8 sheets is being displayed, the top "padding sheet" is the largest and the bottom padding sheet is smallest.  This will push whichever of the actual data sheets into the same physical space for display within the container.  The hard part about doing this is getting the padding sheets to grow/shrink the proper amount, and to do so in proper coordination with the 8 data sheets.


                To me, it's a very neat way to handle it, but I still haven't mastered how to make it happen without a lot of tedious trial and error.


                What I do when I have more than 4 sheets is to overlay two different containers (same size and position) on the dashboard.  In your case, put 4 in each.  Only one sheet will display still, if you do the swapping filter correctly.  For that matter, you could actually use 8 separate containers if you want all 8 to land precisely in the same position without the 9-point lost space.


                Just a caveat on that idea though.  When you overlay containers and sheets, you can't cursor into them (ditto parameters and filters if that's what you are overlaying) when you are using them in Desktop.  But they function properly when they are published.  There are some threads on this behavior in this forum.

                • 5. Re: Running out of space on sheet selector
                  Carlos Albuquerque

                  Joe, thank you very much for the post.

                  I had seen your video and tried your approach of using multiple containers, but as you said, i gave up when i noticed i could select anything on the charts because of the layout placement. Now that you mention this does not occur when the report is published to the server, than i might keep this approach.

                  Do you have a workbook that demonstrates your first idea?


                  Thank you

                  • 6. Re: Running out of space on sheet selector
                    Joe Oppelt

                    This is an example that I got from a guy named Ville Tyrväinen who hasn't been around lately, but whose creativity I highly admire.

                    In the attached, he uses Sheet21 and Sheet22 as the upper and lower padding sheets.  He changes the size of the two sheets via data from a clipboard.


                    Just look at that dashboard!  It's a work of art.  Every sheet lines up perfectly, and he's swapping 20 sheets in there.

                    Sample workbook is V8.2.  Unhide all the sheets when you are on the dashboard to see all the sheet in use.

                    • 7. Re: Running out of space on sheet selector
                      Joe Oppelt

                      Note also that the example uses the technique of positioning things off the actual dashboard boundaries.  For example the height of the dashboard itself is 600, but the data container is 920, and it starts at -149 (so it extends both above and below the actual visible dashboard itself.  All the padding and placeholder space is kept outside the boundaries of the actual dashboard.)