Today, my good friend asked me how to upload RDL file(s) to Power BI.
And my answer was "What? Power BI supports RDL now???" :)
At the end, this is my research results.
RDL on Power BI (premium)
Yes, it's true that Power BI Online supports RDL as "Paginated Report".
I followed Tutorial: Create a paginated report and upload it to the Power BI service to learn how to do it manually.
How to upload RDL via API
Now time to research how to upload RDL. Power BI team provide comprehensive API documentation at Power BI REST APIs
I looked through the documentation and figured out this is how it works.
- Use Post Import In Group API to import RDL file from local disk, which creates RDL (or Paginated Report) repot in Power BI Online.
- Use Get Import In Group to get the import result which contains created report information.
If I only want to upload RDL files, then that's it. However, I had to set data source credential manually, so I did further research. And this is how to set credential.
- Use Reports - Get Datasources In Group API to get the datasource for the report, which returns gateway and datasource information.
- Use Gateways - Update Datasource API to set the credential.
Okay that's it!
PowerShell exmple
There is an official Power BI PowerShell module, and I partially used it. I may need to contribute the module so that it supports entire workflow.
This is far from perfect but I hope you get some idea.
- Please change Encoding from UTF8 to whatever your file is when you Get-Content
function Publish-ImportRDLFile
{
param
(
[string]$RdlFilePath,
[string]$GroupId,
[string]$nameConflict = "Abort"
)
# Get file content and create body
$fileName = [IO.Path]::GetFileName($RdlFilePath)
$boundary = [guid]::NewGuid().ToString()
$fileBody = Get-Content -Path $RdlFilePath -Encoding UTF8
$body = @"
---------FormBoundary$boundary
Content-Disposition: form-data; name="$filename"; filename="$filename"
Content-Type: application/rdl
$fileBody
---------FormBoundary$boundary--
"@
# Get AccessToken and set it as header.
$headers = Get-PowerBIAccessToken
if ($GroupId) {
$url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/imports?datasetDisplayName=$fileName&nameConflict=$nameConflict"
}
else {
$url = "https://api.powerbi.com/v1.0/myorg/imports?datasetDisplayName=$fileName&nameConflict=$nameConflict"
}
# Create import
$report = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -Body $body -ContentType "multipart/form-data"
$report.id
}
function Set-BasicPassword-To-RDL
{
param
(
[string]$Id,
[string]$GroupId,
[string]$UserName,
[string]$Password
)
# Get AccessToken and set it as header.
$headers = Get-PowerBIAccessToken
# Get import result first. As it may take a bit of time until report is created, so repeat until I can get report Id
$reportId = $null
while($reportId -eq $null)
{
if ($GroupId) {
$url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/imports/$Id"
}
else {
$url = "https://api.powerbi.com/v1.0/myorg/imports/$Id"
}
$import = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
$reportId = $import.reports.id
}
# Datasource information
if ($GroupId) {
$url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/reports/$reportId/datasources"
}
else {
$url = "https://api.powerbi.com/v1.0/myorg/reports/$reportId/datasources"
}
$datasources = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
$gatewayId = $datasources.value[0].gatewayId
$datasourceId = $datasources.value[0].datasourceId
# Finallly set credential. No Uri difference between myworkspace and group workspace.
$url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$datasourceId"
# This is a sample for basic authentication. Find more samples at https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#examples
$body = @"
{
"credentialDetails": {
"credentialType":"Basic",
"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"$UserName\"},{\"name\":\"password\", \"value\":\"$Password\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}
}
"@
$datasources = Invoke-RestMethod -Uri $url -Method Patch -Headers $headers -ContentType "application/json" -Body $body
}
# Connect to Power BI.
Connect-PowerBIServiceAccount
$groupId = "<group id>"
# Create Import
$id = Publish-ImportRDLFile -GroupId $groupId -RdlFilePath "<path to rdl file>"
# Set password
Set-BasicPassword-To-RDL -Id $id -GroupId $groupId -UserName "<username>" -Password "<password>"
Summary
Power BI provides powerful Web API for developer. Whenever you need some automation, check it out!
Top comments (8)
Interesting... I just run the script again and it simply works in my environment so it could be some setup difference? If you signup for new trial, does it work? Anyway I recommend you to create support ticket to see if support team give some idea.
Actually I don't need a trial, as I am uploading my RLD report for the embed scenario (aka "embedding Power BI content into an application for your customers"). So, the REST API client is authorized through a service principal ID and secret. My code works fine for PBIX files, while it does not for RDL ones. Perhaps there is some issue when the token refers to a service principal, not a user.
Thanks for your feedback!
Thanks for posting this code. I was able to get my Paginated report distribution script working using your code as a base.
This is a great script but my report was empty after uploading then I found out that content type was wrong then I made this change to script
Changed this line: (13)
$fileBody = Get-Content -Path $RdlFilePath -Encoding UTF8
to this
$fileBytes = [System.IO.File]::ReadAllBytes($RdlFilePath)
$encoding = [System.Text.Encoding]::GetEncoding("iso-8859-1")
$filebody = $encoding.GetString($fileBytes)
then my reports were loading fine
Thanks for the update!
Excellent example. I used your post to develop a solution to upload Paginated Reports with a live connection to Azure Analysis Services. Thank you!
One thing that is not clear from the API documentation is how to change the reports name. How would you go about that?
I also try to figure it out but I couldn't. Maybe we need to re-create it with different name?
Helllo Kenichiro,
can you please confirm that this example does actually work? I have been working on import an RDL report into the Power BI service by means of the REST API for a few days now, but I have not managed to upload the report so far. Although I am coding in Go with direct HTTP request, I have tried also your example as well as a C# one, so that I could find a working solution and then compare the flow with mine in Go.
Typically the error "400 Bad Request RequestedFileIsEncryptedOrCorrupted" is returned.
I also noticed that a plenty of people have faced the same issue, just a few topics found on the Power BI forum confirming that:
Therefore, if you can provide further details on your approach, it would be appreciated.
Best reagrds,
Michał