Tag Archives: SSRS

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

Paginated Report works in Power BI Service but Fails in Power BI Report Server

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.

Conclusion – The Power BI Report Builder and Microsoft Report builder is different in-terms of deployment and data source settings also the built-in system codes with data sources. Some of the words are considered as keyword in Microsoft Report Builder but not in the Power BI Report Builder.

ByHariharan Rajendran

Access Report Manager outside of Network

Nowadays, most of the organisations are using Azure Virtual Machine to host SQL Server Reporting services. By default, we can access the reporting service manager with-in same network. It won’t be possible to access the reporting services outside of network without setting up below configuration.

This is not only for SSRS in Azure VM. The same setup is applicable for the on-premises server.

Follow the below steps,

Step 1: Go to virtual machine resource group and select Network Security Group.

Step 2: Create a new inbound security role with service as HTTP, it will take 80 as a port.

Step 3: Create a same inbound rule inside the virtual machine. Use the below power shell script to create a rule.

New-NetFirewallRule -DisplayName “Report Server (TCP on port 443)” -Direction Inbound –Protocol TCP -LocalPort 80

Step 4: Enable DNS if not configured already. Click Public IP in Virtual Machine overview section in the portal. Set up the DNS name. It will look like below.

Step 5: Make sure report server is configured and working without an issue.

Step 6: Pass the DNS name with extension in your local machine as like below.

Step 7: Pass the credentials and it will show your reports.

Share your comments below.

ByHariharan Rajendran

SSRS Error Message – Cannot compare data of types System.Decimal and System.Double

I got a below error message when I execute the report. The error message is very straight forward and it highlights the exact issue.

Basically, this error occurred due to different data types. There are many situations where we get this error so I thought to document this scenario.

Error Message,

An error occurred during local report processing.

An error has occurred during report processing.

The processing of FilterExpression for the dataset ‘Dataset1’ cannot be performed. Cannot compare data of types System.Decimal and System.Double. Please check the data type returned by the FilterExpression.

SSRS Error

Error Reason:

In Dataset1, I used a filter property and filtered “Unit Price” greater than 12. When I choose Unit Price column, it automatically picked the data type as float as it has values like xxx.xx in table.

SSRS

The Original data type of the Unit Price column in the database is different, it is money data type. There is a mismatch of the data type.

Solution:

There are two ways to handle this issue.

1.      We can filter the value (Unit Price > 12) in script level and directly pass the script in dataset Instead of using dataset filter properties. We also will get good performance. Check my post on …..

2.      If we want to use the dataset filter then we need to convert the unit price column into SSRS supported data types, in this case, float.

 convert(float,[UnitPrice]) as UnitPrice

 Available data types in dataset filter.

SSRS 2

ByHariharan Rajendran

Cascading Parameter in SSRS – Different Approachs

I saw a query raised by a user in one of the SQL forum. He has explained his query with a scenario.

Scenario: Need to create a simple report with 3 parameters, those parameters are “FromDate”, “ToDate” and “Type”. Two parameters (“FromDate” & “ToDate”) should react when I change the Type parameter value.

The possible values in “Type” field is “Yes” and “No”. If the end user selects “Yes” then “FromDate” and “ToDate” should show one year back from today. i.e. 2016-01-10  to 2017-01-11

If the value is “No” the date should be one month back from today. i.e. 2016-12-10 to 2017-01-11.

By seeing the above requirement, I felt cascading parameter option would be the solution for above requirement but it will not give 100% result because once the value is entered or set on parameter textbox, it won’t be changed until we refresh a page or open the report again. This could be unavoidable.

I thought to document this scenario with possible solutions.

Solution 1:

As usual, create a main query dataset with all the above mentioned three parameters. It could be like below,

Dataset 1

SELECT <column names> FORM <tablename>

WHERE FromDate=@FromDate and ToDate=@ToDate and Type=@Type

The above script in the main dataset will create 3 parameters in parameters folder.

Dataset 2

Create another dataset with following Script,

SELECT

      CASE WHEN Type=’Yes’ then  DATEADD (d,-365,GETDATE()) ELSE DATEADD (d,-30 ,GETDATE()) END as FromDate,

       CASE WHEN Type=’Yes’ then GETDATE() ELSE GETDATE() END as ToDate

  FROM tablename

  Where Type=@Type

Dataset 3

Create another dataset for Type value “Yes” and “No”.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value from dataset 2 and do the same for “ToDate”

Report_Dataset

Run the report, you should see the result as like below.

ReportParameter2

Solution 2:

Follow the same steps as you did with solution 1.

Create Dataset1 & Dataset 3.No need to create dataset 2 as we are going to use an expression in parameter itself.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value with “Specify values” option. Click “Add” and write the below expression.

=IIF(Parameters!Type.Value=”Yes”,DateAdd(“d”,-365,Today), DateAdd(“d”,-30,Today))

For ToDate Parameter,

=IIF(Parameters!Type.Value=”Yes”,Today,Today)

ReportParameter

Report Parameter1

 

ByHariharan Rajendran

PowerBI Reports in SQL Server Reporting Services

Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.

This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.

This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.

As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.

Look and Feel

As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.

In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.

PowerBi

 

Approaches to publish PowerBI Reports to the portal,

Approach 1:

Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.

Bi1

 

BI2

Approach 2:

Save the report in local folder and upload manually to the portal.

Bi3

1