Inserting dataset parameters into custom SQL - Amazon QuickSight
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Inserting dataset parameters into custom SQL

You can insert dataset parameters into the custom SQL of a dataset in direct query mode by referencing it with <<$parameter_name>> in the SQL statement. At runtime, dashboard users can enter filter control values that are associated with a dataset parameter. Then, they can see the results in the dashboard visuals after the values propagate to the SQL query. You can use parameters to create basic filters based on customer input in where clauses. Alternatively, you could add case when or if else clauses to dynamically change the logic of the SQL query based on a parameter's input.

For example, say you want to add a WHERE clause to your custom SQL that filters data based on an end user's Region name. In this case, you create a single value parameter called RegionName:

SELECT * FROM transactions WHERE region = <<$RegionName>>

You can also let users provide multiple values to the parameter:

SELECT * FROM transactions WHERE region in (<<$RegionNames>>)

In the following more complex example, a dataset author refers to two dataset parameters twice based on a user's first and last names that can be selected in a dashboard filter control:

SELECT Region, Country, OrderDate, Sales FROM transactions WHERE region= (Case WHEN <<$UserFIRSTNAME>> In (select firstname from user where region='region1') and <<$UserLASTNAME>> In (select lastname from user where region='region1') THEN 'region1' WHEN <<$UserFIRSTNAME>> In (select firstname from user where region='region2') and <<$UserLASTNAME>> In (select lastname from user where region='region2') THEN 'region2' ELSE 'region3' END)

You can also use parameters in SELECT clauses to create new columns in a dataset from user input:

SELECT Region, Country, date, (case WHEN <<$RegionName>>='EU' THEN sum(sales) * 0.93 --convert US dollar to euro WHEN <<$RegionName>>='CAN' THEN sum(sales) * 0.78 --convert US dollar to Canadian Dollar ELSE sum(sales) -- US dollar END ) as "Sales" FROM transactions WHERE region = <<$RegionName>>

To create a custom SQL query or to edit an existing query before adding a dataset parameter, see Using SQL to customize data.

When you apply custom SQL with a dataset parameter, <<$parameter_name>> is used as a placeholder value. When a user chooses one of the parameter values from a control, QuickSight replaces the placeholder with the values that the user selects on the dashboard.

In the following example, the user enters a new custom SQL query that filters data by state:

select * from all_flights where origin_state_abr = <<$State>>

The default value of the parameter is applied to the SQL query and the results appear in the Preview pane. In the following screenshot, the default value of the State parameter is IL, or Illinois. The SQL query filters the data from the dataset and returns all entries in the dataset where the origin state is IL.

SQL query filtering data for Illinois state with results shown in preview pane.