cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

wyzer
Contributor II

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Hubert-Dudek
Esteemed Contributor III

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

image.png

View solution in original post

8 REPLIES 8

Hubert-Dudek
Esteemed Contributor III

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

image.png

Leenaa
New Contributor II

Hi guys

thank you for the information😊

wyzer
Contributor II

Thnak you.

We also found this :

SELECT * FROM TempBase.Customers WHERE Name = ?

HariharaSam
Contributor

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
New Contributor II

Were you able to fix the error?

arzsanj
New Contributor II

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

Nj11
New Contributor II

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
New Contributor II

Were you able to fix the query?

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group