Tag Archives: Dataset

ByHariharan Rajendran

SSRS Shared Dataset with Parameter

As you know, a dataset is the direct input component for SQL Server Reporting Services. These datasets are part of rdl files. Reports can use the datasets as an embedded dataset or shared dataset.

But in a real time, we mostly used embedded dataset for reports. Now, shared dataset is the mandatory option for SQL Server 2016 Reporting Services mobile reports.

There are plenty of articles available to show how to use the Shared Dataset for mobile report publisher. This article explains to you that how to create a shared dataset.

Shared dataset can be created any of the following tools.

  1. Visual Studio Data Tools
  2. Report Builder

Let us see the steps to create a shared dataset in Visual Studio Data Tools.

  1. Create a Reporting Services Project in Visual Studio
  2. Create a Shared Data source Connection under Solution Explorer
  3. Right Click on Shared dataset Folder and choose “Add New Dataset”
  4. Specify the name of the dataset and choose your data source.
  5. Under Query Type, you have 3 options. Text, Table & Stored Procedure. In our case, choose Text
  6. Pass your T-SQL Query. It can be simple or complex queries
  7. Make sure the query is correct and click Ok.
  8. Right click on your dataset and click deploy.
  9. Check the deployed dataset in datasets folder in report manager URL.

Let us discuss another scenario.

Scenario: Need to pass the parameter in shared dataset.

Follow the below steps,

  1. Add the parameter in your T-SQL Query as like below,

Where <columnname> = @<parametername>

In the above syntax, you can pass any condition as like regular T-SQL script. Instead of value pass parameter name with @ symbol.

  1. Once you added the above line, go to parameters section.
  2. You can see your parameter there as like below or you can add one new parameter using Add option.
  3. Select the check boxes based on your requirement but you need to pass one default value
  4. Deploy the dataset.

Share your comments below.

 

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.

 

1