Multiple value parameter with and the 'ALL' option to functioning.

[rogerzebra]

Okay, I decided to start a new thread due to the fact that my issue is a little bit different than Bessebo's on this subject, sorry about that. I'm trying to create a report with multiple parameter choice and being able to use the 'ALL' option. It works fine picking multiple values as long as I'm using a subquery that produces all the column records for column producer but,not the original query that just shows the top 20 producers. Also, I can't get the 'ALL' option to work. Subquery

select 'ALL' as producer 
union 
select producer from tbl...

I would appreciate if someone could give me a helping hand and set me in the correct direction. I added this in the where clause, but it doesn't seem to have any impact -->

and producer in (select slice from @MVP) or 'ALL' in (@ParamMV)

As always I appreciate all efforts for anyone that takes the time to look at my problem.
Thanks

 

[SgtJarrow]

I am not quite sure of your actual problem (your statement is a bit confusing to me....early morning) but to use 'ALL' in your report, you need your WHERE clause to use something like:

AND (Producer IN (SELECT slice FROM @ParamMV) OR @ParamMV = 'ALL')

This works because if you provide the ALL value, EVERY record will evaluate to TRUE, but if you provide an actual value, the ALL check never is TRUE and only those that match what you pass in are.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[rogerzebra]

Thanks mstrmage1768,
That helped a lot. But,I still have an issue though with my subquery code. If I'm using the simple code beneath that's working but, that sql returns all the producers which are hundreds of them.

select 'All' as producer union select producer from tbl

I only interested the see the top 20 producers with the most premiums. That is what my main query does, it filters out to just show the top 20 producers. My idea is to use the same query a bit stripped to just contain the premium and producer in the sub query without the function that make a comma delimited array but, I can't get that to work, it seems that it creates spaces in between each producer number in the parameter window.
What about if I would use a view that gets populated with the data from the mainquery? Would it work to use the subquery to read from the view in run-time? My main query looks something like this.

declare @MVP1 table 
	(
	 slice varchar(50)
	)
 
 declare @MVP table 
	(
	 slice varchar(50)
	)
  declare @index1 int
  declare @slice1 nvarchar(4000)
    set @index1 = 1
  if @ParamMV is null	
    set @index1 = 0
  while @index1 !=0
    begin
      set @index1 = charindex(',',@ParamMV)
      if @index1 !=0
        set @slice1 = left(@ParamMV,@index1 - 1)
      else
        set @slice1 = @ParamMV
      insert into @MVP select @slice1
      set @ParamMV= right(@ParamMV,len(@ParamMV) - @index1)
      if len(@ParamMV) = 0
        break
      end
insert into @tmp1
select ...
where filter..
insert into @tmp2
select ...
where filter..
from @tmp1 
insert into @tmp3
select ..
where filter..
from @tmp2
select * from @tmp3
where (Producer IN (SELECT slice FROM @MVp) OR @ParamMV = 'All')

I appreciate all ideas.
Thx

 

[jymm]

I did something simlar to this a while back - and you are pretty close

First I did one dataset where got all of my customers (ok - for you - producers).

select CustomerNumber, CustomerName
from Customers

This gave me the list to work from for the parameter. So in the parameters I pointed to this list and displayed the name as the label field and the customernumber as the value

THEN I did another dataset. I selected where I got the top 20 customers in my DB so - something like

select top 20 CustomerNumber 
from invoices
order by sales desc

THIS dataset was used as the default for the parameter. So when the user came into the report - it automagiacally gave them the sales for the top 20 customers - BUT - they could easily expand the list and select a few more.

OVer time I made the second query a bit smarter and dependant on the date so they now get the top 20 customers for the last year - not just in general.

This make any sense - I should be back in the office tomorrow and can get the example if you want.