Forum Discussion

alvabress's avatar
alvabress
New Member
8 years ago

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

Stored

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    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's avatar
      alvabress
      New Member
      8 years ago

      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