Past releases in power bi have seen new enterprise related capabilities coming out which lets you manage power bi usage within your organisation. Few such feature that came out with Power BI desktop April update is query parameters and template files. In this blog post we will look at how to utilise those two features within power bi desktop to meet specific organisation needs.
Say for example you work for an organisation that has many divisions and each divisions has its own database. Within those divisions you have power bi users that uses their division specific database and develop reports. You as an administrator would like to have some capabilities to manage those reports such as apply version control, organisation specific branding, templates and layout. It would also make sense to have a central repository for each pbix file that users would develop for their division as currently if you lose pbix file and report is published, there is no way to recover that report from power bi service except create a new one. Using template files and query parameters you will be able to have a solution for above mentioned scenarios.
Query parameters lets you create parameters that can be used as a data source within your reports. For this post I am going to assume you have SQL Server database as your data source.
In power bi desktop click Edit Queries. In Home ribbon, under Parameters tab click Manage Parameters dropdown and select New Parameter.
In parameters window fill appropriate details as shown below with Required checkbox ticked, Type as ‘Text’ and ‘Any value’ for Allowed Values. Provide sql server and instance name in format of servername\instancename in Current Value field and click OK.
Click again on Manage Parameters -> New Parameters and create new parameter as shown below.
Next click on New Source -> SQL Server Database -> for server dropdown choose Parameter as ConnDetails and for Database dropdown choose Parameter as DatabaseName and click OK. Provided power bi was able to make connection you will see navigator with database objects. Choose tables you require and click OK. Finally click Close & Apply at the top left hand of query editor.
At this stage you have parameterised data source in power bi. Build your visuals and save pbix file. Next you need to create template file that can be distributed to other users.
Introduced in power bi desktop April update, template files (.pbit) are similar to your pbix file except without data. So think of it as a template for your reports that you can distribute to your users and they just need to open it in power bi desktop, connect it to their data source and publish. As an administrator you will develop reports and layout which your users don’t need to worry about. This particular feature is very useful when you have a large organisation and lot of users using power bi to develop and publish reports. This way you will have control over not just layout of the reports but individual users won’t have to worry about maintaining pbix files as one template file can be maintained and distributed as required.
Open report you created above in power bi desktop.
Next click on File -> Export -> Power BI Template.
Provide template description -> OK -> save pbit file.
Once saved power bi template files with query parameters can be distributed to other users. Users when open template file will be prompted to enter parameter values based on parameter type such as dropdown or text field. In our example, user will be prompted to enter ConnDetails and DatabaseName paramters as both are text parameters.
Once parameter values are filled power bi will connect to data source and all visuals will appear.