Retrieving all rows using a stored procedure with parameters

Hi, I would be interested in understanding how I can retrieve all records/rows using a stored procedure that has multiple parameters. I want to bring in all the data and then filter it once it is in the data model.

Is there a wildcard parameter that I can use for each parameter in order to retrieve all rows; shown in the highlighted row of the image below?

Thanks in advance. :slightly_smiling_face:

Hi @Kazza, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Hello @Kazza,

Thank you for posting your query onto the Forum.

I’ve mentioned a link below of a video which may help you in retrieving the records using a stored procedure that has multiple parameters.

Hoping you find this link useful and can help you to achieve your desired analysis.

Thanks & Warm Regards,
Harsh

[Stored Procedure with Parameter in Power BI] -
(https://www.youtube.com/watch?v=P5OXO994I0U)

Hi @Kazza,

I will suggest you check this materials out, it treated how you can Querying multiple tables using a wildcard parameter.

If you find it helpful kindly make it a solution for others.

DAVID

Hi @Kazza. It will hinge off the design of your stored procedure; in SQL Server, one can specify a default for each parameter for a stored procedure in case a value is not supplied when calling it. I’m not familiar with a way to do this via Power BI. Hope this helps. Greg

Kazza,
Why are you trying to filter the data on the SQL Server side. If you do this you block the Power BI engine from doing query folding and make it less efficient.
If you have data, like sales, and parameters, date, store, product etc then it seems like you would want to bring in all the sales data, and also the dimension tables for each parameter and set up your relationships and do your filtering inside of power bi…instead of doing it as part of the load process.
Can you give us a better idea of just what kind of data you are loading and what kind of parameters you would like to use?

1 Like

Thank you very much I will check it out now.

Thanks also I shall review the info.

Thanks for your reply appreciate it.

Hi, Just read your reply.
That’s exactly what I wanted to do, however, if I was to use the SP I needed to pass the parameters. Hence the wildcard idea. However, I was unable to achieve that so what I did was to look at the SQL for the SP and I rewrote it removing the parameters. Brought the data into Power BI and then as you described proceeded to filter it as required. On import I placed the SQL into the ‘Advanced Options’ section.
Just some background - I was converting an SSRS report to Power BI. The filtering was available in the SSRS report. It was all project related data. The SP was expecting a Project Manager name as one of the parameters.
Given that I am a beginner in Power BI I made the mistake of thinking I had to use the SP!

Thanks for your suggestion you were spot on!

It’s great to know that you are making progress with your query @Kazza. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like