Forum Discussion
Using ODBC to Snowflake as a Datasource within Report Builder. Require to use Parameters. Issue.
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'
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.
4 Replies
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.
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?
Same issue, I am getting, Anonymous / @mgtaylor3, if anyone has solution please share?
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.
