Generating a PDF from a PowerBI report is simple, there is even a button for that. Or you can subscribe to one and it appears daily in your mailbox. The use-case is very common and easy to implement, but what if you want to create a file version of a report, for every product you have, or every customer, or any other arbitrary list of values that can change the full content of that report.

Apart from Report Builder and perhaps paginated reports, I didn’t come across many options. With Report Builder you can use PowerBI datasets as source, but then have to go through the whole layout experience again, link in parameters with DAX queries, and you’re off for a long and painful development, while you already have that datamodel available and a good tool to design reports (PowerBI Desktop).

Here comes my approach: automate it using the PowerBI REST APIs. This allows you to request an export of a report to file. In addition, you can specify the file type, but more importantly, you can specify filters for the report as well as specify which report page you’d like to export. Major drawback for some: it will not work on PPU license or Pro, you’ll need a Premium capacity for it (based on my tests).

The steps are the following:

And this we’ll do in a loop for each item you want to generate a report about.

Let me walk you through this in more detail

What do I need

Apart from the Premium capacity, you’ll need:

  • a PowerBI tenantId (can be retrieved in PowerBI service, help icon, About PowerBI, the Tenant URL contains the Id after ctid)
  • a PowerBI workspace (note down the workspaceId from the report URL (the GUID after groups when you open the workspace in the browser, or the report. e.g. https://app.powerbi.com/groups/9e555b37-2b4a-bb9a-4cd2-2425b3560ae6/reports/4cd2-2425b3560ae6-9e555b37-2b4a-bb9a/ )
  • a report you designed, which will be the blueprint for the exports (note down the reportId, again in the URL after reports this time)
  • a page in the report (in case you have multiple pages and want to extract just one). Again, this is found in the URL, after the reports GUID.
  • a service principal (check my other PowerBI articles on how to set up and use it), note down the clientID and the secret + provide access (see troubleshooting below)
  • a list of items you want to generate the report for, for simplicity I store them in a file called items.txt, one on each line

In Powershell we’ll list this in the beginning

$servicePrincipalId = "as noted above"
$servicePrincipalSecret = "as noted above"
$tenantId = "as noted above"
$workspaceId = "as noted above"   
$reportId = "as noted above"   
$pageName = "as noted above"

Starting the export

You can use the Export To File API for reports located in your personal workspace ( Reports – Export To File ), but I’ll use the more common Export To File In Group, as in many organisations, you’d have reports published in various workspaces (=groups), which can be found here : Reports – Export To File In Group

Connect to PowerBI

First we’ll have to connect to PowerBI with following Powershell statements:

Install-Module -Name MicrosoftPowerBIMgmt 
Install-Module -Name MicrosoftPowerBIMgmt.Profile

$SecureStringPwd = ConvertTo-SecureString $servicePrincipalSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($servicePrincipalId, $SecureStringPwd)
Connect-PowerBIServiceAccount -ServicePrincipal -Tenant $tenantId -Credential $credential

Export to PDF

The following Powershell code shows what needs to be in place to request the extract to start. Note that $workspaceId and $reportId are defined earlier.

$exportToFileURL = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/ExportTo"

$headers = Get-PowerBIAccessToken
$headers.Add("Content-Type", "application/json")
$exportBody = @"
{
      format: "PDF"
}
"@
$response = Invoke-RestMethod -Headers $headers -Uri $exportToFileURL -Body $exportBody -Method 'Post'

For now, I’ll start with just specifying the format and expand on that with filters and pages later on. Other file types are available according to the documentation, so you could try any of these

ACCESSIBLEPDFstringAccessible PDF (only supported for paginated reports)
CSVstringCSV (only supported for paginated reports)
DOCXstringMicrosoft Word (only supported for paginated reports)
IMAGEstringBMP, EMF, GIF, JPEG, PNG, or TIFF image formats (only supported for paginated reports)
MHTMLstringMHTML (only supported for paginated reports)
PDFstringPDF
PNGstringPNG (only supported for Power BI reports)
PPTXstringMicrosoft PowerPoint
XLSXstringMicrosoft Excel (only supported for paginated reports)
XMLstringXML (only supported for paginated reports)

Specify Page to export

You can specify to export a specific page from the report in below pageName attribute. This can be found as part of the URL you see after /reports/report-GUID/pagename when opening a PowerBI Report. The pretty name of the page itself doesn’t work.

{
    format: "PDF",
    powerBIReportConfiguration : {
        pages : [ { pageName : "ReportSection05c145645664df51" } ]        
    }
}

Specify Filters to apply to report

In order to customize the report to only show data for one specific user, we have to tell the API to apply a filter, which will be transparently applied as if you were selecting a slicer in the report. The dataset now gets filtered by the specified filter, which is in the form of Table/Field eq ‘value’
Note: all parts are important, the quotes, the tablename, etc… More on filtering PowerBI reports can be found here : Filter a report using query string parameters in the URL This also applies to the filter attribute below.

To test the filter you want to apply to your report, just open the report in PowerBI service and add ?filter=(filter) to it, e.g.

https://app.powerbi.com/groups/xxxxxxxx/reports/xxxxxxxxx/ReportSectionxxxxx?filter=Employee/ID eq ‘554433’ and Signed_x0020_Date/Year eq 2021

More syntax can be found on the above link (Filter a report using query string parameters in the URL)

In the request body, we’ll add this filter as well in the reportLevelFilters node

{
    format: "PDF",
    powerBIReportConfiguration : {
        pages : [ { pageName : "ReportSection05c145645664df51" } ],
        reportLevelFilters :  [ { filter : "Products/Item eq '$item'" }]
    }
}

Checking up on the export

In the response of the export API call you get a exportId returned, which you’ll use to check the status of the report:

   $response = Invoke-RestMethod -Headers $headers -Uri $exportToFileURL -Body $exportBody -Method 'Post'  
   $exportId = $response.Id

   # check export status
   $checkExportURL = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/exports/$exportId"
   $currentStatus = Invoke-RestMethod -Headers $headers -Method GET -Uri $checkExportURL
   $wait = 5 #seconds

   Do
   {
      if ($currentStatus.status -eq "NotStarted" -or $currentStatus.status -eq "Running"){
        Write-Host("Sleeping $wait seconds...")
        Start-Sleep $wait
      }
      $currentStatus = Invoke-RestMethod -Headers $headers -Method GET -Uri $checkExportURL

      Write-Host("Percent Complete: " + $currentStatus.percentComplete + " | Last Status: " + $currentStatus.status)
   } While ($currentStatus.status -eq "NotStarted" -or $currentStatus.status -eq "Running")

The code will poll the PowerBI API until the export is not longer Running, so either Completed or another error. For simplicity we’ll assume it’s completed.

Downloading the file

When the status is complete, the resourceLocation will contain the URL where the file can be downloaded. Also during that call you’ll need to provide the token in the headers, be aware of this when you simply replicate this in Postman.

$downloadURL = $currentStatus.resourceLocation
$outputFile = "performance-$item.pdf"
Invoke-WebRequest -Uri $downloadURL -OutFile $outputFile -Headers $headers

Finally

Putting it all together just requires to add a loop that reads all lines from the items.txt file and pipes them into the ForEach-Object, so we have a loop for each line in the items file. That item will be used in the filter, and the file downloaded at the end will contain the item in the filename, so you can create unique files. I also write the item to a separate item file, to keep track of which reports were already generated and potentially remove them from the input items.txt

Complete script

Install-Module -Name MicrosoftPowerBIMgmt 
Install-Module -Name MicrosoftPowerBIMgmt.Profile

$servicePrincipalId = "as noted above"
$servicePrincipalSecret = "as noted above"
$tenantId = "as noted above"
$workspaceId = "as noted above"   
$reportId = "as noted above"   
$pageName = "as noted above"

# Connect with service principal
Write-Host("Connecting to PowerBI")
$SecureStringPwd = ConvertTo-SecureString $servicePrincipalSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($servicePrincipalId, $SecureStringPwd)
Connect-PowerBIServiceAccount -ServicePrincipal -Tenant $tenantId -Credential $credential  

Get-Content C:\exports\items.txt | ForEach-Object {
   $item = $_

   # start Export to file
   $exportToFileURL = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/ExportTo"

   $headers = Get-PowerBIAccessToken
   $headers.Add("Content-Type", "application/json")
   $exportBody = @"
{
      format: "PDF",
      powerBIReportConfiguration : {
        pages : [ { pageName : "$pageName" } ],
        reportLevelFilters :  [ { filter : "Products/Item eq '$item'" }]
    }
}
"@

   Write-Host("$item- Starting export")
   $response = Invoke-RestMethod -Headers $headers -Uri $exportToFileURL -Body $exportBody -Method 'Post'  
   $exportId = $response.Id

   # check export status
   $checkExportURL = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/exports/$exportId"
   $currentStatus = Invoke-RestMethod -Headers $headers -Method GET -Uri $checkExportURL
   $wait = 5

   Do
   {
      $currentStatus = Invoke-RestMethod -Headers $headers -Method GET -Uri $checkExportURL

      Write-Host("Percent Complete: " + $currentStatus.percentComplete + " | Last Status: " + $currentStatus.status)
    
      if ($currentStatus.status -eq "NotStarted" -or $currentStatus.status -eq "Running"){
        Write-Host("Sleeping $wait seconds...")
        Start-Sleep $wait
      }
   } While ($currentStatus.status -eq "NotStarted" -or $currentStatus.status -eq "Running")
   Write-Host("$item- downloading")

   # retrieve the file
   $downloadURL = $currentStatus.resourceLocation
   $outputFile = "report-$item.pdf"   
   Invoke-WebRequest -Uri $downloadURL -OutFile $outputFile -Headers $headers


   Add-Content C:\exports\items_generated.txt -Value $item
}

What could go wrong

When you get a 403 error in the return of a call this is typically for one of the below reasons

Permissions

API Permissions

As specified in the API (but often overlooked), the account needs Dataset.ReadWrite.All (or Dataset.Read.All) and Workspace.ReadWrite.All (or Workspace.Read.All) to start the export.

To get the export status, you also need the Report.ReadWrite.All (or Report.Read.All) permission, which for a service Principal can be added in the App Registration, API permissions, add permission, PowerBI Services, delegate permissions.

PowerBI Permissions

Make sure the service principal is in the tenant allowed to make API calls
Make sure the service principal is Admin on the workspace containing the report you’re exporting.

API call issues

Also make sure you are actually passing the token in the header of the API call to get the export status.

When finally retrieving the exported image, again make sure you also provide the token in the API call as Bearer token.

Note: RLS secured reports cannot be exported with service principal