Power BI report Server on premises with url paramaters

[Anonymous]

1) We are using power bi report server (on-premises) with local network.

2) Created one procedure ([dbo].[GetUserInfo] @SubsNum) with parameter using SQL Server.

3) From power bi desktop, created Query with our server and database, connected direct query mode and added default query 'exec [dbo].[GetUserInfo] @SubsNum=102' from advanced filters.
4) On 'OK' click, it transform the data with default parameter value
5) Now we created a new parameter to pass the SubsNum (datatype is whole numer) parameter value dynamically from enduser and added this query to M query editor like below,

let
Source = (SubsNum as number) =>
let
SQLQuery = "EXEC GetUserInfo @SubsNum=" & Number.ToText(SubsNum),
Result = Sql.Database("server", "databasename", [Query=SQLQuery])
in
Result
in
Source

6) with the above query, we created one invoke function used this in report design

7) in the report design, Created one new parameter like 'SubsNum' added this to slicer

😎 finally we published the report and called this dashboard from end user application with embed url like below,

http://<YourReportServerName>/reports/powerbi/<YourReportPath>?rs:embed=true&SubsNum=101

9) here, every time we get same data if we pass the different value to the SubsNum parameter and we suspect that power bi report server not able to access the paramters

[Anonymous]

Hi, @hackcrr

Tried code you mentioned but,from the report design--> manage relationship --> not able to bind to parameter .

Here, in advanced property editor not available bind to parameter and from modeling tab-> Field parameter also not available

i am using Power BI Server and Desktop (May 2024)

[Anonymous]

Hi, @hackcrr

Thank You in advance,

i will try this code.

[hackcrr · Accepted solution]

Hi, @Anonymous 

You can try the following steps:
First in Power BI Desktop, create a parameter SubsNum named.
Set the data type Whole Number and provide the default value. Update your M query to use the defined parameter as shown below:

let
    Source = Sql.Database("server", "databasename"),
    SQLQuery = "EXEC GetUserInfo @SubsNum=" & Number.ToText(SubsNum),
    Result = Sql.Database("server", "databasename", [Query=SQLQuery])
in
    Result

Define a function in the M query editor that takes SubsNum as an argument:

let
    Source = (SubsNum as number) =>
    let
        SQLQuery = "EXEC GetUserInfo @SubsNum=" & Number.ToText(SubsNum),
        Result = Sql.Database("server", "databasename", [Query=SQLQuery])
    in
        Result
in
    Source

In the report design, call this function and pass the SubsNum parameter to it. Open the report in the Power BI Reporting Server to ensure that it can use the default parameter values.
Use the following URL format SubsNum to pass parameters dynamically:

http://<YourReportServerName>/reports/powerbi/<YourReportPath>?rs:embed=true&rs:ParameterName=SubsNum&rs:ParameterValue=101

Assume that the report server is named MyReportServer, the report path is Reports/MyReport, and you want to pass a value of 101 for SubsNum:

http://MyReportServer/reports/powerbi/Reports/MyReport?rs:embed=true&rs:ParameterName=SubsNum&rs:ParameterValue=101

hackcrr

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