Pass Dynamic Parameter to SQL Stored Procedure in Power BI Direct Query Mode and Paginated Report

This is a not a new topic folks. There are numerous articles about this. This article though is a comprehensive end to end instruction and the pros and cons of three main solutions identified so far.

Use Case

Although Power BI guideline states that one should convert a stored procedure to a semantic model to leverage the star schema in Vertipaq engine as import mode, there is situation where we still want to make use of the SQL stored procedure for whatever reasons. Stored procedure can be consumed in both direct query and import mode, however, in the case of pass dynamic parameter to stored procedure, direct query mode is the only choice.

For those who wonder why, the reason is if you want to have user input in report UI and pass that input as parameter back to stored procedure in SQL server using bind to parameter feature in Power BI. This interactive requirement between user and the SQL table means it has to be in direct query mode.

The other question is why we use stored procedure as opposed a table, view or SQL query statement as data source. It is usually a legacy way of retrieving data. Some organizations have created stored procedure with dynamic parameters just to narrow down the data to be returned. Hence those stored procedures are the ones just to filter data using parameter like the where in select statement, not DDL or DML statement.

All right, we can say that the prerequisite of this use case is.

• Power Bi Direct Query Mode
• Dynamic parameter in stored procedure.
• Stored Prod is just a query statement

The Solution Walkthrough

Again, as there are numerous articles this before, For your benefit, I just summarize the steps of different solutions with the hyperlink here and elaborate the use case and practicality of each solution

1. Call the stored procedure with Openrowset in Power BI Direct Query Mode

The reason to use Openrowset is here if you are interested. I would just skip to the solution.

• Test the Openrowset statement in SSMS to see if it works as expected. This step ensure your code is error free and can be used in step 2.

--test the execution first
exec StoredProcedure 'Parameter1', 'Parameter2')

 --test openrowset to execute the stored procedure
SELECT * FROM OPENROWSET('SQLNCLI','server=ServerName ;trusted_connection=yes','exec DatabaseName.Schema.StoredProcedure " Parameter1", "Parameter2" ' )        

• Paste the Openrowset code into Power BI as SQL statement. This way, Power Query will automatically wrap SQL statement in M query. Don't spend time manually wrapping the SQL query in power query advance editor!

The following steps can be found in Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn. Therefore, I am not going to elaborate them here.

• Create a parameter in power query
• Replace the sample parameter with new parameter object.
• Select the column and bind to parameter
• Build the report with the bind to parameter column as slicer

The consideration of this solution is

1. Enable ad hot distribution in SQL server to be able to run Openrowset query
2. Data metadata needs to be defined using With Result Sets when multiple result set is expected or temp table is used in stored procedure.

For example, when you execute the Select * from Openrowset () code in SSMS as first step (clean slate without Power BI), the following error message come up indicating that the metadata is not defined in the result.

Interestingly, the same stored prod that is executed using exec StoredProdName will not generate the same error message. without drill into the reason, let's just fix it adding the With Result Sets.

--To fix the issue, need to use With Result Set to define the metadata. 
SELECT * FROM OPENROWSET
('SQLNCLI','server=ServerName ;trusted_connection=yes','exec DatabaseName.Schema.StoredProcedure " Parameter1",  "Parameter2" 
WITH RESULT SETS
    (
        (
		 Column1 Type,
                 Column2 Type ,
                 ....
       )
    )'
 )        

2. Convert Stored Procedure to function

Chris Webb has a detailed blog to talk about this method. The prerequisite is that one has to convert stored procedures into function. This still a brilliant idea to avoid using the relatively long Openrowset code, given the similarity in function and stored procedure.

The only drawback is for complicate stored procedure that has temp table, you have to convert the temp table as CTE expression which is extra work.

3. Call Stored Procedure in Paginated Report

You will be surprised how straightforward stored procedure is called in paginated report, it will automatically recognize the parameter once you select the stored procedure. I guess the reason is stored procedure as data source is an old way to filter data in a report before the introduction of semantic model as analytical layer, paginated report (aka SSRS in the old day) is the perfect match for stored procedure as it leverages the dynamic parameter in the stored procedure. in other word, you can treat stored procedure as a dynamic "data model" in SQL database based on parameter.

The use case here is for those report that has very limited interactive requirement and more focus on data export or static type of report. e.g. customer invoice or statement, data export.

let's dive into it.

• First create a new data source

• 2. Create a dataset from the new data source.

• 3. from the new list, select the newly create data source, stored procedure as query type, then enter the stored procedure name or select it from the dropdown list. click ok.

4. Now double click on the newly created dataset, then select parameter section. You will be able to find the stored procedure input parameters appear here and also in the report parameter folder on the left.

now the setup is ready and you can unharness the powerful parameter and expression in paginated report to achieve things like cascading parameter, parameter linking etc.

The limitation of dynamic parameter is the UI (user interface). You can put the parameters into order, but you can't group them together based on category, nor you can change its style from drop down list to other types like vertical list, between for date or tile. In addition. The same parameter pane will appear in the different page regardless of content and page selection. Overall, dynamic parameter in stored procedure is well-supported in paginated report, although its UI is pretty preliminary compared to the function of Slicer in Power BI.

The Conclusion

Ok folks, if you want to enjoy dynamic parameter in the stored procedure as data source, you need to first answer the question of what end report result you want to achieve.

If you want to have interactive report experience and don't mind enable ad-hoc distribution in SQL server, direct query in Power BI with Openrowset feature is the way to go. Or if you don't mind converting the stored procedure into function by converting the temp table into CTE. direct query in power bi with function is the way to go. these 2 steps are 2 branches of the same root eventually as it requires bind to parameters in report level.

If your report has limited or no interactive requirement or you just need it for data extract, paginated report is the perfect match as it fully supports the parameter set up and configuration.

Lastly, it worths mention that compare to other BI tool in the market, Power BI is strong at its data modelling capabilities in Vertipaq engine in import mode. With time and resources allow, I will recommend converting the stored procedure into a semantic model to fully take advantage its capabilities. Afterall, import mode guarantee the best performance and less hassle in report development down the track.

What do you think? If you find this topic helpful, thumb up or leave comment if you have any questions or ideas.