How to pass parameters in SSRS/Power BI (report builder) ?

[wyzer]

Hello,

In SSRS/Power BI (report builder), how to query a table in Databricks with parameters please ?

Because this code doesn't works :

SELECT * FROM TempBase.Customers WHERE Name = {{ @P_Name }}

Thanks.

[Hubert-Dudek · Accepted solution]

hmm first you need to create Power BI dataset with databricks connections (I normally use PowerBI Desktop APP for that) and than you can quey it from Report Builder

[Leenaa]

Hi guys

thank you for the information😊

[wyzer]

Thnak you.

We also found this :

SELECT * FROM TempBase.Customers WHERE Name = ?

[HariharaSam]

Hi @Hubert Dudek​ ,

I have a similar requirement where I am trying to query a table in Databricks by passing a parameter from Power BI report builder. So I have two queries out of which one is working and the other is not working.

Can you help in identify why one query is not working.

--Working query--

Select Id,Name,StartDate from TableA where Id = ?

--Not Working query--

Select Id,Name,date_format(StartDate,'dd') as StartDay from TableA where Id = ?

 In the second query I have used a date_format function which is causing the issue.

I tried running both the queries in Databricks directly and both are working but the second query is failing when I try to run it from Power BI report builder with the placeholder for parameter

This is the error message I am getting when I am trying to validate the second query in Power BI report builder

"ERROR [42000] [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.catalyst.parser.ParseException: 

mismatched input '?' expecting {'(', '{', 'CALLED', 'CLONE', 'COLLECT', 'CONTAINS', 'CONVERT', 'COPY', 'COPY_OPTIONS', 'CREDENTIALS', 'DEEP', 'DEFINER', 'DELTA', 'DETERMINISTIC', 'ENCRYPTION', 'EXPECT', 'FAIL', 'FILES', 'FORMAT_OPTIONS', 'HISTORY', 'INCREMENTAL', 'INPUT', 'INVOKER', 'LANGUAGE', 'LIVE', 'MODIFIES', 'OPTIMIZE', 'PATTERN', 'READS', 'RESTORE', 'RETURN', 'RETURNS', 'SAMPLE', 'SECURITY', 'SHALLOW', 'SPECIFIC', 'SQL', 'TIMESTAMP', 'VERSION', 'VIOLATION', 'ZORDER', 'ADD', 'AFTER', 'ALL', 'ALTER', 'ALWAYS', 'ANALYZE', 'AND', 'ANTI', 'ANY', 'ARCHIVE', 'ARRAY', 'AS', 'ASC', 'AT', 'AUTHORIZATION', 'BETWEEN', 'BOTH', 'BUCKET', 'BUCKETS', 'BY', 'CACHE', 'CASCADE', 'CASE', 'CAST', 'CHANGE', 'CHECK', 'CLEAR', 'CLUSTER', 'CLUSTERED', 'CODEGEN', 'COLLATE', 'COLLECTION',..."

[mj2017]

Were you able to fix the error?

[arzsanj]

to learn how to SEO your articles please check talaex.ir

[Nj11]

Hi, I am not able to see the data in SSRS while I am using date parameters but with manual dates data is populating fine. The database is pointing to databricks. I am not sure what I am missing here. Please help me in this. Thanks

I am trying with query:

Select * from table1 where startdate >= '{{Parameter_Start_date}}' and enddate <= '{{Parameter_end_date}}'

[mj2017]

Were you able to fix the query?