Forum Discussion
parameters to SP or Sql query
Thank you so much. Will give this a go as soon as possible.
By the way, this work fine. Thanks so much. Now I need to find out how to update the parameters in PowerBI.com so that i can refresh the data as needed. Thanks again.
Hi MRZ,
I made a test to call stored procedure with parameters in Power BI Desktop. You can review the following example to apply it to your scenario.
In SQL Server, I create a procedure named p4test in test database of v-kaxion2015tes\sql2016tabular server.
Create PROC p4test @sdate date,@edate date AS BEGIN SELECT @sdate StartDate, @edate Enddate END
1. In Query Editor of Power BI Desktop, click "New source"> "SQL Server", enter server name, database name and statement "exec p4test '8/11/2016', '8/12/2016'", click ok.
2. Right click on that query on the left panel and select Advanced Editor, and paste the below code.
let
SQLSource = (param1 as date, param2 as date) =>
let
Source = Sql.Database("v-kaxion2015tes\sql2016tabular", "test", [Query="exec p4test '"& Date.ToText(param1) & "','" & Date.ToText(param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSource
3.Click “Invoke” button and enter parameter values as shown in the following screenshot.
Thanks,
Lydia Zhang
Anonymous: May i know how this works in Power BI Service. If it is not going to work in Power BI Service, then is there any alternative to achieve the same kind of functionality in Power BI Service
We are also looking for a solution to this from the service... it doesn't appear to exist as far as I can tell...
Hi All,
Can any one Plz help me out ,
Is it possible to get the Invoke function , like as a parameter in desktop to pass i/p value as a parameter to my direct query (SP) .
Based on that i/p value my data has to be refreshed or updated .
Is it possible to desible the "Refresh data" or "Edit Permission" warnings messages always .
Thanks
Raghu
Thanks Lydia, this worked perfectly. Would you have any idea how to just pass a textual value, instead of date. So Text.ToText or similar?
let
SQLSource = (param1 as text) =>
let
Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
in
Source
in
SQLSourceThis will Work
Regards,
Firoj Shaikh
let
SQLSource = (Start_Date as date, End_Date as date, Fund_Name as text) =>Not sure... is this what you were looking?
I use like '"&Text.From(Period)& "'
Thank you so much. Will give this a go as soon as possible.
By the way, this work fine. Thanks so much. Now I need to find out how to update the parameters in PowerBI.com so that i can refresh the data as needed. Thanks again.
Seems this is a solution for PBI desktop only.
Dont know what will be the benifit of this if we have to modify parameter every time a new range of data is required and then publish the report.
I was looking for similar implementation in service side but no success so far.
Hi Anonymous,
I'm dealing with something like this but this doesn't work for me. I want, in the step 3, fill these values with the value from an app. It's is possible? Full post is available here.
Thanks in advance
Julian
Guys do we've a solution for this, I've similar requirement, I've got four drop downs in my report
1. Country
2. State
3. City
4 Store
the navigation is working fine, but I've a requirement to take this to next level i.e. in each drop down I've option to select either one or all, now once the selection is made, I want to use this as a input parameter for a stored procedure which resides in my sql server database.
I understand that in the new query editor I can use the stored procedure but how do I populate the parameter from the drop down visualization i.e. slicer in my case (I can use some other visualization aslo), first will list country, second will have states, third will have city and fourth will have storeIDs, now I can either have all selected or any indivudial values and on the basisi of this select tion, my procedure should execute like below
exec dbo.myproc countryid = @countryvar, stateid = @statevar, cityID = @cityID, storeDi = @storeID
Hey, can you find any solution or workaround, to this? pls share in any
I receive this error after made your step to create a parameters
DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16Any idea to fix this?
I received this error mesage after run your steps with my own procedure
DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16something Imade wrong?
let
SQLSource = (Param1 as date, Param2 as date) =>
let
Source = Sql.Database("10.58.211.97,49461", "PPL_KPI", [Query="EXEC [PPL_KPI].[dbo].[Completed_kpi]'"& Date.ToText(Param1) & "','" & Date.ToText(Param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSourceI received this error mesage after run your steps with my own procedure
DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16something Imade wrong?
let
SQLSource = (Param1 as date, Param2 as date) =>
let
Source = Sql.Database("10.58.211.97,49461", "PPL_KPI", [Query="EXEC [PPL_KPI].[dbo].[Completed_kpi]'"& Date.ToText(Param1) & "','" & Date.ToText(Param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSourceI was able to create a Fired End query using a stored procedure with parameters...I invoke and then there is an InvokedFunction with the results...I built a report off of it but it will not refresh with prompts for new dates. It continues to refresh with the original date values. How do you fix that?
