Tag Archives: ODBC

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

ODBC Escape Sequences – Date, Time & Timestamp

To convert the data type of the column in SQL Server, we use to follow below options.

  1. CAST
  2. CONVERT

This post explains “ODBC Escape Sequence” to convert the data type of the column or expression.

It also explains that how we can use the ODBC escape Sequence to get same result as CAST and CONVERT.

The syntax of the ODBC escape Sequences

Date – {d ‘Value’}  –Value format is yyy-mm-dd

Time – {t ‘Value’} – Value Format is  hh:mm:ss

Timestamp – {ts ‘Value’} – Value Format is yyyy-mm-dd hh:mm:ss[.f…]

–Create Table

CREATE TABLE Employee (

ID int not null identity (1,1),

Name NVARCHAR(25),

DateofJoin DATETIME

)

GO

INSERT INTO Employee values(‘John’,’2016-10-26′);

INSERT INTO Employee values(‘Richard’,’2016-1-13′);

INSERT INTO Employee values(‘David’,’2016-05-02′);

INSERT INTO Employee values(‘Jacob’,’2016-07-20′);

GO

–CAST

SELECT * FROM Employee

WHERE DateofJoin >= CAST(‘2016-06-02’ AS DATE)

GO

–CONVERT

SELECT * FROM Employee

WHERE DateofJoin >= CONVERT(DATE,’2016-06-02′)

GO

–ODBC date escape sequence

SELECT * FROM Employee

WHERE DateofJoin >= {d’2016-06-02′}

GO

–ODBC time & timestamp escape sequence

SELECT {t’09:00:00’} as ODBCTime

 

1