Using ODBC to Snowflake as a Datasource within Report Builder. Require to use Parameters. Issue.

[Anonymous]

Good Morning, I wonder if I have a curly one here for you.

I've created a Paginated Report, initially using a link to a PBI, we now require this to be a direct link to snowflake.

Ok , so I have the ODBC link and yes i can run a select query and my data appears.  Thing is I need to use Parameters.

Or should I say the Users require to use a few parameters to run the report.

How do I pass a Parameter, Built within Report Builder, thru to the Query in Report Builder  then  for Snowflake to register and use the parameter?

So an example":  very simple...SELECT * FROM Table
Where ENTITY_ID = @Parameter
limit 100;

@Parameter    for example would be 'LMUS'

------------------------------------------------------------------------

OK, I have managed to sort at least a Single sent Parameter...

Using (?)  as a parameter.. So 

SELECT * FROM Table
Where ENTITY_ID = (?)
limit 100;

I then point that (?) to the Report Builder Parameter I built  @Entity...

Question now is Multi Parameter...  what to send through to Snowflake to see and use muiltple selections:- ie  'LMUS,LMUK'

[Anonymous · Accepted solution]

OK, I have managed to sort at least a Single sent Parameter...

Using (?)  as a parameter.. So 

SELECT * FROM Table
Where ENTITY_ID = (?)
limit 100;

https://youtu.be/a4Frj8vG4as   helped quite a bit.

 Also Has shown how to do a Multi Parameter albeit that by typing in the required parameters.

A Colleague has managed to pass me the details of a working method of a Multi Selected Parameter that can be sent through the ODBC to Snowflake, from Report Builder.

Method is Creating an Expression in the Dataset Properties:

="SELECT * from Databasename.SchemaName.Tablename Where FieldName In ('" + Join(Parameters!Entity.Value,"','")+"')"

This then works with the Multi Valued Parameter called Entity.

I will be using this with a more complex Query for my paginated report(s).  Hope this too helps others.

[Ritesh_Air]

@Anonymous 

For me, it comes back with the query window. When I try to follow the steps above.

Can you please help? What am I doing wrong here?

Thanks in advance.

[Anonymous]

I have connected to Snowflake via ODBC connector from paginated report which runs on power bi service.
I have created a parameter from Dataset2 (a query based dataset which gives all the values of vendors from snowflake query).
When I pass this parameter to Dataset1 expression query, it gives me an error in service. The only case when the expression works for Dataset1 is when I do select * and that too without any WHERE condition.

Do you have any idea why this might be happening?

[apatel04]

Same issue, I am getting, @Anonymous / @mgtaylor3, if anyone has solution please share?