SSRS Multiple Parameters

If I want to use multiple parameters on a report do I have to build out a huge WHERE statement in my dataset query or is there a better way to handle this? Filters?

The Parameters are:

StartDate
EndDate
Facilty (populated with a UNION so I can do the Select All trick)
Staff (populated with a UNION so I can do the Select All trick)


Thanks.
LVL 1
LCNWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
I usually create stored procedures for ssrs reports.  Each parameter I initially set to null and then go from there:

your where clause in this case would be

where
(@startdate  is null  or startdate >= @startdate) AND
(@EndDate is null or endDate < @endDate) AND
(@facility is null or facility in (select * from dbo.fn_txt_split(@facility, ';'))  AND -- ';' is a delimiter
(@Staff is null or staff in (select * from dbo.fn_txt_split(@staff, ';'))


This is the code for fn_txt_split:
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 08/24/2010 09:10:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 08/24/2010 09:10:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

GO 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
do I have to build out a huge WHERE statement in my dataset query or is there a better way to handle this? Filters?

The huge WHERE clause and the filters will both work, but you should know they are not exactly the same in execution.  The WHERE clause will be executed on the database server while the filters are executed by the SSRS engine.  In a scale-out deployment that will be a different server.

I usually use query-side filtering (i.e. the WHERE clause) in most cases because if the report doesn't need the data, it shouldn't travel over the network either.  Not only do you save network bandwidth, you also decrease the load for the report processing by the report server.

Use report-side filtering if there's no other option (such as when the source is a stored proc and you're not in the position to have it modified).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.