#PowerBI – External Tool – Open Power BI Report Builder – part 1

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.

  • Analyze in Excel (link)
  • Open in Tableau (link)

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 powershell

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>

What happens

After you have placed the OpenInPowerBIReportBuilder.pbitool.json in the correct folder and you have restarted Power BI Desktop

the toolbar button

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

Paginated Report Bear (@PaginatedBear) | Twitter

Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI

I recently created an external tool to PowerBI desktop that connects your Power BI desktop model to Excel (https://eriksvensen.wordpress.com/2020/07/27/powerbi-external-tool-to-connect-excel-to-the-current-pbix-file/) and then I thought – could we also have a need for an external tool that could open the desktop model in Tableau desktop.

So, I downloaded a trial version of the Tableau Desktop to see what is possible.

And sure, enough Tableau can connect to Microsoft Analysis Services and therefor also the localhost port that Power BI Desktop uses.

We can also save a data source as a local data source file in Tableau

Which gives us a file with a tds extension (Tableau Data Source)

When opening the file in Notepad we can see the connection string and some extra data about metadata-records.

It turns out that the tds file does not need all the meta data record information – so I cleaned the tds file to contain

Opening this file from the explorer will open a new Tableau Desktop file with the connection to the specified model/database/server.

The external tool

Knowing this I could create an external tool the same way as my Excel connector.

First create a PowerShell

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

The PowerShell script

Function ET-TableauDesktopODCConnection
{  

	[CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $false)]        
		[string]
        $port,
        [Parameter(Mandatory = $false)]        
		[string]
        $database,
        [Parameter(Mandatory = $false)]        
		[string]
        $path	
    )
    
        $tdsXml = "<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='LocalPowerBIDesktopFile' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'>
  <document-format-change-manifest>
    <_.fcp.SchemaViewerObjectModel.true...SchemaViewerObjectModel />
  </document-format-change-manifest>
  <connection authentication='sspi' class='msolap' convert-to-extract-prompted='no' dbname='$database' filename='' server='$port' tablename='Model'>
</connection>
</datasource>"   
                
        #the location of the odc file to be opened
        $tdsFile = "$path\tableauconnector.tds"

        $tdsXml | Out-File $tdsFile -Force	

        Invoke-Item $tdsFile

}

ET-TableauDesktopODCConnection -port $args[0] -database $args[1] -path "C:\temp"

The script simply creates a tableauconnectort.tds file and stores it in C:\temp – and the xml content in the file is dynamically referenced as arg(0) and arg(1) when the external tool is called from Power BI Desktop.

Save the script in C:\temp and call it ConnectToTableau.ps1.

The OpenInTableau.pbitool.json file

Next step was to create a pbitool.json file and store it in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

{
  "version": "1.0",
  "name": "Open In Tableau",
  "description": "Open connection to desktop model in Tableau ",
  "path": "C:/Windows/System32/WindowsPowerShell/v1.0/powershell.exe",
  "arguments": "C:/temp/ConnectToTableau.ps1 \"%server%\" \"%database%\"",
  "iconData": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJAAAACQCAYAAADnRuK4AAAABmJLR0QA/wD/AP+gvaeTAAADRklEQVR4nO3dv27TUBiH4WPEitSRS+iCurO0GzdRiS5sXRhAXZhYEAxd2LoUiZtgaxb2iqWXwFiJCzgsqPRPrMb5Jc1x/TxbqgSi5O2xE3+uSwGAUeo2/QRac3R8cla6bvfqB7XOPr19s7e5Z9S2J5t+AoybgIgIiIiAiAiIiICICIiIgIgIiIiAiEziUMbR8cnZovetXbfTlbJ1dbuUy67W80UfP7XDHk83/QQexPVjW/fd9e7trSGPnxqbMCICItLEJqyeljrv593BivbRap0tfNdwH2hVDj58mfuanH5819R+axMBrduQHdvb80BdredT2zEewiaMiICICIiIgIgIiIiAiAiIiICICIiIgIhM4lDGEA5bDGMFIiIgIgIiIiAiAiISTbf1TRK2ZmWTjQvomyRszaomG61ARAREREBEBEREQESaOMdo7eeFjdBYzguzAhEREBHjHP/8fv/i3i8An3/+1dTmowVWICICIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiSx8Lc3Xjcdk/nJ2VWv+/X103+/51dy/9d61ARAREpIlxjilPHvZpbfKwjxWIiICICIiIgIgIiEgTn8KGWmQAfiz/79gH9a1ARG7UP5arG29qBVqHZAXaP5ydDbj7Tqn16v0qXXdZSln4/eo77HFzE+bqxuNy/djW8MdulVLi98smjIiAiNzchI3w6saT1nULv18l3AfqfQrLPnCT80B2ooczD0STRvlF4jp+a/11juVYgYgIiIiAiAiIiICINPEp7Of29txPQC8vLib7qefZq29zX5M/P1439ZpYgYgIiMjSmzCnMY/LKg5bzGMFIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiAiLSxDhHCwzML8cKRERARKJlu2+SsDUPOdnYN0nYmlVNNlqBiAiIiICICIiIgIg08eWZ88Lucl4YkyAgIgIiIiAiAiJinOOWdf0108fKCkREQEQERERARAREREBEBEREQEQERERARCZxKGPw1Y1v3R7y+Kkd9mgioLVPHjZwdeOhWps87GMTRkRARJrYhK1dA1c3fqxGsZ19SOaBhrEJIyIgIgIiIiAiAiIiICICIiIgIgIiIiAAAAAYjb8VJdQbiRXyOAAAAABJRU5ErkJggg=="
}

Test it

Now restart your Power BI desktop and the external tool should be visible in the ribbon

Then open a pbix file with a model and hit the button.

A PowerShell screen will shortly be visible and then Tableau opens the tds file and now we have a new tableau book with a connection to active power bi desktop datamodel.

And we can start to do visualizations that are not yet supported in Power BI –

How can you try it

You can download the files needed from my github repository – link

Feedback

Let me know what you think and if possible share some of the viz that you make.