parameters to SP or Sql query

[MRZ]

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.

[MRZ · Accepted solution]

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

[Anonymous]

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

for example: '"& VariableName & "'

[AJTindall73]

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

[SvenTexas]

Thank you Alan - can't believe I missed the obvious!  Thank you for putting the response on, am sure it will help others out too.

[Sunkari]

@v-yuezhe-msft: 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

[Fugi]

We are also looking for a solution to this from the service... it doesn't appear to exist as far as I can tell...

[MRZ · Accepted solution]

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

[MRZ · Accepted solution]

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.

[sbahugu2]

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.