cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MRZ
Frequent Visitor

parameters to SP or Sql query

There is probably a simple answer but after looking all over for an answer and trying various methods, the right code still escapes me.  The scenario is I have a stored procedure that uses two input parameters, a start and end date. If I hard code the date in the call, getting the data works fine. I defined two parameters in Power BI and have been trying to figure out how to call the SP with these 2 paramters. My paramters are named Sdatex and Edatex. Both are defined as date type variables.

here is my call that works:

DECLARE @return_value int

EXEC @return_value = [dbo].[Capacity_SP]

@SDate = '8/11/2016',

@EDate = '8/12/2016'

SELECT 'Return Value' = @return_value

 

I also tried to execute the SP code (a CTE) insted of the SP and I have the same issue. Hard coded dates work but I do not know how to tell Power BI that I want the paramters (Sdatex and Edarex) used instead of the date.

Any help would be appreciated.

2 ACCEPTED SOLUTIONS

Thank you so much. Will give this a go as soon as possible.

View solution in original post

MRZ
Frequent Visitor

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.

View solution in original post

26 REPLIES 26
KarenL7
Frequent Visitor

Hi 

Using this query I have been able to manage building a parameter to dynamically use my Stored Procedure Parameters just like I use in my SSRS reports.

 

However, I have an extra Text Parameter so when Invoke the parameter I get an error

"1 arguments were passed to function which expects between 2 and 3 power bi parameter function"

 

This is the code which I amended from above - No syntax errors were found

 

let

    SQLSource = (Title as text, StartDate as date, EndDate as date) =>

 let  

    Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [Schema].[StoredProcedureName] '

    "& Text.Contains(Title) & "','" & Date.ToText(StartDate)&"''" & Date.ToText(EndDate)&"' #(lf)#(lf)#(lf) #(lf)"])

 in

     Source

 in

     SQLSource

 

The first parameter can store a list of text fields as set up in the stored procedure - SSRS parameters can handle these presumably Power BI parameters can too - perhaps I am using the wrong text fields?

 

Any help would be great

 

Thanks

 

Karen

v-yuezhe-msft
Microsoft
Microsoft

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.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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=16

 

something 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
SQLSource

 

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=16

 

something 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
SQLSource

 

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=16

Any idea to fix this?

 

Hi @v-yuezhe-msft,

 

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

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
SQLSource

 

 

 

This should work 

Regards,

Firoj Shaikh.

Firojshaikh03@gmail.com

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
SQLSource

 

This will Work 

 

 

 

Regards,

Firoj Shaikh

firojshaikh03@gmail.com

I use like  '"&Text.From(Period)& "'

I use like  '"&Text.From(Period)& "'

@SvenTexas

 

let
SQLSource = (Start_Date as date, End_Date as date, Fund_Name as text) =>

 

 

Not sure... is this what you were looking?

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

I have similar issue let me know if you find any solution

Yeh sorry, no solution found yet on text.totext, ended up coding around it unfortunately.  good luck.

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
SQLSource

 

 

check i tried this and it works

still looking out for way to pass invoked function as input . Slicer could be an option but it takes Stored values than random and it inputs as dropdown or list.

 

Regards,

Firoj 

firojshaikh03@gmail.com

 

Anonymous
Not applicable

 

Guys, you dont need to use Text.ToText, It is required for only non text values. 

for example: '"& VariableName & "'

Anonymous
Not applicable

 

Guys, you dont need to use Text.ToText, It is required for only non text values. 

for example: '"& VariableName & "'

Hi Sven, just dont add the "Text.ToText" part. it's already text.

 

for example:

 

let
    SQLSource = (param1 as text, param2 as date) =>
let  
    Source = Sql.Database("MyServer", "MyDatabase", [Query="exec [MyDatabase].[dbo].[myProc] '"& param1 & "','" & Date.ToText(param2)&"'"])
in
    Source
in
    SQLSource

 I hope this helps.

 

Alan

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors