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 explains a tip when you build the paginated report for Power BI Service and Power BI Report Server. Both the version of paginated reports are .rdl files.
Let me start with simple question.
If you have rdl file published in Power BI service and working there, will the same rdl work in Power BI report server?
Almost, all of us will say YES because we know that there is no difference in RDL file on both the version. This blog post explains, there are certain cases the same RDL will not work in Power BI report server.
I have a paginated report with Oracle as a source and it is working fine in Power BI Paginated Report builder and Power BI Service. I have a data parameter with below name and the same is used in the dataset.
:Start & :End – If you use Oracle in report builder, you need to use “:” for parameters.
The same rdl with Oracle is not working and it ended up with below error in web portal.
Error – An error has occurred during report processing (rsProcessingAborted). Query execution failed for dataset XXXX (rsErrorExecutingCommand).
It shows that there is an error in the dataset. To investigate further, we need to use Microsoft Report Builder (Optimized version of Power BI Report Server / SSRS).
I started to run the query in Dataset – Query Designer and I got the below error.
Error – An error occurred while executing the query. ORA-01745: Invalid host/bind variable name.
The above error conveys something that there is an issue with the parameter / variable which we defined. I have renamed the parameter as :StartDate and :EndDate and it worked.
Published the updated RDL in report server and it worked there also without an issue.
This article explains that on-prem report server is the best option for paginated reports as of now as per my experience and I am also recommending you to use the hybrid approach wherever it is possible. You can maintain a hybrid structure like, Power BI Desktop reports in Power BI Service and Paginated reports in On-prem server. This is something that you can prefer where you will get the more stable version of product with more features.
I am sure, Power BI team will make the Power BI Paginated Report as a stable version with tons of feature in future.
I am highlighting few points in which Report Server is getting more score than the Power BI Service.
License – if your organization has Power BI Premium Capacity node then you can get the report server product key and use them for on-prem installation or you can get it if you have SQL Server Enterprise Edition with Software Assurance.
Report Render Time-Out issue – We often encounter this issue in one of my client. We are using SQL Server and the database and we are handling large tables. If we run the query, it will take at least 5 to 6 mins due to relationship with multiple tables and conditions.
We have done all level of performance optimization but still same performance. If we use the same script in Report Builder it is taking more than 10 mins and same in service taking more than 10 mins and after that it end up with an error. When we check with Microsoft support engineer, they were highlighted that the issue is because of Authentication token expiration but not sure and there is no fix as of now. We tried all the approaches to overcome the issue but finally we end up with Report Server as an alternate solution for these kind of time-out issues. In Report Server we do have control to setup the time-out settings.
Subscription – Our customer had requirement to get the report’s excel export into their email or network drive. Power BI Service has Email option but no network drive as an out of the box feature. We need to use Power Automate to achieve the result which will be difficult for business users. This network file share is one of the default option in on-prem Report Server. Also we do have data driven subscription.
Gateway – Gateway is one of the component in Power BI Service which helps to connect Power BI reports with on-prem data sources. We always need to map the report with gateway data sources. If you have Power Bi Desktop reports then you can map the dataset once with Gateway datasource and you can replace them every time whenever you do the changes on the report and you no need to map the data source again and again for the same report where as you need to map with data source again and again whenever you overwrite the paginated report (rdl).
Security – In terms of security, we have to leverage only the pre-defined access levels which are Admin, Member, Contributor & Viewer in Power BI Service. These pre-defined access levels are more than enough for most of the cases but on few cases we may need to limit certain access. We have same kind of access levels in report server but we do have control to create our own access with defined controls. It will give more control to manage the access levels.
If you have PII data and you dont want to move the data outside of your network then report server is one of the best option.
Data Source Support – This is one of the feature that I highlight Power BI Report server is best. Power BI Paginated report support only limited data sources where as we have few more additional data sources like ODBC, OLEDB and etc supported in Report server paginated report. ODBC and OLEDB can cover many data source connections possible with Report Server report. One of the highlight of Power BI Paginated report, it supports Power BI datasets as a source which is not supported in report server.
Management – Power BI Service has workspace concept to maintain the reports for different teams and groups and etc. If you have multiple sections under one main group then we need to maintain multiple workspaces. In Report Server, all are folders, we can create folders, sub folders and can control the access to root folder or sub folder or report.
Additional Features – Power BI Report Server supports below features which is not fully available in Power BI Paginated reports yet.
1. History Snapshot
2. Cache control
3. Central Data Sources and Datasets
5. Document Map
6. Drill-through reports
7. Linked Reports
Development Tools – Power BI Report Builder is 32 bit and light weight tool and we have same kind of report builder for report server. Both of the tools hanging when we handle report with huge number of rows as a result. If you choose report server then you can use SQL Server Data Tools with Visual Studio and you can create reports and deploy. The development experience will be good when you choose Visual Studio.
Share your thoughts here.