4 Replies Latest reply on May 16, 2013 7:34 AM by Victor Pham

    Is there a way to replace an empty worksheet with another worksheet?

    Victor Pham

      Hi all,

             We have a dashboard report that contains 20 worksheets lined up vertically in a row. Each row is a metric that our department managers are tracking. However, some department don't have data for certain metrics. Each metrics is its own worksheet so whenever you run the report for a department that doesn't have data for that metric, you will get a report with rows of white space. I would like to replace the white space area with another worksheet that say something like "No data available" or something like that. We need to do this in some sort of filter/calculation because the end result will be a .pdf that is printed and posted. Is that possible?  We're currently on Tableau version 8. Thanks.

        • 1. Re: Is there a way to replace an empty worksheet with another worksheet?
          Mark Holtz

          Hello Victor,

           

          Can you share a packaged workbook? Without seeing what you're dealing with, it's tough to assist.

           

          I suspect you could benefit from utilizing a parameter + calculated field to "filter" sheets.  So I believe you could possibly try that.

           

          And in my unsolicited opinion, Tableau typically performs better when you change the dimensions/measures on a view, rather than swap entire views. This is a thread related to swapping views using a parameter:

          http://community.tableau.com/message/209845

          • 2. Re: Is there a way to replace an empty worksheet with another worksheet?
            Victor Pham

            Hi Mark,

                    Thank you for replying. My workbook contains sensitive data so I can't share it out and the data is way too complex for me to replicate fake data. I'm attaching a screenshot instead to show you what I mean. In the "All Rows" image, I have a manager who is tracking all metrics listed so when her report is generated, it will show all rows. I then have a manager that only track four of the 7 metrics so I now have white space in place of the metrics that don't exist for the second manager. The rows and worksheets are there but it is blank because the manager doesn't have data for that metric. What I would like to do is replace those empty rows with a message. Like "data not available". It has to be done calculation so that it triggers whenever a userid is passed in. I'm wondering if this is possible. The example you linked looks like some manual step has to be taken by the user.

            • 3. Re: Is there a way to replace an empty worksheet with another worksheet?
              Mark Holtz

              Hi Victor,

               

              From the look of these views, it doesn't jump out at me why you had to create different views for each row. I see most of the metrics are formatted as numbers except for 1 row. If that is absolutely necessary, you could get around it by creating calculated fields that convert the numbers you're displaying to text fields and concatenating the values when Metric = 'Limb Restraint' with a '%'. I believe you could create 4 views with "Metric" on the rows shelf in each view.

               

              The first view would contain the Metric name, LY Actual CP, Target, Mar-2013, and Diff.

              The second view would contain Metric name (hide Header so column does not display again, but is ordered the same as the first view), and YTD rate colored by whatever colors that field.

              The third view would contain Metric name (hidden and sorted the same) and the line graph for Trend Last 12 Months.

              The fourth view would contain Metric name (hidden and sorted the same) and the value you're using to derive the arrows.

               

              You could add a field in front of the Metric names to split out your "Safety & Quality" or "Service" metrics. Or, you could create a new set of 4 views to do that.

               

              This way, when you have a new value in the Metric name, it will just display under the previous row, but when you do not (i.e., when filtering for a specific manager) you can avoid the empty space.

               

              If you can't do that currently, I'd suggest you look at re-arranging your data so that you can.

               

              Another possible method, and the one I alluded to first, is do set up a parameter of which manager you're looking at. You said you didn't want anything to be manual, but I don't see any way for the user to select the manager to view, so that at least seems like something you'll have the user do. Then, on each view, create a calculated field for each measure to use as a filter like:

              IF [Manager parameter] = 'Manager 1' THEN 'Show'
              ELSEIF [Manager parameter] = 'Manager 2' THEN 'Hide'
              ELSEIF [Manager parameter] = 'Manager 3 THEN 'Show'
              ELSE 'Hide' END. 

              You'd create a separate filtering field for each view and set only the appropriate metric to 'Show' . The key here is to stick them all inside a vertical or horizontal container on your dashboard and let the auto-fitting collapse "empty" views.

              Hope that helps.

              • 4. Re: Is there a way to replace an empty worksheet with another worksheet?
                Victor Pham

                Hi Mark,

                     Thank you for the response. I do appreciate the effort. Just to give you a bit of a background, that report is based off of 1 table. Inside that reporting table, it contains about 80 metrics that comes from 20 to 30 different data sources. Inside our reporting table, we have already done some pre-calculations so that we only need to apply a simple layer of business logic to display what you see in the snapshot I uploaded. The problem for us is that each metric has it's own business logic. The formula to generate the target column for "Fall" is different than the formula to generate the target for "Limb Restraint" for example. That is one of the reasons why we needed separate the metrics out into it's own worksheet. The only way we can do what you are suggesting is to calculate the data all the way to the very end, format the result as a number or percent to the decimal position that we want and then store the data as a string. The issue with that is we have to cut the data in many different ways for the user request. As requests comes in, we would write additional code on the back-end to generate the results for the different views users would want. That is very time consuming and not feasible as we continue to add more and more metrics. Using our current method, we allow Tableau to calculate the result for us and then we just use filters to display the data in the different views the user request.