Forum Discussion
How do I build report against sql query against SQL Server database and Pass parameters to query
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.
10 Replies
nilendraFabric it would work, but it will not pass use selected values, and that's where dynamic M query parameters comes into the picture.
vijaycp2 check dynamic M parameters to achieve this. Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
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)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
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 errorHi 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 FinalResultIf 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 FinalResultHope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
The query option is disabled for me in manage parameter option. I opened support ticket with Microsoft.
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!
Query option is blocked for me. I have opened support case with microsoft.
