How to add dynamic parameter in custom sql query dataset?

Question & Answer
, ,
1

I am trying to create a dashboard/visual analysis, which can be viewed by any company subscribed to my application. Each company will have own set of data and it is loaded in S3. I created a dataset with custom sql query option to S3 via athena. Example query
select * from table where companyid = '123'.

The results of the dataset are used to build my dashboard. This all works well and I want to parameterize the company id field, so I can build dashboards dynamically for any company and each one will be able to see data only related to the company.

I am able to add parameter in dashboard/analysis, but I am not able to find a way to pass this parameter to my custom sql query dataset, so the query can be dynamic based on the parameter.

Any help would be appreciated.

2

I think my understanding about dataset was not clear earlier. I should not have added where filter in custom sql query and found that dataset is about getting all related data across the database and then add filters based on parameters in the analysis/visual.

Now I have remove companyid where clause from my custom sql query and added a parameter in analysis and then created filter based on the parameter. All works well now!

1 Like
3

Hi, did you find out if it is possible to add a parameter to your custom SQL query? Your solution is very elegant but I have a different problem with a large dataset that can only be solved by adding parameters to a custom SQL query.
Thanks and all the best

4

Wait, I just fixed my own query by removing a calculated field and linking my parameter to an actual field. However, my dashboard had become incredibly slow using filters because the database table was so large. Using parameters fixed this issue. SO if your dashboard slows down, try to replace field filters with parameters :slightly_smiling_face:

5

how did you link a parameter to an actual field?

6

Hi paraksh.
I made a control for the parameter and there is an option to link the control to a field so I selected that. I made a separate filter then linked that to the parameter.
Technically I linked the control to a field and not the parameter, but it meant that I was passing valid values to the filter.
I hope that helps.

7

Hi @grace_at_GG

I am also facing the same issue with TBs of data, need dynamic filtering at query level, can you please guide me, it would be great thanks in advance.

Any video or loom would also be helpful.

8

Hi Sarav,

I am also facing same issue and it can’t be fixed at visual level, so do you have any clue to make parameter passing in custom SQL ?

9

Hi @grace_at_GG any luck on this. please help.

10

Hi Shubhashish_Dixit sorry for my delayed reply.
I could not generate a dynamic SQL query using the ‘custom query’ function. Using filters and parameters allowed me to create dashboards with fast filtering that behave as I needed. However, my dataset is smaller than the one you described. The new AWS Quicksight documentation describes the parameters and filtering with them better than I can: Parameters in Amazon QuickSight - Amazon QuickSight

I have also started to add a date filter in my custom SQL queries to reduce the size of my dataset which is sort of dynamic because it uses the current datetime
i.e.

WHERE date > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

I hope that helps

Thanks and all the best