Last updated on April 27th, 2024 at 10:27 am
Hi everyone! This blog post is for anyone who wants to learn more about Power BI parameters.
This is my first blog post for Enhansoft, so let me give you a bit of background about myself. In the past, I worked with Tableau and IBM Cognos Report Studio and I’d say that I have a good working knowledge of both products. They are both similar to Power BI visualization and SSRS reporting applications respectively. Tableau also uses parameters in IT visualizations, so I feel the concepts in these applications are similar when building reports.
Here at Enhansoft, I’m now creating Power BI reports on computer monitor information for Enhansoft Reporting. While working on this Power BI report set, I had an idea to create and configure the parameters, so I would like to share the steps I took with you.
Power BI Parameters
The Parameters feature allows users to define one or more parameter in their Power BI Desktop layers (data, model and report layers). For example, when retrieving data from SQL Server, you can use a parameter to define what SQL Server name and SQL Server instance name you want used, and you can add a second parameter to identify the target database.
These parameters are independent of any datasets, so you can create them either before or after adding a dataset. You must, however, configure them and set their initial values in Query Editor. The parameters you create are listed in the Queries pane, where you can view and update their values, as well as reconfigure their settings.
Creating Power BI Parameters
In this section, see how I created two connection parameters for retrieving data from a SQL Server database.
SQL Server Name
The first parameter includes the SQL Server name that hosts the source database.
From the Power BI main screen, under the Home ribbon, click on the Edit Queries option.
In the Edit Queries main screen, click on Manage Parameters in the ribbon.
Once the Parameters dialog box opens up, click on the New option to begin.
On the right-hand side, start entering the details of the parameter. Type a name for the parameter under Name. In the description box, mention what value you expect the user to enter for the parameter.
The Type option refers to what kind of datatype is to be assigned to the parameter. In this example, I chose, “Any,” for the name of the servers. This gives more flexibility because sometimes they could be alpha-numeric, etc. You can also choose to make these values either required (mandatory) if you check the Required field or, if left unchecked, optional.
Later in this blog post, I give a brief explanation about the rest of the fields in the Parameter dialog box.
At this stage, the parameter for the SQL Server name (SQL Server Name- ‘cm-cas-cb1’) is successfully created, so let’s continue.
Target Database
For my second parameter, I followed the same first two steps (click on the Edit Queries option, and then click on Manage Parameters) in order to get to this point in the above screenshot. Next, I completed the fields in the Parameter dialog box. This is how it looked once I finished updating the fields for the target database (Target Database- cm_cb1).
Power BI Parameter Dialog Box
Here’s a brief explanation, from me, about the fields found in the Power BI Parameter dialog box. If you need an in-depth understanding about the same, you can check out the Deep Dive into Query Parameters and Power BI Templates blog post by Miguel Llopis that I found on the Microsoft Power BI page.
Parameter Name: Assign a name to the parameter.
Parameter Description: This will be shown next to the parameter name and helps the user who is specifying the value to better understand the purpose of this parameter.
Optional vs. Required: Users can specify whether a certain parameter is optional, or a value for that parameter must be given (mandatory).
Parameter Type: This detail applies a Data Type Condition on the input for the parameter. For example, users can define what datatype is to be assigned to the input parameter which could be text, an integer, a date, etc.
Suggested Values: This option allows you set what will be used for the default value. You can choose any value, define a list of values or you could even use a query for the value. Once you select an option, other than Any Value, the Default Value field (see below) is exposed.
Default Value: To set the default value use this field.
Current Value: This detail allows users to specify the value for this parameter in the current report.
Configuring Power BI Parameters
Newly created parameters are displayed under the Queries pane in the Edit Queries window.
Notice the two parameters I created for the SQL Server name and the target database name? The values are in the parenthesis. When you select a parameter, the query editor displays the current value information and the Manage Parameter button. See above for the values of each of my parameters.
In order to ensure that your parameters are set up correctly, under the Query settings panel, on the right-hand side of the screen, double-click on the source option. This opens up the connection dialog box for the native query. Here you see my server and database names.
I want these newly created Power BI parameters in the connection page. This lets Power BI know that the user is expected to enter the server name and the target database name of his/her choice once they use this template.
In order to do this, you need to change the datatype. Start on the left-hand side of the Server box, click on the down arrow. Next, click on the parameter icon and the Server box will change to a drop-down menu where all of the parameters are displayed.
Choose the appropriate values for the respective parameters and click OK to close the dialog box.
I saved this as a template, so I can re-use it across any SQL Server to connect with the associated target database.
Do you want to know more about Power BI templates? I show you how to create one in my next blog post, How to Create Power BI Templates.
Conclusion
Power BI parameters are a great way to define a data-source or its connections. With the ease of adding or configuring parameters before or after the data is loaded, the applications of this feature from Microsoft are endless.
I hope you find that this post about Power BI parameters helps you to create more effective reports and dashboards.