Forum Discussion
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
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 ResultDefine 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 SourceIn 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=101Assume 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=101hackcrr
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
3 Replies
- hackcrr
Memorable Member
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 ResultDefine 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 SourceIn 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=101Assume 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=101hackcrr
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)