As you may have noticed I have blogged twice about the new awesome feature in Power BI Desktop where we can build our own external tool buttons in Power BI Desktop.
Here is a link to the previous posts.
One perhaps forgotten member in Power BI is the Power BI Report Builder – aka – Paginated reports – and even though it requires a premium capacity or Power BI embedded A-SKU to publish/share reports – I thought it might be interesting to see if we could link the local pbix file to the Report Builder.
I will write a part 2 where the external tool will support if the desktop file is connected to an Azure Analysis Server or Power BI Dataset as well.
You can download the free Power BI Report Builder from here.
How to build the external tool
The tool has two files
- A powershell file – ConnectToPowerBIReportBuilder.ps1
Must be stored in C:\temp
- A external tool file – OpenInPowerBIReportBuilder.pbitool.json
Must be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
OBS – in order to run a powershell script on your pc you need to have to set the execution policy – https://go.microsoft.com/fwlink/?linkid=135170
You can download the files from here – https://github.com/donsvensen/openinpowerbireportbuilder
The report builder uses the rdl – file format which is an xml file.
So the PowerShell script is simply to create an rdl file and make the connection dynamic using the parameters about server and database name that the External tool provides as arguments when the button is clicked in Power BI Desktop.
This is a small part of the xml that creates the rdl file and it could be expanded to create datasets and insert visualizations.
$tdsXml = "<?xml version=""1.0""?> <Report MustUnderstand=""df"" xmlns=""http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"" xmlns:rd=""http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"" xmlns:df=""http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily""> <rd:ReportUnitType>Mm</rd:ReportUnitType> <rd:ReportID>cd1b0079-99d9-4e99-9f5e-6fda21f9f09d</rd:ReportID> <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name=""PowerBI""> <rd:SecurityType>Integrated</rd:SecurityType> <ConnectionProperties> <DataProvider>OLEDB-MD</DataProvider> <ConnectString>Data Source=$port;Initial Catalog=$database</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> <rd:DataSourceID>8c230b7c-799c-4007-af55-6bc26eca797c</rd:DataSourceID> </DataSource> </DataSources> <ReportSections> <ReportSection>
After you have placed the OpenInPowerBIReportBuilder.pbitool.json in the correct folder and you have restarted Power BI Desktop
You will see a new button in the ribbon.
When you have a pbix file open containing a datamodel and click the button a desktoptobuilder.rdl file will be generated in C:\temp
And Power BI Report Builder will open – in the xml I have also inserted text in the report header showing the local host port.
In the data Sources you will find
A data source called PowerBI which is connect to your running instance of tabular model
And then we can create a dataset that uses the data source
And use the designer to create the DAX query
And use this to in a chart
Obs – remember to save the file with another name – and to update the connection information after you close and reopen the desktop file.
In my next post I will demonstrate how we can do the same if the pbix is connected to Azure Analysis Services or a Power BI Dataset.
Stay tuned 🙂
PS – This one is for #paginatedreportbear
7 thoughts on “#PowerBI – External Tool – Open Power BI Report Builder – part 1”
Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers
Reblogged this on Nishant Rana's Weblog.
Pingback: #PowerBI – External Tool – Open Power BI Report Builder – part 1 - 365 Community
thanks, very useful.
can i do this with an exisiting .rdl? so i can use the data from this .pbix?
You just add a new datasource to the existing rdl file – or modify the connectionstring for an existing datasource in your rdl
thanks for the quick response.
I think i didnt asked my question the right way.
I want to use external tools to change the connectionstring for an existing .rdl?
For example: Lets say i created a Mickey.rdl with a connection to a Mickey.pbix. After that i closed both files. The next day i refreshed the Mickey.pbix and now i want to have the new data in the Mickey.rdl but instead of changing the connectionstring manualy i want a button in the external tools ribon to open the Mickey.rdl and connect to the Mickey.pbix with the refreshed data?
Maybe you can help me with this one. This would be a comfortable way to do it (imo).
Hi Richard, it can be done – the powershell script should then be expanded to include a file selector and then modify that files datasource element in the xml