Forum Discussion

vijaycp2's avatar
vijaycp2
Helper I
1 year ago

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 ...
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
vijaycp2's avatar
vijaycp2
Helper I
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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.