dropdown list in ssrs

Question

dropdown list in ssrs

cpinky01

Ten Centuries

Points: 1144
More actions
September 9, 2010 at 6:37 am

#229203

I have drop down list in my report where i need all option . it is some thing like this
cateogory1
cateogory2
category3
i need one more option saying all in drop down so that when user selets all it has to display for cateogory1, cateogory2, cateogory3.
column name for this is cateogory
right now my stored proc is some thing like this
create proc proc1
@cateogory
as
select * from cateogortTbl
where cateogoryID=@Cateogory.
can any one explain in detail as i am new into ssrs.
how to write a query for this all in dropdown list to get this functinality.
thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

The best way to do this would be to have two queries, one that just selects everything, without a WHERE clause, and one that selects based on the WHERE clause. Is this TSQL in a stored procedure? If so, you can check to see if the paremeter is passed using an IF statement and then break apart the query accordingly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WayneS SSC Guru Points: 95460 More actions · Answer 2

I can't tell if you just want the "ALL" option in the drop down in the report parameters area, or how to handle in it the following code. So, I'll show you how I do it.

First, assuming the drop down is populated by a query:

SELECT SortColumn = 2, ID, NAME FROM <TABLE>

UNION ALL

SELECT 1, -1, 'ALL'

ORDER BY SortColumn, Name

Will produce a query where the first entry is "ALL", followed by all the NAMEs sorted alphabetically. Set the parameter to use the Name as the label, and the ID as the value.

In the procedure for the report, I typically do this:

CREATE PROCEDURE dbo.MyReport(@Param1 int) AS

CREATE TABLE #Values (ID int)

if @Param1 = -1 -- ALL

insert into #Values

select ID from CategoryTbl

else -- just the selected one

insert into #Values

value (@Param1)

--then join to this table

select <all the columns to report on>

FROM <tables>

JOIN #Values v ON v.ID = <Join Table>.ID

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

cpinky01 (9/9/2010)
I have drop down list in my report where i need all option .
cateogory1
cateogory2
category3
create proc proc1
@cateogory
as
select * from cateogortTbl
where cateogoryID=@Cateogory.

create proc proc1

@cateogory

as

select * from cateogortTbl

where cateogoryID IN (@Cateogory)

where @category will look like {A,B,C,D}

Raunak J

Dennissinned Ten Centuries Points: 1318 More actions · Answer 4

Your main Report Dataset needs to look something like this

select * from cateogortTbl

where cateogoryID=@Cateogory or @Cateogory = 'All Cateogories'

Now you create a new Report Dataset that just Lists the Dropdown Options

Select Distinct CategoryName from cateorgtTbl

Union

Select 'All Cateogories'

Now select the newly created Report Dataset inside the Parameters settings under the Report Layout.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Dennissinned (9/13/2010)
Your main Report Dataset needs to look something like this
select * from cateogortTbl
where cateogoryID=@Cateogory or @Cateogory = 'All Cateogories'
Now you create a new Report Dataset that just Lists the Dropdown Options
Select Distinct CategoryName from cateorgtTbl
Union
Select 'All Cateogories'
Now select the newly created Report Dataset inside the Parameters settings under the Report Layout.

I belive the IN clause will solve the purpose

Raunak J

Dennissinned Ten Centuries Points: 1318 More actions · Answer 6

You right SSRS will put a "Select All" option for Multi-Value Parameters. However, if you want the user to only select either one option or everything it won't work. Also, you have no flexibility as to show different text other than "Select All" or make it default.