cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alvabress
New Member

parameter to a stored parameters

 

Dear All,

i would like to use a sql stored procedure as a source for a table in a power bi reports.

 

Something like this:

 

DECLARE @RC int
DECLARE @LastName nvarchar(50)

SET @LastName = 'Pluto'

EXECUTE @RC = [HumanResources].[uspGetEmployeesTest2] @LastNam

 

This store have an input parameter that i would like to manage as a filter (slicer) in the power BI

reports. 

How i can manage this issue?

Thanks

2 REPLIES 2
Anonymous
Not applicable

Re: parameter to a stored parameters


Hi @alvabress,

Simple answer is I don't think you can. We have experimented with using SP's as data sources with and without parameters, and we have had nothing but problems. It just about works without parameters, but we had no success with.

In the end we switched to using SQL Views to provide the dataset we wanted to import.

We have gone through a mindset change in using PBI as opposed to SSRS. With SSRS you do want to get a dataset that has a limited / focused set of records, its the best way to get a good fast report. But with PBI its not quite the same, the power of PBI is in the dynamic filters and visualisation associations that you can lever to give the end user a better experience, thus you may not really want to limit the dataset in the data model, maybe you really want to load the whole lot, and then use filters to shape how it looks in PBI. Like I say a mindset change. Worried about the number of records or data model size, we did to start with, but PBI is very efficient at compression, now we just load the lot and let PBI take the strain. We are actually using SSRS tabular, but that's another story.

I hope this is helpful, if not then please excuse my rambles!

All the best
Rob

 


 

alvabress
New Member

Re: parameter to a stored parameters

Dear,
thanks for the answer, I agree with everything you said, my problem is that the store

has aready build in an external SQL DB and of course contain some logic that i can't replicate

in a power bi report or in a sql view.

 

Unfortunally what i need is use a slicer for get a value and use this value as a store parameters and

then present the result of the store.

 

Many thanks.

Alvaro

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors