01-17-2022 01:31 AM
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.
01-17-2022 02:17 AM
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
01-17-2022 02:17 AM
03-24-2023 11:42 PM
Hi guys
thank you for the information😊
01-18-2022 04:50 AM
Thnak you.
We also found this :
SELECT * FROM TempBase.Customers WHERE Name = ?
03-24-2023 05:10 AM
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',..."
05-20-2024 09:38 AM
Were you able to fix the error?
03-25-2023 12:37 AM
to learn how to SEO your articles please check talaex.ir
08-24-2023 02:33 AM
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}}'
05-20-2024 09:40 AM
Were you able to fix the query?
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