4 Replies Latest reply on Nov 30, 2011 10:02 AM by Austin Dahl

    Dynamically adding complex sets of filters to a report?

    Steve Mazer

      Summary: I am trying to find out if it’s possible to create a complex report that allows for the “run-time” addition of dynamically chosen sets of data filters, with Boolean logic between the filters. I think I may be trying to use a hammer to drive a screw, but I could be wrong.

       

      Details and Background: My company has been using Tableau for over a year now, and we have used it to produce many different static and reports. Recently, the company decided that they would like to explore the expansion of our Tableau use; they are hoping to replace some older data gathering and reporting products with Tableau.

       

      One application that we are looking at to replace with Tableau is a legacy marketing application that is used by a 6 people to filter data and produce lists. The application actually does much more, but list-building is the only thing that it is used for now, and the annual licensing fees are somewhat expensive.

       

      The way this application works is this: It begins by showing you the count of ALL the rows of data from a fact table joined with some related dimension tables in a SQL Server data mart. Then, based on the distinct and/or aggregated data in the columns on the dimension tables, filters can be applied. A filter can be simple, such as one that has a single condition:All people between 30 and 34

       

      A filter could also be more complex. It could be a group of conditions:

      Filter Group


      All people between 30 and 34


      All people who live in a Seattle, WA


      All people who have a net household income range of $50K – $65K


       

      A filter could be much more complex, and could include several sets of grouped conditions, with Boolean logic between the groups:

      Filter Group 1


      All people between 30 and 34


      All people who live in a Seattle, WA


      All people who have a net household income range of $50K – $65K


      OR

      Filter Group 2


      All people between 45 and 49


      All people who live in Oregon


      All people who have a net household income range of $50K – $65K


      NOT

      Filter Group 3


      People who have bought less than 4 products


      People who have been contacted inside of the last 12 months


      People who have purchased product XYZ from partner ABC


       

      Users of this application can add a single filter (simple or complex), or they can add as many  levels of filters as they would like, kind of the same way that you add levels of sorting in Excel 2007 or newer. After each level of filter is added, the application shows a summary of the filtered conditions and how many rows are left in the result set. For example, after one filter is added, the number of rows might be reduced from 38 million to 250,000. After a second filter level is added, the number of rows might be further reduced to 78,000. After a third filter level is added, this data set might be further reduced to 43,000. When they are done filtering the data to their liking, they export the data to Excel, and use the data for marketing campaigns.

       

      We had hoped that we may be able to build a Tableau report and distribute it to the group on our Tableau Server to replace the functionality of our application, but I don’t think that Tableau will work in this case, since I think that the filtering requirements are too complex. The only way that I can see us using Tableau to replace this application is to buy Tableau licenses for each of the people who currently use the marketing application, train each of them to use Tableau, and then let each of them use Tableau to connect to the data warehouse to generate their own reports. This isn’t necessarily a bad solution, but the benefits out way the costs, and I think that the existing application is much better suited to adding levels of complex filters than Tableau is.

       

      I haven’t done a tremendous amount of report building with Tableau. If someone could explain/show me how I could build reports where I could dynamically add levels of filters that contained groups of conditions (with Boolean logic between the groups), I would really appreciate it. Or if someone could just tell me that Tableau is the wrong tool for this job, that would be fine as well.

        • 1. Re: Dynamically adding complex sets of filters to a report?
          Joe Mako

          If you don't give them desktop, you will need to define all available options with parameters when creating the workbook, or at least make them type in parameters. Too many parameters can cause slowness for interaction, but it sounds like this is a filter and export, so that may not be an issue. It would not be as many levels of filters as they like, only the amount that you allow for when creating the workbook. While it is be doable to create a limited application for Tableau Reader, Tableau Desktop would be the optional tool in my opinion for this task of filtering data any possible way and then seeing the results.

           

          If you can provide a sample packaged workbook, and example can be created for you.

          • 2. Re: Dynamically adding complex sets of filters to a report?
            Steve Mazer

            Thanks Joe. The problem is that the users want to be able to access any column for filtering (over 100 of them), which I think could get to be a be unwieldy to build and maintain in Tableau. Also, the business isn't currently interested in deploying more licenses for Tableau Desktop -- they would like to have the analysts produce dynamic reports for deployment to the end users on our Tableau Server.

             

            After some further internal discussion, we've decided on the status quo, and will revisit this issue later on. But I really appreciate your feedback (once again).

            • 3. Re: Dynamically adding complex sets of filters to a report?
              johan koopmans

              Hi Steve,

               

              I have a similar case. Though it is easy in tableau to dynamically change your filters without slowing down . In here

              http://www.thedatastudio.co.uk/blog/the-data-studio-blog/andy-cotgreave/user-built-views you see the example and how-to. I mimicked it succesfully giving the versatility of a single dashboard a great push.

               

               

              I don't know whether a user can build sets on the fly when they purely interact with the tableau server in the browser.

               

              Building sets, anyway, with any tool, can be hazarduous and prone to error. Though it would be nice if one could do it once approaching the workbook over the intranet.

              • 4. Re: Dynamically adding complex sets of filters to a report?
                Austin Dahl

                You can alter the filters on the fly in Tableau Server.  Take a look at http://downloads.tableausoftware.com/quickstart/server-guides/server_admin6.1.pdf on page 78 (JavaScript API) and you will see a small example of how you set filters as part of a URL.  The example in the documentation is no where near as complex as Steve has laid out.  The boolean operations between the sets is going to be tricky with just filters.

                 

                If I was going to do this, I'd probably generate calculations for Tableau Desktop and use a lot of parameters like Joe suggests.