Forum Discussion
Solved
Passing Report Builder Parameters to a Stored Procedure Through a Power Query Data SourceMy challeng
My challenge is that I need to pass parameter values entered in the paginated report (for example, Start Date, End Date, Customer ID, etc.) to the stored procedure through the Power Query connection...
- What can work in this case is understanding how Report Builder and Power Query interact. In practice, Power Query is evaluated as part of the dataset definition, and unlike a direct SQL dataset, it does not natively accept report parameters at runtime in the same way.Because of that, passing Report Builder parameters dynamically into a stored procedure through a Power Query data source is generally not supported in a direct way.What can happen is that Power Query parameters are resolved before the paginated report execution, so they cannot be bound dynamically to report parameters during runtime.What can work instead is a workaround approach.One option is to define Power Query parameters and reference them in your M query (for example inside a Value.NativeQuery or a query that calls the stored procedure). However, those parameters need to be set at the dataset level, not passed directly from the report UI.Another approach, and usually the more reliable one, is to avoid Power Query for this specific case and use a direct dataset connection (SQL or stored procedure) where Report Builder parameters can be mapped directly. That is the native behaviour of paginated reports.If you must stay with Power Query, a common workaround is to:
- expose the parameters as part of the query logic (for example via a staging table or function)
- then filter the data model using those values after retrieval
But this means the filtering is not truly pushed down to the stored procedure at runtime.There are also some limitations worth noting. Stored procedures with Power Query sources are less flexible in parameter handling, and features like dynamic query folding or true parameter binding are not guaranteed.So in practice, this is less about configuration and more about engine limitations.In short, it is not really possible to dynamically bind Report Builder parameters to Power Query parameters at runtime in the same way as a native SQL dataset. The more robust approach is either to switch to a direct connection or redesign the logic so that parameters are handled outside of Power Query.If you want, you can share your current M query, and I can suggest a pattern that gets as close as possible to dynamic behaviour within those constraints.
Zanqueta
Super User
Super UserWhat can work in this case is understanding how Report Builder and Power Query interact. In practice, Power Query is evaluated as part of the dataset definition, and unlike a direct SQL dataset, it does not natively accept report parameters at runtime in the same way.
Because of that, passing Report Builder parameters dynamically into a stored procedure through a Power Query data source is generally not supported in a direct way.
What can happen is that Power Query parameters are resolved before the paginated report execution, so they cannot be bound dynamically to report parameters during runtime.
What can work instead is a workaround approach.
One option is to define Power Query parameters and reference them in your M query (for example inside a Value.NativeQuery or a query that calls the stored procedure). However, those parameters need to be set at the dataset level, not passed directly from the report UI.
Another approach, and usually the more reliable one, is to avoid Power Query for this specific case and use a direct dataset connection (SQL or stored procedure) where Report Builder parameters can be mapped directly. That is the native behaviour of paginated reports.
If you must stay with Power Query, a common workaround is to:
- expose the parameters as part of the query logic (for example via a staging table or function)
- then filter the data model using those values after retrieval
But this means the filtering is not truly pushed down to the stored procedure at runtime.
There are also some limitations worth noting. Stored procedures with Power Query sources are less flexible in parameter handling, and features like dynamic query folding or true parameter binding are not guaranteed.
So in practice, this is less about configuration and more about engine limitations.
In short, it is not really possible to dynamically bind Report Builder parameters to Power Query parameters at runtime in the same way as a native SQL dataset. The more robust approach is either to switch to a direct connection or redesign the logic so that parameters are handled outside of Power Query.
If you want, you can share your current M query, and I can suggest a pattern that gets as close as possible to dynamic behaviour within those constraints.