Handling Parameterised Stored Procedures while migrating SSRS to Fabric

Hi max_mrc ,

Building on what v-saisrao-msft mentioned, here are a few practical approaches depending on your scenario:

Option 1: Dynamic M Query Parameters + DirectQuery (closest to SSRS)
This gives you the most SSRS-like experience. You create Power Query parameters, reference them in your M query, then bind slicer fields to those parameters in Model view. When a user selects a value from the slicer, it gets passed into the SQL sent to your database — similar to how SSRS parameters work at runtime.

One gotcha with stored procedures specifically: you'll likely need to wrap your EXEC call in OPENQUERY/OPENROWSET, because a plain parameterized stored procedure call works fine in Power Query Editor but fails when the report actually renders. The M query ends up looking something like:

Source = Sql.Database("server", "db", [Query="SELECT * FROM OPENROWSET('SQLNCLI','trusted_connection=yes', 'exec YourDB..YourProc ''" & YourParameter & "'''")"])

Option 2: Convert stored procedures to Table-Valued Functions
If you control the database, consider converting your parameterized SPs to inline table-valued functions. They work much more naturally with DirectQuery and Dynamic M Parameters — no OPENQUERY workaround needed. 

Option 3: Paginated Reports in Fabric
If you need the reports to behave almost identically to SSRS (parameter prompts, pixel-perfect layout, export to PDF/Excel), paginated reports in Fabric are your path of least resistance. They support traditional parameters natively and can call stored procedures with user-supplied values just like SSRS did. You can even embed them inside regular Power BI reports using the Paginated Report visual.

For your specific parameters:
- @date — works well with any of the above. For Dynamic M Parameters, bind a date slicer to the parameter.
- @RunNumber — create a small lookup table of valid run numbers (can be imported), use it as a slicer, and bind that to the M parameter.

The right choice depends on data volume and how interactive the reports need to be. For a quick migration with minimal rework, paginated reports are easiest. For a more modern interactive experience, Dynamic M Parameters in DirectQuery is the way to go.

Hope this helps!

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Hello max_mrc 

You can invoke stored procedures with parameters using a pipeline activity. The stored procedure can easily write to a Lakehouse or a Warehouse table that drives the Power BI report. Microsoft Fabric pipelines can be executed on specific events, not just on a schedule or manually.

The stored procedure parameters in the activity allows you to supply dynamic parameters too!

HI max_mrc,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

Hi max_mrc,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.