topic Re: How to pass parameters in SSRS/Power BI (report builder) ? in Data Engineering

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

wyzer — Mon, 17 Jan 2022 09:31:45 GMT

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.

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

Hubert-Dudek — Mon, 17 Jan 2022 10:17:40 GMT

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

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

wyzer — Tue, 18 Jan 2022 12:50:38 GMT

Thnak you.

We also found this :

SELECT * FROM TempBase.Customers WHERE Name = ?

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

HariharaSam — Fri, 24 Mar 2023 12:10:19 GMT

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',..."

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

Leenaa — Sat, 25 Mar 2023 06:42:16 GMT

Hi guys

thank you for the information😊

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

arzsanj — Sat, 25 Mar 2023 07:37:48 GMT

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

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

Nj11 — Thu, 24 Aug 2023 09:33:54 GMT

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}}'

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

mj2017 — Mon, 20 May 2024 16:38:02 GMT

Were you able to fix the error?

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

mj2017 — Mon, 20 May 2024 16:40:00 GMT

Were you able to fix the query?