1 Reply Latest reply on May 17, 2018 3:36 PM by Eric Hammond

    Using LOD with ODBC error

    Jan Jenko

      Hi,

       

      I'm trying to use an LOD expression on data I'm connected to over ODBC.  When I drag the calculated field ({FIXED [some_integer_field]: SUM([some_other_int_filed]}) to "Rows", I get a pop-up window saying "An error occured while communicating with data source _____".

       

      I looked at the SQL query it's trying to run:

                SELECT SUM((CASE WHEN `t0`.`X_measure__2` = 0 THEN NULL ELSE CAST(`t0`.`X_measure__1` AS FLOAT) / `t0`.`X_measure__2` END)) AS `sum:Calculation_1062568104450506752:ok`,

                  `t0`.`category` AS `category`

                FROM (

                      SELECT SUM(`table`.`field1`) AS `X_measure__1`,

                           COUNT(`table`.`field1`) AS `X_measure__2`,

                           `table`.`symbol` AS `symbol`,

                           `table`.`category` AS `category`

                       FROM `table`

                       GROUP BY 4,3

                     ) `t0`

                GROUP BY 2

       

      I tried running it in my own MySQL client, which also returned an error. However, things work fine when I change CAST(`t0`.`X_measure__1` AS FLOAT) for just `t0`.`X_measure__1`.

       

      Is there a way around this? Can I tell Tableau to change the query or something? I've had this problem ever since I started using Tableau, but I attributed it to not knowing how to use LOD expressions

       

      Anyway, any help will be appreciated...

       

      Best,

      Jan

       

      Versions:

      Tableau 10.5

      MySQL 5.6

        • 1. Re: Using LOD with ODBC error
          Eric Hammond

          Hi Jan,

           

          I am not getting that error with a similar LOD, but I don't have a MySQL data source on which to test this.  As LOD expressions usually identify a dimension as the fixed level, I would try to isolate the issue as being caused by the use of measure (some integer field).  Try:

          • {FIXED [some_stringdimension_field]: SUM([some_other_int_filed]}    //to confirm that the LOD works with a dimension. If yes, then:
          • {FIXED STR([some_integer_field]): SUM([some_other_int_filed]}       //in hopes that forcing the conversion of the integer to a string avoids the ODBC error.  Otherwise, try:
          • {FIXED 'x' + STR([some_integer_field]): SUM([some_other_int_filed]}   // concatenating an alpha character to prevent the "string" from being converted back to an integer at some point in the process.

           

          Even if a work-around gets past the error, consider reporting the error so that the development team can correct the issue in a future version.  Good Luck!