Using HANA Input Parameters in Tableau

[Former Member]

We are using Tableau at my work and I think it is Harry Potter level magical when it comes to creating beautiful, easy to understand visuals.  However, when connecting to HANA there seems to be a limitation in passing input parameters to a view.  When first connecting to a view in Tableau, it will prompt you for any input parameters used in your view.

This problem with this method is you can't change it dynamically in a dashboard with a parameter created in Tableau.  To pass an input parameter dynamically with a parameter in Tableau, there are two different methods you can use.  The first is creating a Custom SQL data source in Tableau.  Using the RSPCPROCESSLOG table as an example data source (I have another blog coming using it :smile: ), here is an example of using a Tableau parameter and a Custom SQL data source.

select l.variante as process_chain,

to_date(utctolocal(to_timestamp(l.starttimestamp), 'PST')) as start_date,

utctolocal(to_timestamp(l.starttimestamp), 'PST') as start_time,

utctolocal(to_timestamp(l.endtimestamp), 'PST') as end_time,

round((seconds_between(to_timestamp(l.starttimestamp), to_timestamp(l.endtimestamp)))/60, 2) as duration

from <your schema here>.rspcprocesslog l

where l.starttimestamp is not null and l.starttimestamp != 0

and l.endtimestamp is not null and l.endtimestamp != 0

and to_date(utctolocal(to_timestamp(l.starttimestamp), 'PST')) = <Parameters.My Parameter>

While this works just fine, it still doesn't solve the problem of using an actual input parameter in your HANA view.  To do this, we will use a Custom SQL data source in Tableau again and query the view with PLACEHOLDER.  Our calculation view in HANA will look like this and our input parameter is IP_START_DATE.

var_out =  select l.variante as process_chain,

    to_date(utctolocal(to_timestamp(l.starttimestamp), 'PST')) as start_date,

  utctolocal(to_timestamp(l.starttimestamp), 'PST') as start_time,

  utctolocal(to_timestamp(l.endtimestamp), 'PST') as end_time,

  round((seconds_between(to_timestamp(l.starttimestamp), to_timestamp(l.endtimestamp)))/60, 2) as duration

  from rspcprocesslog l

  and l.starttimestamp is not null and l.starttimestamp != 0

  and l.endtimestamp is not null and l.endtimestamp != 0

  and to_date(utctolocal(to_timestamp(l.starttimestamp), 'PST')) = IP_START_DATE;

In your Custom SQL data source in Tableau, use the following syntax.

select * from "_SYS_BIC"."YOUR_CALCULATION_VIEW"('PLACEHOLDER' = ('$$IP_START_DATE$$', <Parameters.My Parameter>))

You will need to add your view as a data source the first way mentioned in this blog so you can add a Tableau parameter that you can insert into your Custom SQL data source.  I noticed that when I set my parameter as a date in Tableau, it created an error when passing it HANA.  To solve this, I changed the data type to a string and it worked just fine.  You won't have a date picker, but typing YYYY-MM-DD is not very hard.

Now you may be asking why does this matter and why not just write the SQL directly in Tableau?  If you need to push down your parameters to intermediary steps in your calculation view and report on the results, you need to be able to pass values to an input parameter.  Also, by having the value you are passing to the HANA input parameter as a Tableau parameter, you can put it on a dashboard and allow a user to easily change it.  This opens up the possibility to all sorts of creative, dynamic visualizations you can create.  I hope this helps someone out and thanks for reading.

Also, I would like to thank my coworker Vineeth for figuring out the PLACEHOLDER version and allowing me to share it with you.