SSRS Report with Filter which contains ALL as a value

Question

Post reply

SSRS Report with Filter which contains ALL as a value

devender.07042

Old Hand

Points: 354
More actions
August 21, 2011 at 11:27 pm

#243548

Hi all,
Iam new to SSRS and my requirement is as follows..
I'm creating a report with filter "technician" which contains "All" as filter option which when selected should work as if default "select All" is selected . there is a column named "technician" which should display data based on value selected in "technician" filter...
any help would be appreciated..

Viewing 4 posts - 1 through 3 (of 3 total)

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

gfilby SSC-Addicted Points: 435 More actions · Answer 1

Instead of using a filter create a parameter then structure you query using 'like' eg.

select * from xxx where technician like @parameter

Then you add the values for the parameter eg:

Value: John Name: John

Value: Paul Name: Paul

Value: % Name: All

or you could add another dataset using a query like:

select technician as 'technician' from xxx

group by technician

union all

select '%' as 'technician'

Then using that dataset to populate the values for the parameter. However you will then have a % sign in the list, I have not yet found away around that.

--EDIT--

Ok the best way to do it is change your query to:

dataset1 = select * from xxx where technician in (@parameter)

dataset2 = select technician as 'technician' from xxx group by technician

Then on the prameter set it use dataset2 and set it allow multiple values.

You should now have a 'select all' in the drop down list.

Hope that is what you where looking for.

bteague SSC Eights! Points: 895 More actions · Answer 2

I will preface my response by saying that this is for SQL Server 2008, noting that this is a SQL Server 2005 posting.

I have had luck editing the query as text and placing IF control flow logic in the query that evaluates the parameter so that

IF @parameter = 'ALL'

SELECT ....

IF @parameter <> 'ALL'

SELECT .... WHERE field = @parameter

To populate the parameter list with a manually or with query where I add ALL to the list (simple UNION in the case of a query).

nellisjp SSChasing Mays Points: 624 More actions · Answer 3

From Chris Hays's Reporting Services Sleazy Hacks Weblog:

http://blogs.msdn.com/b/chrishays/archive/2004/07/27/allparametervalue.aspx