How to add 'ALL' as report parameter in drop down with other parameters

Hi ALL,
I would like to add 'ALL' to other report parameters.The other report parameters are counties.I would like to add 'all' so that user can select all counties from drop down.

I added union select 'ALL' to sub query.
in main query @.county='all' is it right or wrong.After I did this report is running very very slow.performance problem begins.
what exactly is the procedure to do.
thanks
r sankar

Hi -

You should be able to add an "All Counties" option to your drop down list without a much of a performance hit.

For the dataset that is the basis for the parameter list, have something like this:
SELECT
County,
CountyId
FROM
Counties

UNION

SELECT
'(ALL)', -1
Now in the dataset that the report is based on, include something like this:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR CountyId = -1)
Of course you'll see performance benefits from wrapping these two statements in stored procedures.

HTH....

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||Joe,
Thank you very much.Explained in very good way.I will try this way.If any problems I will let you know.Thanks once again.
r sankar|||

hi Joe Webbon ,

I've faced this problem , and I'm searching for a better solution for it.

I think this solution is not right . I try it . but it didn't work.
Why?
so it could be :
WHERE (Country.CTRYIsn =98) ====== country no 98
or
WHERE (Country.CTRYIsn = -1) ====== ((((NO DATA))))
because if the report user choose 'ALL' in the drop down menu , the value willl be -1, so, anyone knows that the query results only bring the row which match the value. and because there is not -1 in the country field in the fact table, the results will be 0 ROWS

SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn = - 1)

NO DATA, u see?

-
my idea is :
SELECT
County,
cast (CountyId as varchar(9))
FROM
Counties
UNION
SELECT
'(ALL)', '%'
SELECT BillOfLading.*
FROM Country INNER JOIN
BillOfLading ON Country.CTRYIsn = BillOfLading.CTRYIsn
WHERE (Country.CTRYIsn LIKE @.CTRYIsn)

so it could be :
WHERE (Country.CTRYIsn LIKE 98) ====== country no 98
or
WHERE (Country.CTRYIsn LIKE %) ====== ALL
anyone can find another solution that can high the performance than my sol. , please say it !!!!!!

|||

Rather than comparing the data field to -1 it should have compared the parameter. ie

WHERE (Country.CTRYIsn LIKE @.CTRYIsn OR @.CTRYIsn = -1)

I think this would be be a better solution rather than passing a %.

Cheers

|||Hi,
I use dynamic SQL in the stored procedure to manage the performance issues. I have got multiple drop down with the "All" option. example:

if @.CTRYIsn != '%' begin

set @.vWhere = @.vWhere + ' and s.CTRYIsn = ' + char(39) + @.CTRYIsn + char(39)

end

If you use the new "Multi-Value" property, change this to handle cases where user selected multiple items. I yet have to work on that. I guess that the parameter list will have to be compared to the distinct count of counties to see if the count is 1 or = to the count of counties or in between. if = to 1, then use = if in between then use in(@.CTRYsn) else do not set any limit on the item. What I do not know yet is how to count the items in the parameter list. May be you could count the "," from the parameter list?

Regards,
Philippe

|||

thank u again,

I still can't understand , I think agian your statment are WRONG !!!!
did u try it , or u just propose a solution

dont say an example , My solution before is NOW on use.

Karim

|||Hi Karim -
You're absolutely right. The code I posted will not work as is. There was a typo in it. The code should read:
SELECT
col1,
col2,
col3,
...,
coln
FROM
tab1
WHERE
(CountyId = @.CountyId OR @.CountyId = -1)
The typo was in the second part of the OR clause. It should have the parameter (@.CountyId) as shown above rather than the fieldname (CountyId) as shown in the original post.
When a user selects a specific CountyId in the dropdown, the first part of the OR condition will be satisified. When the user selects the ALL item in the dropdown, the second part of the OR condition will be satisfied for every row in the table.
The solution you posted will certainly return the appropriate resultset, but it will also incurr the performance hit associated with using LIKE. It's not nearly as efficient as comparing two integers.
HTH....and thanks for pointing out the typo!
--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)||| (CountyId = @.CountyId OR @.CountyId = -1)
Actually, you may get better performance if you reverse the order, like this:
(@.CountyId = -1 OR CountyId = @.CountyId)
to take advantage of short-circuit evaluation of the OR operator. Because the expression "@.CountyId = -1" can be evaluated both ahead of time and just once, it may be faster.
For example, in SQL Server 2000, this:
declare @.value varchar(10)
set @.value='All'

select distinct patid from tx_history_all
where
@.value = 'All' or
cost_center_code = @.value
-- or @.value = 'All' is three times faster than this:

declare @.value varchar(10)
set @.value='All'
select distinct patid from tx_history_all
where
-- @.value = 'All' or
cost_center_code = @.value
or @.value = 'All'

|||Hey There --

I think you've gotten your "2000" answer already, so the following is just an FYI...You get multi-parameter select ("All", among other combinations) in 2005, making all the workarounds unnecessary.|||

Very interesting thread...Sounds like 2005 has this issue resolved!

But just for fun I would like to continue the "2000" workarounds...

You see, I was trying to get "(ALL)" or single item capabilities added to existing item range parameters. The user could indicate one item, all items, or a range of items. Here is what I came up with (it's kind of hokey looking but it works):

="select PONUMBER,ITEMNMBR,ITEMDESC,LOCNCODE,VENDORID,UOFM,QTYORDER,REQDATE from POP10110 (NOLOCK) WHERE QTYORDER > 0 " & IIf(Parameters!ItemStart.Value = "(ALL)","AND ITEMNMBR NOT LIKE "," AND ITEMNMBR BETWEEN '" & trim(Parameters!ItemStart.Value) & "' AND ") &
IIf(Parameters!ItemEnd.Value = "(ALL)","'" & trim(Parameters!ItemStart.Value) & "'","'" & trim(Parameters!ItemEnd.Value) & "'")