Tag Archives: Power BI Report Server

ByHariharan Rajendran

Power BI Report Server – Change Data Source using REST APIs

This post gives an idea that you can change the Power BI report’s data source without using Power BI desktop. Do you think is it possible? I am talking about Power BI Report Server.

Yes. This is possible with the help of Power BI Report Server REST APIs. You can go through the supported API methods using the below URL.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0

The syntax of the REST API is http://<reportservername>/reports/api/v2.0/

Scenario:

I have a report published into Power BI Report Server portal and I want to change the database used on the report to some other database. In this example, I am using a database called IT_Dev on my report and I will be changing the database to IT_Prod. Even you can change the server name also.

I am using Postman tool to access the REST APIs. Please check my below article to know about using the Postman with Power BI Report Server REST APIs.

Power BI Report Server REST API with Postman

We need to perform 3 main steps.

  1. Get the report ID of which we want to change the data source
  2. Get the Current data source information of the report
  3. Update the data source

Initially the below data source is used on the report.

Get Report ID

Use the below REST API to get the ID of the report. It will show all other information of the report. Since we are using GET method, you can access this URL on the browser itself.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports

Here PowerBIReports is a REST API which will list out all the reports from report server.

Note down the report ID of the report.

Get Current Data Source Information

As we are trying to update the data source, we need to know the structure of the current data source so that we can use the same structure while updating the data source.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We need to use the captured ID on the above URL.

Note down the Value section of the result.

Update the Data Source

In this step, we will update the database from IT_Dev into IT_Prod.

Method – PATCH

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We are using the same URL but with different methods. And, we need to paste the data source value script which we copied on the previous step on the body section. Change the database name and enter the username and password.

Structure:

[

{

“Id”: “2dc59c3d-e941-ea11-bed7-08edb9949ce0”,

“Name”: null,

“Description”: null,

“Path”: null,

“Type”: “DataSource”,

“Hidden”: false,

“Size”: 0,

“ModifiedBy”: “<domain\username>”,

“ModifiedDate”: “2020-01-31T19:39:14.067+05:30”,

“CreatedBy”: “<domain\username>”,

“CreatedDate”: “2020-01-28T21:44:21.507+05:30”,

“ParentFolderId”: null,

“IsFavorite”: false,

“Roles”: [],

“ContentType”: null,

“Content”: “”,

“IsEnabled”: true,

“ConnectionString”: “data source=.;initial catalog=IT_Dev;persist security info=False”,

“DataSourceType”: null,

“IsOriginalConnectionStringExpressionBased”: false,

“IsConnectionStringOverridden”: false,

“CredentialRetrieval”: “prompt”,

“CredentialsByUser”: null,

“CredentialsInServer”: null,

“IsReference”: false,

“DataSourceSubType”: “DataModel”,

“DataModelDataSource”: {

“Type”: “DirectQuery”,

“Kind”: “SQL”,

“AuthType”: “Integrated”,

“SupportedAuthTypes”: [

“Integrated”,

“Windows”,

“UsernamePassword”

],

“Username”: “<yourusername>”,

“Secret”: “<YourPassword>”,

“ModelConnectionName”: “d0f1b0ce-c7e4-4ff9-b72f-3df06f48f6fe”

}

}

]

That’s it. If you go and check the report data source then you can see IT_Prod.

Video explanation

Please leave your comments below. Happy Learning!!

1