CODEWITHSUNDEEP
reporting-services
Arsee
Arsee

Reputation: 671

How to use Snowflake Parameter in SSRS Query

How can I use a Snowflake parameter in SSRS query? I tried different ways of doing it in SSRS, but unable to make it work.

Example from Snowflake sql date parameter as i have defined the Start and End date parameter within SSRS Report. In query text connected to Snowflake.

WHERE MyDate BETWEEN :Start_Date AND :End_Date > Got compile error :Start_Date not set.

Changed the date parameter behavior to

WHERE MyDate BETWWEN ? AND ? > Got an compile error @Start is not recognized.

I am actually, embedding the code inside the SSRS Query. Please advice

Upvotes: 1

Views: 194

Answers (2)

Holmes IV
Holmes IV

Reputation: 1749

It really depends on the type of parameters, if you are sending single use. I typically do it with a set statement and a ?

Example:

Set vStartDate = ?

Select $vStartDate

Then in the Parameters menu on the side of the Dataset Properties you assign the ? to whatever the Name = ? Value = @ParameterStartDate

The harder ones and the ones I am going in circles over are the multi value ones. for those I have been setting up the Parameters section with an expression

=Join(Parameters!ParamLocation.Value,",")

Then in the code itself I have been setting up CTEs. It throws an error and will not refresh the fields for you :) , but you can add the fields automatically in the dataset properties or write the query without parameters to get fields then add them and not refresh. I just ignore the error "OK"

WITH LOC_FILTER AS (
    SELECT TO_NUMBER(table2.VALUE) AS LOC_ID
    FROM TABLE(SPLIT_to_table(? ,',')) AS table2
    )   

SELECT top 10 LOC_ID  FROM LOC_FILTER

I am actually looking for a better solution, but the only other one I have seen is put the entire Query into a expression and give that to snowflake, my problem is most my code hits the expression limit and cannot get past up :(

Upvotes: 0

Cedrick Clemente
Cedrick Clemente

Reputation: 61

you need to use it as

WHERE MyDate BETWEEN @Start_Date AND @End_Date

Upvotes: 0

Related Questions