Power BI Report Builder

[Trainor]

Hi Everyone,

I am able to create reports in Power BI Report Builder that reads from either a table or a view in a Fabric warehouse.  What I would like to to is to read from a stored procedure in a Fabric warehouse and to pass in parameters.  Is this even possible?  BTW, I also tried using parameters in my reports (from either a table or view), but could not get it to work.  Is there any documentation out there that I could follow?

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other.

Regards,
Rufyda Rahma | MIE 

[Trainor]

Hi @tayloramy,

Thank you for the quick response.  However, I have not figured out how to even build a query (i.e. SELECT <column> FROM <table> etc.), let alone attempt to run EXEC off of a stored procedure.  I have even tried this using our on-prem SQL Server in place of a Fabric warehouse.  

I get that this product was built from SSRS, but it certainly does not behave the same in certain aspects.  I will continue to search for maybe a YouTube video that might help, but I have little hope at this point.

Thanks,
Steve

[tayloramy]

Hi @Trainor, 

First you add a data source: 

I am using a SQL Server for this example as I have one handy

THen you add a dataset: 

and you can type in a query: 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Trainor]

Thanks again tayloramy,

That is the path I would normally take for creating SSRS reports which is something I tried earlier.  I tried it again and ran into a network issue which looked like it is on my end -- we are super strict with access.

If/when I can get past my access, I will give this another go just to see if I can:
1. Get parameters to work
2. run a stored procedure.

Then I will try to see if I can do any of this from a Fabric warehouse.  But I believe I might have to use DAX.

Thanks again for your persistence!!

Steve

[tayloramy]

Hi @Trainor, 

Yes, you will need to have the ports open on your computer to be able to connect to Fabric data stores. 

My org is also very strict with this, it took me 6 months to get firewall rules approved, and takes a few weeks every time I need to add a new device to those rules. 

Hopefully once you get the firewall stuff figured out you will be able to do this. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Trainor]

Again, thank you so much for being such a great help.  I was able to read from a Fabric table using parameters while no step by step documentation was available to me.  I also got it my parameters to be passed into a Fabric view.  I still want to read form SPs and to get SQL queries to work from reading out of a Fabric warehouse, but not today.  It must be some configuration on my end.

I can totally see how you made Community Champion and earned all the kudos!!  I gave you as many as these posts would allow me.

[Anusha66]

I have tried this, but when I am connecting to the data source as a fabric warehouse (which has stored procedures that I have created), its only showing up all the tables, Views and Functions. But not stored procedures of the warehouse.  Also tried by connecting this fabric warehouse through SQL Server End Point- that dint work too

[Trainor]

Thanks @Anusha66,
Yes, this is my experience as well.  My solution (for now) is to convert my SP to a view in order to access the data.
Thanks,
Steve

[Anusha66]

Hey ! I explored this more. You can actually get a Stored Procedure option. 

In data source, pls chose Azure SQL Database ( not fabric).. and use your sql server end point connection. and its working just like anyother sql server !

[Trainor]

Thank you for your persistnece Anusha66,

I now just need to figure out my connection settings and go from there.

Thanks again,

Steve

[v-tejrama · Accepted solution]

Hi @Trainor ,

Thanks for the update and for sharing your experience. What you are seeing is expected behavior today and not the result of a missing configuration or anything you are doing incorrectly. With Power BI Report Builder connected to a Fabric Warehouse, parameters work as designed when querying tables and views, including passing parameters into Fabric views.

However, stored procedures are not currently supported or reliably handled by the Fabric Warehouse SQL endpoint for schema discovery and execution in Report Builder. Because Report Builder cannot retrieve the result set metadata from stored procedures in this scenario, the dataset cannot be validated and parameters cannot be bound, even though the same procedure may run successfully in tools like SSMS or Azure Data Studio.

Converting the stored procedure logic into a view or function is the correct and supported workaround at this time. This is a known platform limitation rather than a tooling issue, so you should not expect stored procedures to work from Fabric Warehouses in Report Builder until support is added in the service.

Thank you.

[tayloramy]

Happy to help 

[v-tejrama]

Hi @Trainor ,

It is definitely possible to call a stored procedure from Power BI Report Builder, but the experience can feel a little different than classic SSRS, especially when you’re connecting through Fabric. On a normal SQL Server data source, you can either switch the dataset to Stored Procedure mode and enter the procedure name, or leave it in Text mode and type something like:
EXEC dbo.YourProcedure @Param1 = @Param1
Report Builder will pick up any parameters you reference and create them as dataset parameters, and you can map them to report parameters in the Parameters tab. If the stored procedure returns a regular result set, the fields should appear once you refresh them.

Where things sometimes break down is when the connection is pointed at a Fabric Warehouse. Not every Fabric SQL endpoint supports stored procedures yet, and in those cases Report Builder won’t be able to validate or execute EXEC statements. When that happens, the behaviour you described is exactly what you see. If you test the same stored procedure in SSMS using the identical connection string and it doesn’t work, that confirms it’s a Fabric limitation rather than something you’re doing wrong in the report. In those situations, the workaround is to expose the logic through a view or a table-valued function instead, since those return tabular data reliably and Report Builder can query them just like a regular table.

If you are still unable to run even a simple SELECT statement from Report Builder, that usually indicates either the wrong data source type, missing permissions, or a Fabric endpoint that doesn’t fully support the features you’re expecting. If you want to share the exact error you get when validating the dataset, I can help pinpoint exactly where it’s failing.

Thank you.

[tayloramy]

Hi @Trainor, 

In your dataset, can you use a query that does EXEC <stored proc name>?
I know I've been able to do this on prem before, but never tried in Fabric. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.