Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
User | Count |
---|---|
10 | |
10 | |
3 | |
2 | |
1 |