Tag Archives: Power BI Report Server

ByHariharan Rajendran

How to overcome the Limitations of Paginated Report ODBC/OLEDB connectors in Power BI Report Server

This article covers how the ODBC and OLEDB connectors can be effectively used and overcome the limitations. The ODBC and OLEDB connectors are only supported in on-prem version of report builder as of now.

What is the use of ODBC and OLEDB in Report Builder?

As you know, we have more connectors support in Power BI Desktop and the list is keep extending on a monthly basis but the data source support for report builder is limited. If you take Power BI Report builder then very less connectors are supported as of now. It doesn’t support ODBC or OLEDB data source. The same is available in Microsoft Report Builder (on-prem version) and gives the flexibility to connect many data sources through ODBC and OLEDB. For example, if you want to create paginated report using SAP HANA as a source then you just have only one option which is ODBC (if you are looking for on-prem solution).

Info – SAP HANA with Power BI cloud, you can use Power BI Desktop report or use Power BI Datasets as a source for Power BI Builder report.

The list of supported data sources from Microsoft Report Builder.

 

How to use ODBC or OLEDB in Report builder?

First, you need to have answer for “what is the data source you are trying to connect from report builder through ODBC”?

Let us take an example, you want to connect SAP HANA then you need to install 32 bit of SAP HANA ODBC client and then you need to create a DSN in your system to connect and the same needs to be created in Report server machine. The same should be followed if you want to connect with other ODBC supported data sources.

Attached some sample screenshots,

Enter the connection details and create the DSN.

We need to choose ODBC from report builder data sources and can choose the created DSN.

To connect through OLEDB, you need OLE DB provider for the data sources. If you have installed the OLEDB driver then you can choose and create the connection.

What are the primary limitations?

  1. To define the parameter, we need to use “?”. In case, if you have multiple parameters in a dataset then it will difficult to find the order of the parameters.
  2. It will not support multi-value parameters in the report.

Error – 

Cannot add multi value query parameter ‘?’ for dataset ‘DataSet1’ because it is not supported by the data extension.

—————————-

An error has occurred during report processing.

How to overcome multi-value parameter value limitation?

We can overcome the limitation using below steps.

  1. Use Join Condition in the parameter expression to convert the list into comma separated values.
  2. Use Database function to convert the comma separated values into row by row in the dataset query.

Step by Step Procedure

Scenario – I have a report where I connected Oracle through OLEDB data source. I need to pass the account type values into the parameter which is multi-value enabled and need to show the result in the report.

Step 1 – Create a parameter as like below and enable the “Allow multiple values” checkbox.

Step 2 – Build your query as like below. It should have IN operator as we need to pass multiple value. The initial query will be like below.

Step 3 – We have “regexp_substr” function in oracle which will convert comma separated values into row by row. Added the statement below. To test this statement, you can replace the “?” into hard-coded value and see the result.

(SELECT trim(regexp_substr(?, ‘[^,]+’, 1, LEVEL)) str   FROM DUAL

CONNECT BY instr(?, ‘,’, 1, LEVEL – 1) > 0)

Step 4 – Need to add the above statement in the step 2 dataset query.

Step 5 – There will two parameters created, need to map with already created parameter.

Step 6 – Need to convert the @AccType into expression to join the multi-value into comma separated. Use the below expression for both the parameters.

Step 7 – Add a simple table to show the result and run the report. Report will run and show te exprect result.

Alternate Approach

We do have other approach using combined query (query expression) in dataset to achieve the same result.

Create a query field in fields section for all the select columns.

The above expression will work and get the expected result.

Happy Learning!! Share your comments below.

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