Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

28 REPLIES 28
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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors