7 Replies Latest reply on Mar 22, 2019 7:03 AM by Matt Voda

    Show/Hide Sheet Based on Calculated Field ?

    John Taylor

      So I'm familiar with the Show/Hide sheet function using a parameter to toggle the view, but what about showing and hiding a sheet based on the result of a calculated field?


      I'm trying to figure out a way to hide or show a parameter based on the result of a calculated field. A possible workaround in my mind is to show or hide a floating blank worksheet over the parameter. Super silly.


      For the calculated field, I'm setting it to 1 or 0 based on the condition. I'm using this as a filter, and I want the blank sheet to cover the parameter if it's 0, and the parameter to be visible if it's 1.


      It doesn't seem to work like I had hoped. Is this something that can only be achieved using js?

        • 2. Re: Show/Hide Sheet Based on Calculated Field ?
          Joe Oppelt

          John -- Yes, you can do that.


          Do you have a sample workbook I can use so I can show you a way?  (It's way easier to show you than to describe it.)  Hack up a superstore example similar to your current workbook if you don't want to share your actual workbook.

          • 3. Re: Show/Hide Sheet Based on Calculated Field ?
            John Taylor


            Joe Oppelt

            I've attached an example workbook with a small explanation in there. Thanks for providing assistance, please let me know if I need to clarify any part.


            Thank you

            • 4. Re: Show/Hide Sheet Based on Calculated Field ?
              Joe Oppelt

              In the attached, I floated your param, and replaced it with a BLANK object to hold the same spacing.


              then I created Sheet 4 based on your [Ship Mode Calc].  I now see that I did the selection backwards.  I expose the param when the calc is 0, but you can swap that...

              Basically, right now the sheet displays something when ship mode calc is 0, and goes blank when it is 1.

              And I use that behavior to "push" the param right or left based on the condition.


              On the dashboard, select Ship Mode = first class.  Watch what the param does.  Select something else.  (BTW, if you have multiple ship modes selected, the calc evaluates to 0.  You probably know that, but I though I would point this out.)


              the movement occurs because I have sheet 4 in a floating container, and behind it I dragged the param.  When the value is 0 and the sheet displays, it pushes the param to the right.  When the value is 1, sheet 4 goes blank, and the param shifts to occupy he space that the sheet previously occupied.


              This is called "popping".

              Go to Dashboard 2.  Here I positioned the floating container so that it starts in a negative x-coordinate.  you'll never see sheet 4.  It's outside the boundaries of the dashboard (Though, if you click "presentation mode" you'll see that activity in Desktop, though the user will never see it on Server.)  When the condition is right, Sheet 4 exposes, and pushes the param into view.

              2 of 2 people found this helpful
              • 5. Re: Show/Hide Sheet Based on Calculated Field ?
                Joe Oppelt

                PS:  Some formatting I did on Sheet 4:


                I made the display color white.  If that sheet ever gets positioned so that some of it actually displays on the dashboard, the user will never see it.  Also I formatted it so that there are no row or column borders.  Essentially the sheet is invisible this way.

                It really doesn't matter what you actually display in this sheet.  What matters is that you set it up to display or not based on a condition, such as the condition you created in your calc.  You just have to make it big enough to push whatever object you want to pop out.  In this example the object is a param, but it can be an entire sheet (which I do all over the place) or even another container with multiple sheets and text objects and other formatting.


                I even pop out images and web objects in some of my workbooks.  Once you get popping to work, the design possibilities are only limited by your imagination! 

                • 6. Re: Show/Hide Sheet Based on Calculated Field ?
                  John Taylor

                  That is great Joe. Thanks again I appreciate the time spent helping me. I'll return the favor to another user in training when I see the opportunity

                  • 7. Re: Show/Hide Sheet Based on Calculated Field ?
                    Matt Voda

                    You're a legend, Joe