1 Reply Latest reply on Oct 26, 2017 5:45 PM by Tyler Garrett

    A Dynamic Parameter using Union All *womp*, No more manual updated parameters :-)

    Tyler Garrett

      Hello, it's just a dynamic parameter - and might work for you. Or you can call it a work-around to many to many in Tableau.





      A lot of peeps and businesses have a lot of data, lots of sources, and then eventually you want a single filter.


      What happens when you need 20+ data sources? Do we call it a monster workbook and then run away?


      After you hit 10-20-30 data sources, things slow down, and this speeds it up every time. Like a glove. I'm excited to drop it here.


      I've used this to clean up manual parameter updates - and also consolidated 10,20,30,50...+ data sources. Into one single extract. (micro process it in your database, and frontend excel stacking SQL is below!)


      Dynamic parameter and Solution to TOO MANY DATA SOURCES.


      A CIO asked, "why doesn't it have multiple checkboxes.... Like it did in the DEMO... (i didnt do the demo )"


      In Tableau Desktop V8... I didn't have an answer to why parameters didn't have multiple checkboxes if the answer was MANY TO MANY isn't working. Until we whiteboarded... But of course... I tried to explain it to him right away.. lol


      I tried to explain, "... many to many, vs one to many..."


      "make it work, or this doesn't work."


      Okay here's the solution and powerpoint slide to help sell it to the business. SQL on bottom. Excel/csv syntax.


      Seems to be one of my favorite tools too.

      Screen Shot 2017-10-23 at 7.47.57 PM.png

      This removes manually fixing parameters that are used to filter.


      If your parameters need to be updated, please stop using that method.


      If you need to ever manually change a parameter... please stop doing that. And start utilizing this below.

      Screen Shot 2017-10-23 at 7.48.07 PM.png


      This solution also allows you to be able to offer stacked bar chart and multiple lines.


      Work to dimensionalize the lines using the same column.


      Here's the presentation if you need to 'sell it' as a solution.

      Screen Shot 2017-10-23 at 8.04.17 PM.png


      I've implemented this across 30+ Tableau environments.


      Please consider micro processing 'every stack' - utilize the database for what it's designed to do.


      Processing loads. Then select * your dimensionalized data.

      Screen Shot 2017-10-23 at 7.58.34 PM.png




      Screen Shot 2017-10-23 at 7.58.57 PM.png

      Report flag is helpful.


      This solution was almost generated in the product with the Version 9 Union All.


      I believe this needs to be implemented directly into the product. Because of how helpful it is to have a single extract from a filtering perspective VS offering global filters that don't optimize singular extract jobs, singular tables, singular micro processed stacks of data, and instantly queried back to use in Tableau Desktop.


      Screen Shot 2017-10-23 at 8.03.16 PM.png


      ExcelSQL to utilize.

      Select   "Employee" as [ReportFlag],

        [Employee$].[Name] as [Employee Name],

        [Employee$].[PhoneNumber] as [PhoneNumber],

        [Employee$].[Region] as [Region],

        [Employee$].[Department] as [Department],

        0 as [Sales],

        0 as [Profit],

        "" as [ItemID],

        "" as [WarehouseLocation],

        0 as [Cost]

      From   [Employee$]

      union all

      Select   "Revenue" as [ReportFlag],

        "" as [Employee Name],

        "" as [PhoneNumber],

        [Revenue$].[Region] as [Region],

        [Revenue$].[Department] as [Department],

        [Revenue$].[Sales] as [Sales],

        [Revenue$].[Profit] as [Profit],

        "" as [ItemID],

        "" as [WarehouseLocation],

        0 as [Cost] 

      From   [Revenue$]

      union all

      Select   "Inventory" as [ReportFlag],

        "" as [Employee Name],

        "" as [PhoneNumber],

        [Inventory$].[Region] as [Region],

        [Inventory$].[Department] as [Department],

        0 as [Sales],

        0 as [Profit],

        [Inventory$].[ItemID] as [ItemID],

        [Inventory$].[WarehouseLocation] as [WarehouseLocation],

        [Inventory$].[Cost] as [Cost]

      From   [Inventory$]





      It's the key to a lot of ideas. Hope it helps you too.



      Tyler Garrett