Referring parameters in direct query of report builder and i am trying to fetch data from databricks

[Ashok_Gopeani]

Hi Team,

I am connecting the databricks to fetch the data through direct query, in that direct query i want pass parameter value at where clause of the direct query to display the report dynamically

i am following below steps 

setting report parameter

and setting the parameter in dataset

and trying to pass this parameter in direct query

i written the query in advanced editor like below

i have tried query with below syntaxes but nothing is working 

SELECT  LN_ID,RUN_ID, VAL_AMT FROM  sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where RUN_ID = "q_ruind"

SELECT  LN_ID,RUN_ID, VAL_AMT FROM  sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where RUN_ID = :q_ruind

SELECT  LN_ID,RUN_ID, VAL_AMT FROM  sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where RUN_ID = {{q_ruind}}

SELECT  LN_ID,RUN_ID, VAL_AMT FROM  sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where RUN_ID = "{{q_ruind}}"

SELECT  LN_ID,RUN_ID, VAL_AMT FROM  sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where RUN_ID = "&q_ruind&"

 while i am using above syntaxes i am getting bellow errors

could you please suggest any valid syntax to refer or any other alternative way to pass parameter value to the direct query.

[Ashok_Gopeani · Accepted solution]

@burakkaragoz 

thak you for your reply... 

i have tried with below  still i am getting bellow error

select * from sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where run_id = @q_runid

below is the total text in the advanced editor

let

    Source = Value.NativeQuery(DatabricksMultiCloud.Catalogs("spg-ratings-idf-sf-dev.cloud.databricks.com", "/sql/1.0/warehouses/f8f23a5f6689a0d8", [Catalog="sf_cmbs_dev", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="sf_cmbs_dev",Kind="Database"]}[Data], "select * from sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where run_id =@runid", null, [EnableFolding=true])

in

    Source

[v-sdhruv]

Hi @Ashok_Gopeani ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Ashok_Gopeani ,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sdhruv]

Hi @Ashok_Gopeani ,

Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @Ashok_Gopeani ,

1. Create a report parameter like- q_runid

2.In Dataset Properties → Parameters, map @q_runid  to your Report Parameter.

3. In Edit Mashup , define the M code like below:

Make sure parameter type in Report Builder matches the expected type in SQL. If run_id is numeric, do not wrap it in single quotes.
For Numeric-
query = "SELECT * FROM ... WHERE run_id = " & Text.From(runidParam)
For String- 
query = "SELECT * FROM ... WHERE run_id = '" & runidParam & "'"
You can debug your dynamic query using-
Diagnostics.Trace(query, 1)

Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[burakkaragoz]

Hi @Ashok_Gopeani ,

When using DirectQuery with Databricks in Report Builder, parameter substitution doesn’t work the same way as in Import mode or traditional SQL Server sources. You can’t just drop the parameter name into the query string like 'RUNID' — it won’t resolve properly.

Here’s what you can try:

1. Use a query parameter placeholder
   In your dataset query, use @YourParameterName like this:

   SELECT ID, LOAN_ID, VAL_AMT 
   FROM #_cmtbs_devnew.custdetails_credit_model_rank_dim_loi_ctp 
   WHERE RUN_ID = @RUNID

1. Make sure the parameter is defined
   In Report Builder:

   • Go to Dataset Properties > Parameters
   • Map @RUNID to the report parameter you created

2. Avoid quotes around the parameter
   Don’t wrap @RUNID in quotes — Report Builder will handle that based on the parameter’s data type.

3. Check for table name typos
   Your table name looks a bit off:
   #_cmtbs_devnew custdetails_credit_model_rank dim_loi_ctp
   If that’s not a valid table name, it might be causing the error. Make sure it’s fully qualified and doesn’t have spaces unless aliased properly.

Let me know if you’re still hitting errors — happy to help troubleshoot further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Ashok_Gopeani · Accepted solution]

@burakkaragoz 

thak you for your reply... 

i have tried with below  still i am getting bellow error

select * from sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where run_id = @q_runid

below is the total text in the advanced editor

let

    Source = Value.NativeQuery(DatabricksMultiCloud.Catalogs("spg-ratings-idf-sf-dev.cloud.databricks.com", "/sql/1.0/warehouses/f8f23a5f6689a0d8", [Catalog="sf_cmbs_dev", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="sf_cmbs_dev",Kind="Database"]}[Data], "select * from sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp where run_id =@runid", null, [EnableFolding=true])

in

    Source

[burakkaragoz]

Thanks for the update.

The issue here is that in Power Query (M language), you can’t use @parameter syntax like in SQL Server. Instead, you need to dynamically build the SQL query string using the parameter value.

Here’s how you can fix it:

1. Make sure you’ve created a parameter in Power BI or Report Builder (e.g. q_runid).
2. Then update your M code like this:

let
    runidParam = q_runid,
    query = "SELECT * FROM sf_cmbs_dev.emea_curated.t_credit_model_run_dm_ln_otp WHERE run_id = '" & runidParam & "'",
    Source = Value.NativeQuery(
        DatabricksMultiCloud.Catalogs("spg-ratings-idf-sf-dev.cloud.databricks.com", "/sql/1.0/warehouses/f8f23a5f6689a0d8", [Catalog="sf_cmbs_dev"]),
        query,
        null,
        [EnableFolding=true]
    )
in
    Source

This way, the parameter value is injected directly into the SQL string, which is how Power Query handles dynamic queries with Databricks.

Let me know if that works for you or if you hit any other issues.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Ashok_Gopeani]

@burakkaragoz 

when i use this above as suggested 

i am getting below error

and we are defining report parameter at report level, we can able set parameter at dataset properties and also on edit mashup window also..like below

once click on edit mashup, we can set another param. so whatever query we are writting in advanced editor is taking parameter from edit mashup only i believe and we don't have mapping between param in edit mashup and report parameter. please clarify whether Parameter at edit mashup and parameter at dataset properties are same or not.

and i tried with below also like edit mashup param with blank value and below

i can able to save but getting some other issue while execution

but when i try to save with runid param some value like 42334 getting bellow error 

i know its long post and kept every thing.. please verify all the points and suggest possible solution..

thank you very much for your support.