Power BI: Dynamic SQL parameters question

[OndrejVyhnal]

I am trying to do a Proof of Concept whether Power BI can be used to replace reporting in MicroStrategy/Narrowcaster.

What I am currently stuck on is how to implement dynamic parameters that would pass values into report SQL in Direct Query mode. I query against data in Oracle database.

I watched some videos ( I am new to Power BI) and tried this syntax below but doesn’t work.

A_MONTH_ID is Integer. 

A_MONTH_ID_PARAM is parameter name.

Any advice would be highly aprecaited.

let

    Source = Oracle.Database("DWHDTBDEVPRIMA.WORLD", [HierarchicalNavigation=true, Query="

    SELECT

ISIN,

ISSUER_NAME,

ISIN_ORDER,

PRD_DATE,

CORE_REPORTING_STRUCTURE_ID,

EXCHANGE_NAME,

EXCHANGE_CODE,

A_MONTH_ID,

STOCK_REPORT_ID,

WJXBFS1 As ADJ_MIXED_PRICE,

WJXBFS1 as PERF

FROM XSR_FE.PEER_GROUP_COMPARISON_POWERBI_POC

WHERE WJXBFS1 IS NOT NULL

AND A_MONTH_ID = " & A_MONTH_ID_PARAM & ""

])

in

    Source

[v-vpabbu · Accepted solution]

Hi @OndrejVyhnal,

Power BI has notoriously limited support for dynamic parameters, and unfortunately, Oracle's connector does not support ? placeholders for parameter binding like SQL Server or PostgreSQL.

Use Value.NativeQuery, which does support parameterized queries in Oracle when done correctly.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[OndrejVyhnal]

Thanks a lot!

So looks like the only way to make this work on Oracle is to set every Power BI parameter as text only. 

I was able to run the query witohur error now, which is great. But a bit woried what other problems it will bring later when integers and dates are set as text and so on...let see I continue with this advanture. 

Have a nice day!

[OndrejVyhnal]

Hi Vinay, thanks a lot for your feedback.

Could you please share a bit more details regarding this 'Value.NativeQuery' approach?

[v-vpabbu]

Hi @OndrejVyhnal,

Please check the similar thread which has been addressed in the community

https://community.fabric.microsoft.com/t5/Desktop/Use-power-bi-parameters-in-a-query-through-native-... 

Regards,

Vinay

[OndrejVyhnal]

Thanks a lot for your advise...I tried to change the native query to include '?'

but got Oracle: ORA-00911: invalid character

[kushanNa]

Hi @OndrejVyhnal 

if you know your parameter values then this can be done ,

you need to create a parameter table and use it as a slicer and bind that parameter table column to your parameter ,

in this video he explain how to do it , in here he is using date as a parameter you can use your month IDs in here instead

https://www.youtube.com/watch?v=pXU_SpiBWUA&t=18s&ab_channel=DataPlatformCentral 

but if you do not know the parameter and if it can be any text then it will get complex and you will need to use something like power apps i believe 

[DataNinja777]

Hi @OndrejVyhnal ,

In Power BI using DirectQuery mode, you cannot dynamically inject parameters into a native SQL query by concatenating strings inside the M code as you attempted. The syntax AND A_MONTH_ID = " & A_MONTH_ID_PARAM & "" will not work because Power Query doesn't support that kind of inline variable expansion in a SQL string. Instead, you need to define a parameter in Power BI (via Manage Parameters), and reference it through the native query interface—not by M code concatenation.

Create a numeric parameter called A_MONTH_ID_PARAM in Power BI. Then when connecting to Oracle, instead of writing M code manually, use the built-in connector and paste the query below directly in the SQL dialog:

SELECT
    ISIN,
    ISSUER_NAME,
    ISIN_ORDER,
    PRD_DATE,
    CORE_REPORTING_STRUCTURE_ID,
    EXCHANGE_NAME,
    EXCHANGE_CODE,
    A_MONTH_ID,
    STOCK_REPORT_ID,
    WJXBFS1 As ADJ_MIXED_PRICE,
    WJXBFS1 as PERF
FROM XSR_FE.PEER_GROUP_COMPARISON_POWERBI_POC
WHERE WJXBFS1 IS NOT NULL
  AND A_MONTH_ID = ?

When you enter this query and press OK, Power BI will detect the question mark ? as a placeholder and prompt you to bind it to a parameter. Select the A_MONTH_ID_PARAM you created. This allows you to drive the Oracle query dynamically via parameter value changes from the Power BI UI. Keep in mind, in DirectQuery mode, these parameter changes cannot be driven directly by slicers or visuals unless you use paginated reports or workarounds with query reduction settings.

Best regards,

[OndrejVyhnal]

Hi DataNinja, the solution you outlined didn't work. It seemed so promissing, but nope, Question mark in SQL doesnt work on Oracle...any chance you can take a look and advise?

So far PowerBI has been super frustrating making easy things to work seems like hell...Any help would be apreciated.

[OndrejVyhnal]

Thanks a lot for your advise...I tried to change the native query to include '?'

but got error 'Oracle: ORA-00911: invalid character'