Tag Archives: rest api

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!!

ByHariharan Rajendran

Power BI Report Server REST API with Postman

This post explains how to use Postman tool to access the Power BI Report Server REST API. As you know, Microsoft Power BI team released the REST API feature for both Power BI Service and Report Server. Using this REST API, we can access and control the Power BI objects.

Use the below URL to access Power BI Report Server REST API.

http://<reportservername/reports/api/v2.0/

The list of objects which supported in REST API are,

  • AlertSubscriptions
  • CacheRefreshPlans·
  • CacheRefreshPlanHistory
  • CatalogItems·
  • Comments·
  • DataSets·
  • DataSetData·
  • DataSources·
  • ExcelWorkbooks·
  • Extensions·
  • FavoriteItems·
  • Folders·
  • Kpis·
  • LinkedReports·
  • MobileReports·
  • Notifications·
  • PowerBIReports·
  • Reports·
  • ParameterDefinitions·
  • Resources·
  • Schedules·
  • Subscriptions·
  • SystemResources·
  • SystemResourceItems·
  • UserSettings·
  • System·
  • Me·
  • Telemetry·
  • PowerBIIntegration·
  • ServiceState

Microsoft providing the swagger site to show the detailed REST APIs.

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

Example,

If I want to see the list of Power BI reports then we need to use the below URL on the browser. Actually, it is a get method so we can use the browser.

http://hari-pc/reports/api/v2.0/PowerBIReports

It also will show the properties of each report.

If we want to use the other method like POST, PATCH, PUT and DELETE then we need to use any other application because it can’t be done in browsers. Use the Postman tool to access all the REST API methods.

  1. Choose Methods
  2. Place the URL
  3. Authorization
  4. Body
  5. Status

Authorization Section

 

Body Section

Happy Learning!.

 

 

1