AWS Quicksight : How to use parameter value inside SQL (data set) to render dynamic data on dashboard?

user_6081210 0
Viewed 6024

There is a provision to pass value for quicksight parameters via URL. But how can I use the value of the parameter inside SQL (data set) to get dynamic data on dashboard?

For example: QUERY as of now:

select * from CITYLIST;

Dashboard:

CITYLIST

city_name | cost_of_living
AAAAAAAAA | 20000
BBBBBBBBB | 25000
CCCCCCCCC | 30000

Parameter Created : cityName URL Triggered : https://aws-------------------/dashboard/abcd123456xyz#p.cityName=AAAAAAAAA

Somehow I need to use the value passed in URL inside SQL so that I can write a dynamic query as below: select * from CITYLIST where city_name = SomeHowNeedAccessOfParameterValue;

amazon-quicksight amazon-web-services
3 Answers

QuickSight doesn't provide a way to access parameters via SQL directly.

Instead you should create a filter from the parameter to accomplish your use-case.

This is effectively QuickSight's way of creating the WHERE clause for you.

This design decision makes sense to me. Though it takes filtering out of your control in the SQL, it makes your data sets more reusable (what would happen in the SQL if the parameter weren't provided?)

user_13782920

Create a parameter, then control and then filter ("Custom filter" -> "Use parameters").

If you select Direct query option and Custom SQL query for data set, then SQL query will be executed per each visual change/update.

The final query on DB side will look like [custom SQL query] + WHERE clause.

For example:

Visual side: For control Control_1 selected values "A", "B", "C";

DB side: [Custom SQL from data set] + 'WHERE column in ("A", "B", "C")'

Quicksight builds a query for you and runs it on DB side. This allows to reduce data sent over network.

user_73258900
user_2333470
Related