Filtering Dataset or Tabix using Report Parameter in SSRS

Filtering Dataset or Tabix using Report Parameter in SSRS

A very comman requirement in your Report where you want to filter your Dataset or Records using Some parameter so there are two ways to achieve this in SSRS.

In below example we  want to filter our Report Based on  ResourceId to check which resourceId is working on which project

for this i will create a table to Explain you in a more better way

--Creating a test table for the dataset

create table Projects
(
ProjectName nvarchar(50),
ResourceID nvarchar(50),
Hours int,
Cost float
)

insert into Projects values('ProjA',1,8,3000),('ProjA',2,7,7000),('ProjA',1,3,3000),('ProjB',4,8,3700),('ProjB',1,5,3800),('Projc',1,5,3800)

Creating Dataset for ResourceID

Now For Resource Name

create a Dataset-DSTResourceID

Query-

select distinct resourcename from projects 

click ok

Creating Parameters for ResourceID

-Go to Parameters

-Right Click

-Add parameter

-ResourceName

-Allow Multiple Values Checked

-Available Values

-Check Radio Button -Get Values from a Query

-Dataset-DSTResourceName

-Value field-ResourceName

-Display field-ResourceName

click ok

Now in your Main Dataset

You want to filter your Report Based on ResourceName

-Create Dataset for Main Report

Select projectname,resourceID,cost, hours from projectname

where resourceID in (@ResourceID)

This @ResourceID is your parameter that will filter your Report Based on ResourceID

Click ok

Now preview the report 

Select ResourceId from ur ResourceID parameter it will fiter the report

The Second Method 

where you dont want to add a where clause in your query

Go to  your Main Dataset reomove where Clause

Select projectname,resourceID,cost, hours from projectname

Go to filter properties of Your MainDataset

ADD FIlter

Expression - ResourceID

Operator-In

Values-Click-Fx and Select your Parameter ResourceID

click ok

Now preview the report 

Select ResourceId from ur ResourceID parameter it will fiter the report,

NOTE: In our Parameter we are using filter based on multiselection i.e more than 1 resourceID that's why we are using IN operator in case you want to filter with only one resourceId Remove Multiselection from Parameter Property and Change Operator to equal in Second Method.