2 Replies Latest reply on May 23, 2018 3:11 AM by Filip Pecsérke

    Custom SQL query using a parameter to define a column name

    Jack Powell

      Hi everyone,

       

      I'm working on a viz where the customer has requested an open item analysis, backlog, or queue (depending on what you'd like to call it).

       

      We have multiple states which our rows will go through Open, Analysis, Closed, and fixed. I use a Custom SQL approach to add additional rows to our database which updates the rows every time a change occurs but keeps a history using fields such as: dayopened, dayanalysis, dayclosed, and dayfixed. Using the Custom SQL, I'm able to create field called "Action Type" which stores the state of the current row and maps the corresponding "day****" field to a singular "day" field. This is explained in more detail here if you're interested (I'm trying to keep this light as this isn't my actual question):

      Queues and Utilization in Tableau: Part 1 – Queues « Data Driven: Data Analytics, Dashboard Design

       

      Based on that our customer would like to see the negative portion of the graph as either Closed or Fixed states which is easy enough to do, in theory. I'm trying to manipulate which field tableau selects to map to the joint "day" field based on a Parameter that switches between "dayfixed" and "dayclosed" but am receiving an error. The error states that there's no such column in the table named "dayfixed" when using the parameter, but when manually putting "dayfixed" in it works perfectly fine.

       

      Any ideas or suggestions?

       

      -Jack

        • 1. Re: Custom SQL query using a parameter to define a column name
          alex macharia

          Hi,

          make sure they match-e.g lowercase and no spaces.

          Also, try to a calculated field for the parameter to the filter panel and type dayfixed and click the + button to add in the custom value list.

          If this does not work, then it has to do with the data source

          • 2. Re: Custom SQL query using a parameter to define a column name
            Filip Pecsérke

            Hello,

            I have the same problem. It seems that when you put the parameter in, it is a string value stored in 'parenthesis'.

            for example:

            ... WHERE <Parameters.FY/CY>=2018 .... (parameter can have value of 'fy' or 'cy' for switching between calendar or fiscal year)

            translates to:

            ... WHERE 'fy'=2018 ...

            which will return error since you cannot compare string value to integer and even if you could, 'fy'='2018' retuns no values since they are not equal.

            The correct way should be:

            ... WHERE fy=2018 ...

             

            Unfortunately I don't know the solution to this, I suppose we could start an idea to implement this in Tableau (e.g. "Parameter defining field/table names or in Custom SQL") if it is not possible.

             

            It should have nothing to do with the data source.