4 Replies Latest reply on Jun 21, 2017 7:53 PM by Roger Gemnay

    Facing Issue while passing Tableau Date parameter to SAP HANA input parameter

    Rekha Bathla

      Hi ,

      I am creating report on SAP HANA views and these  views have input parameters. I have created custom SQL in tableau where I am passing Tableau Parameters to HANA input parameter using Placeholder clause in SQL and "Insert Parameter" option.

      Below is the syntax in custom SQL:

       

      Select * from FROM "_SYS_BIC"."View_Name" ( 'PLACEHOLDER' = ('$$DeliveryStartDate$$',<Parameters.Start Date>), 'PLACEHOLDER' = ('$$DeliveryEndDate$$', <Parameters.End Date>), 'PLACEHOLDER' = ('$$InputDeliveryOFacilityAliasId$$', <Parameters.Facility Alias ID>) ) "View_Name"

       

      I am getting below syntax error for Date paramaters

      [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near "TO_DATE": line 35 col 111 (at pos 3301)

       

      This issue is only for Start and End Date Paramaters. We are not able to pass Data parameter to HANA Parameters DeliveryStartDate and DeliveryEndDate. I tried passing String parameter to DeliveryStartDate and DeliveryEndDate , it works fine with string paramaters. But I have requirement to have parameter with calendar view. String Parameter won't work in this case.

       

       

      If anybody came across same issue and knows the workaround, please help. Any pointers would be appreciated.

       

      Thanks,

      Rekha Bathla

       

       

        • 1. Re: Facing Issue while passing Tableau Date parameter to SAP HANA input parameter
          Brian Smith

          Rekha, we are having precisely the same issue, including the user requirement that we include a calendar control. We could find our way around it if we could make a dynamic String parameter that could convert the date value to a string before placing it into the custom SQL, but that also isn't something that Tableau supports.

          I don't have an answer for you, but I very much want to keep up with this conversation, and I will update here with anything I am able to find out. Thanks!

          • 2. Re: Facing Issue while passing Tableau Date parameter to SAP HANA input parameter
            Brian Smith

            Rekha, following up on this issue. My colleague and I took a look at the logs on both Tableau and HANA, and we have at least figured out the reason for the error. When Tableau embeds a parameter in a custom SQL query to be sent to HANA, if that parameter is a Date data type, Tableau puts a TO_DATE() function call around the parameter value, like this:

             

            'PLACEHOLDER' = ('$$IP_DATE_TO$$', TO_DATE('2016-01-31'))

             

            HANA will not accept any function calls within the PLACEHOLDER statement, so it throws an error and does not run the query.

            When the date value is sent as a String instead of as a Date type, the TO_DATE() function call is not inserted, so HANA accepts the query and runs it. But that means that we can't use a calendar picker to set the date value, which is a requirement for both of our projects.

             

            As we've said before, our problems would be solved by dynamic parameters -- even if those parameters could be set just based on values of other parameters.

             

            Other than better knowledge of the problem, no progress so far. I will update if we figure anything else out.

            • 3. Re: Facing Issue while passing Tableau Date parameter to SAP HANA input parameter
              Rekha Bathla

              Thanks Brian for the information !!

              I also thought the same workaround to use dynamic parameters based on value of other parameter. But tableau doesn't support even that.

              For the time being what I am doing..

              In the custom SQL, I have hardcoded the HANA date range parameters with some large range so that entire data is retrieved from HANA.

              I have created data parameters in worksheet and applied quick filter (calculated field using date parameter)on the data view  .but this approach will impact performance .

               

              Please keep me posted if you find any solution.

               

              Thanks ,

              Rekha

              • 4. Re: Facing Issue while passing Tableau Date parameter to SAP HANA input parameter
                Roger Gemnay

                Hello Bryan,

                 

                This syntax does not work, it will generate an error as you mentioned above...

                select *

                from "_SYS_BIC".""_SYS_BIC"."VIEW_NAME"

                ('PLACEHOLDER' = ('$$AsofDate$$',Parameters.MyDate>))

                 

                But if I use the dot notation syntax for PLACEHOLDER as per snippet below, it works for me.

                Select *

                FROM "_SYS_BIC"."VIEW_NAME"

                (PLACEHOLDER."$$AsofDate$$" => <Parameters.MyDate>)

                 

                Best,

                RogerG

                1 of 1 people found this helpful