Forum Discussion

Trainor's avatar
Trainor
Frequent Visitor
8 months ago
Solved

Power BI Report Builder

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?

  • Anonymous's avatar
    Anonymous
    8 months ago

    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.

14 Replies

  • tayloramy's avatar
    tayloramy
    Icon for Super User rankSuper User
    8 months ago

    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.

  • Trainor's avatar
    Trainor
    Frequent Visitor
    8 months ago

    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's avatar
      tayloramy
      Icon for Super User rankSuper User
      8 months ago

      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's avatar
        Trainor
        Frequent Visitor
        8 months ago

        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