How do I build report against sql query against SQL Server database and Pass parameters to query

[vijaycp2]

How do I develop PowerBI desktop report using sql query against SQL Server database and pass parameters to query. I want to develop drop down list and pass the value selected by user as parameter to the query. 

[vijaycp2]

Query option is blocked for me. I have opened support case with microsoft. 

[parry2k]

@vijaycp2 did you check all the pre-requisite before using dynamic M parameters? Your table has to be in DQ mode.

[vijaycp2]

The query option is disabled for me in manage parameter option. I opened support ticket with Microsoft. 

[Anonymous]

Hi @vijaycp2 ,
It's been a while since I've heard back from you and I wanted to follow up. Have you had a chance to try the solution that has been offered? If the issue has been resolved, could you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[vijaycp2]

I referred to this link - Create dynamic reports with parameters - Training | Microsoft Learn however I am getting following error for the simple following query used. DataSource.Error: Microsoft SQL: Incorrect syntax near '&'.

Query -

SELECT HRRef, FirstName, LastName
FROM dbo.HRRM
WHERE HRRef = &HRRef -- I tried HRRef = '&HRRef&' however same error

[Anonymous]

Hi All,
Firstly,  vijaycp2  and parry2k thank you for yours solutions!
And @vijaycp2 ,Based on the error code you uploaded, it appears to be related to placeholders in SQL queries. This is because when using parameters in a SQL query, you can't simply concatenate strings directly with & as you can in some programming languages.
You can use a combination of & and Parameter to refer to parameters in Power BI:

let
    Source = Sql.Databases("VM"),
    AdventureWorks2022 = Source{[Name="AdventureWorks2022"]}[Data],
    Sales_Store = AdventureWorks2022{[Schema="Sales",Item="Store"]}[Data],
    BusinessEntityIDValue = Value,  
    FilteredSalesStore = Sql.Database("VM", "AdventureWorks2022", 
                                       [Query="SELECT * FROM Sales.Store WHERE BusinessEntityID = " & Text.From(BusinessEntityIDValue)]),
    FinalResult = FilteredSalesStore
in
    FinalResult

If you need to use the Value.NativeQuery function then you can try the following code, hope it helps:

let

    Source = Sql.Databases("VM"),
    Query = "SELECT * FROM Sales.Store WHERE BusinessEntityID = @BusinessEntityID",
    FilteredSalesStore = Value.NativeQuery(
        Source{[Name="AdventureWorks2022"]}[Data], 
        Query, 
        [BusinessEntityID = Value]  
    ),
    
    FinalResult = FilteredSalesStore
in
    FinalResult

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@nilendraFabric it would work, but it will not pass use selected values, and that's where dynamic M query parameters comes into the picture.

[nilendraFabric]

Hi @vijaycp2 ,

as @parry2k  suggested ,to make your query dynamic you can define one or more parameters in Power Query (via Manage Parameters) and then reference those parameters inside your query using M code. For example, using the Value.NativeQuery function you can build a query string that concatenates your parameter’s value.
  let
   Server = Sql.Database(“YourServer”, “YourDatabase”),
   ParamValue = ParameterName,
   Query = “SELECT * FROM YourTable WHERE SomeColumn = ‘” & ParamValue & “’”,
   Data = Value.NativeQuery(Server, Query)

[vijaycp2]

I referred to this link - Create dynamic reports with parameters - Training | Microsoft Learn however I am getting following error for the simple following query used. DataSource.Error: Microsoft SQL: Incorrect syntax near '&'.

Query -

SELECT HRRef, FirstName, LastName
FROM dbo.HRRM
WHERE HRRef = &HRRef -- I tried HRRef = '&HRRef&' however same error

[parry2k]

@vijaycp2 check dynamic M parameters to achieve this. Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn