Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
yravipudi
Frequent Visitor

Power BI report Server on premises with url paramaters

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

1 ACCEPTED SOLUTION
hackcrr
Super User
Super User

Hi, @yravipudi 

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

View solution in original post

3 REPLIES 3
yravipudi
Frequent Visitor

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)

yravipudi
Frequent Visitor

Hi, @hackcrr

Thank You in advance,

i will try this code.

hackcrr
Super User
Super User

Hi, @yravipudi 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors