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