topic Re: parameter to a stored parameters in Report Server

parameter to a stored parameters

alvabress — Mon, 19 Feb 2018 14:22:17 GMT

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

Re: parameter to a stored parameters

Anonymous — Mon, 19 Feb 2018 16:06:48 GMT

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

Re: parameter to a stored parameters

alvabress — Tue, 20 Feb 2018 08:20:52 GMT

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