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.