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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


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

 


 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.