Using local variable in Custom Query in DataSet

Question & Answer
1

Hello,

Is there a way to use local variable in custom SQL Query in AWS QuickSight?

When I try to declare a variable

    Declare @Total_Count int
    Set @Total_Count = (Select count(*) from Database.Table)

I will get an error saying error near declare.

For my business use case, I will require this count to be used for my query,

Using static parameter variable instead will not work as my DB is updated frequently.

Is there any way to work around?

2

Hi @QSUser1234 - Welcome to AWS QuickSight Community and thanks for posting the question. You can not create variables the way you are looking for snowflake, however explore the data set parameters, see the link below. Creating dataset parameters in Amazon QuickSight - Amazon QuickSight

Please provide your requirement what exactly you want to use this value for so that we can help you how to achieve it in QuickSight.

Tagging @Max_B @David_Wong @Koushik_Muthanna @Naveed for their expert advise as well.

Regards - Sanjeeb

1 Like
3

Hi,

There is no option to used parameter query in Direct Query mode.

Regard,
Naveed Ali

2 Likes
4

@QSUser1234 ,

Details would be helpful as to how the count you get will be used ? . If you just need a count , use a CTE

with count AS (
select count(*) as countsales from mountain_bikes.orders_timestamp
)
select countsales from count

1 Like
5

Hi @QSUser1234

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.