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?
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.
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.
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.
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.
The syntax of the REST API is http://<reportservername>/reports/api/v2.0/
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.
We need to perform 3 main steps.
Initially the below data source is used on the report.
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
Here PowerBIReports is a REST API which will list out all the reports from report server.
Note down the report ID of the report.
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
We need to use the captured ID on the above URL.
Note down the Value section of the result.
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.
“ConnectionString”: “data source=.;initial catalog=IT_Dev;persist security info=False”,
That’s it. If you go and check the report data source then you can see IT_Prod.
Please leave your comments below. Happy Learning!!