SSRS

ByHariharan Rajendran

Hide SSRS Report Viewer Toolbar

Usually, when we run a report with report viewer we will see the report output with report viewer toolbar options.

To hide the report viewer toolbar, add the following parameter at the end of your report URL.

&rc:Toolbar=false

Latest URL will be like below,

http://XXXXX/Pages/ReportViewer.aspx?%2fReport+Project2%2fReport3&rs:Command=Render&rc:Toolbar=false

Note: Open the report using report viewer. This is not applicable when you directly open a report from report manager.

ByHariharan Rajendran

Name mismatch (DataSet1) in SQL Server Mobile Report Publisher

This post explains a very simple error message that we may face if any issue with a shared dataset.

For example, take a below scenario

You have a shared dataset deployed to the report manager portal. The name of the dataset is Sales and you need to build a mobile report using Sales shared dataset.

When you use the Sales Shared Dataset from mobile report publisher, you may get an error message if any issue with the query used in Shared dataset or data source issue. The error message usually will say DataSet1 even though we used the proper name for the shared dataset.

It is because the shared dataset by default considering the name ‘DataSet1’ when you create a dataset. It can be identified and modified if you open the dataset file in notepad.

Have you seen this scenario? Share your comments below.

ByHariharan Rajendran

Add Data to Mobile Report Publisher

Recently, I saw a question raised in MSDN forum.

The question was “How to add data into Mobile Report Publisher?”

The answer is very simple, Mobile Report Publisher will use dataset for data.

If there are no datasets deployed to report server then we can’t see anything when using mobile report publisher data tab.

Steps to create dataset and make available for Mobile Report Publisher.

Step 1: Create a shared Dataset

Step 2: Deploy the shared dataset to report server

Step 3: Make sure the deployed shared dataset is deployed successfully and check it in report server as well.

Step 4:  Click the Add data in Mobile Report Publisher.

Step 5: Choose the available dataset from report server.

Data will be added and start build the mobile report

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

Dataset Filter in SSRS

In My previous post, I said: “Avoid using Dataset in SSRS”. If you read that article then probably you may have the following question.

What is the purpose of filters in dataset properties?

Yes, I said to avoid but we have some scenarios where we need to use the dataset filter properties.

Consider a scenario, you are a report developer and you need to develop a report for the given requirement. As part of development, you need to use one of the stored procedures and want to display certain rows as a result in the report.

You don’t have enough access to modify the stored procedure to add the filter condition and no other write access.

In this scenario, you can’t filter the data in script level so you need to use report dataset filter to filter the data.

Pass the stored procedure directly into the dataset and filter the respective column with a value.

Performance is important but not all the cases. Still, we can get good performance, use dataset filter when we extract less number of records from database layer.

Dataset_SP1

Dataset_SP2

ByHariharan Rajendran

Avoid Using Dataset Filters in SSRS

If you are Microsoft SQL Server Report Developer, then you have a chance to notice the dataset properties as it is the place where we supply the data subsets to the report.

Let us understand the filters in SSRS first and analyse why dataset filters should be avoided.

We have three different main places to filter the data in SSRS

  1. T-SQL Script
  2. Dataset Filters
  3. Run-time Filter using Parameters

Filter in T-SQL Script

When we understand the business requirements, need to categories what are the filters static and dynamic.

Always use the static filters in T-SQL script. Dynamic filters can be done through parameters but still need to add the filter in T-SQL Script with parameters.

For example,

If you want to filter country values as the US on your country table.

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=’US’

Note: In this case, we are restricting data in database server itself.

Dataset Filters

You can filter in SSRS report dataset.

Go to Dataset -> Filters

RD

Note: In this case, we are taking all the data to report server and filtering the data in the report.

Run-time filter using Parameter

It is a special kind of filter because this will be the requirement from business in most of the cases.

First 2 filters won’t be visible to end users but this filter will be visible and user needs to filter the value while running the report.

This can be achieved by adding the parameter in T-SQL script,

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=@Country

RD2

Why should we avoid dataset filters?

The main reason is to improve the performance of the report.

In case 1 (Filter in  T-SQL Script), we are filtering the data in database layer itself. It means we are taking only required data to the reporting layer and showing the result. The performance of the report will be good as it is processing only fewer records.

In case 2 (Dataset filter), we are taking all the records from table database and filtering in the report server. It means report server has to make process and filter the data and show the result. We are giving burden to report server so it will affect the performance.

 

ByHariharan Rajendran

Cascading Parameter in SSRS

Cascading parameter means, one parameter values are based on another parameter value.

This article explains this concept in a very simple way with the below scenario.

Pre-Requisites:

  1. Main Dataset with 2 parameters
  2. Dataset for parameter 1 distinct values
  3. Dataset for parameter 2 distinct value with parameter 1 in where clause

Main Dataset:

Select EnglishProductCategoryName as ProductCategoryName, EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category and EnglishProductSubcategoryName=@SubCategory

Dataset 2 for Parameter 1 Values:

Select distinct EnglishProductCategoryName as ProductCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Dataset 3 for Parameter 2 values with Parameter 1 in where clause:

Select distinct EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category

Assign the available values for both parameters

Run the report.

Initially, it will show the first parameter with drop down values and the second parameter will not show any values.

As per the above scenario, we are passing the first parameter value to the second parameter.

We need to choose a value of the first parameter so that we can see the second parameter values.

Based on the first parameter value, second parameter values are changing.

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