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

27 REPLIES 27

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

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.

@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

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

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

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.

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!