Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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
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)
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
User | Count |
---|---|
4 | |
4 | |
2 | |
2 | |
2 |