Using a multi-value parameter and want to put All in the header

[bessebo]

I like to put in the header of each report the value of the parameter(s) that are chosen or ALL. Using a multi-value parameter I am not sure how to put an All products in the header if the Select All value is chosen for a parameter called Product. If you choose the Select All value I obviously don't want to put all of the parameters in the header (COULD BE A LOT) so putting in an "All Products" would be great. Does anyone out there have a good way to do this? What is the easiest way to identify that the Select All option was chosen?

Thanks in Advance,
Bessebo

 

[jymm]

I did it this way when I was allowing them to select many items

First in the header I put this

& ", Items = " & Join(Parameters!ItemLabel.Label, ", ") &

Second in the Where clause of the SQL I put this

(RevenueReportLabel IN (@ItemLabel) OR 'ALL' IN (@ItemLabel))

and for the select part of the list of items I have a simple union tagged into the SQL

select 'ALL' as RevenueLabel
union all
select...

This point you in the right direction?

 

[bessebo]

Hi,
Thanks for the post. I actually found a better way to do this. If you choose the multi-value parameter (checkbox that is available to you under Report Parameters) the 'All' option will be available to you automatically. There is no need to perform the union query because if you choose the All option the string of all of the values will be strung together automatically. So you do not need either the "OR 'ALL' in (@ItemLabel) or the union in the query. Try it out. It works great.

Bessebo

 

[jymm]

ah - I figured that you were still on an old version of SSRS - if you are back a service pack you do not get that capability interestingly enough.

 

[bessebo]

So if we upgrade to the next service pack this report will break?

 

[bessebo]

jymm,
I'm confused with your post. Did you mean that you were on an old version of SSRS or I am? You say "if you are back a service pack you do not get that capability interestingly enough". Not sure exatly what you mean here...

Thanks

 

[jymm]

I am working a bit from memory - newer versions of SQL are fine - I doubt it will break as they progress. If you were on a previous SP of SQL there were a number of things (like the 'all') that you had to do by hand (as I did in my exampel).

I have one SQL box that is still on SP 1 (so if you go into a new query and do 'select @@version' you will see that it says Service Pack 1) - because it is running some vendor software (ick pooey) that was not working well when we installed SP2 of SQL.

So - in short - on SP1 - 'all' was not working like you are seeing now.

make sense?

 

[bessebo]

OK that makes sense. I understood you saying that we were on an older version so if we upgraded we could have a problem. This makes more sense to me. Thanks for your post and the multi-value option that automatically gives you the ALL option is really a great thing. I have also been using cascading prompts (or prompts that are dependent on other prompts) and that is working great.

Regards,
Bessebo

 

[MisterMo]

AI think it was the first release of SP2 that removed the "ALL" option on the multiselect. Not a bug it was intentional because early users use to to have 1000's in a list and select them all causing the report to break, and then complaining to Microsoft.

Had to call Microsoft to get the patch and I guess I wasn't the only one because it was all restored soon after.

-Mo

 

[bessebo]

Thanks for the info Mo...

Regards,
Bessebo

 

[rogerzebra]

Hi,
It seems that both of you know how to get the multivalue parameter option to functioning. I have struggled with this for a while now and I would really appreciate if any of you could give me some help to finish this. I works fine in SQL server but apparently not in reporting services. A couple of questions come up when I saw this thread.
A) Do I need to use a sub dataset with the union code that you are mentioning for this to work?

select 'ALL' as RevenueLabel
union all
select...

B) Do I need to create a header with the code beneath you are referring to for this to functioning?

& ", Items = " & Join(Parameters!ItemLabel.Label, ", ") &

Here is my settings in the Report Parameters
Parameters: ParamMV

Name: ParamMV
DataType: string
Prompt: ParamMV
Multivalue option checked.

Avaliable values: Non queried

Label: Producercode

Value: =Join(Parameters!ParamMV.Value,", ")

Default value: Null

select ....prodcucercode
from ...tbl
where ....
and producercode IN (SELECT Item FROM dbo.Split1 (@ParamMV, ','))

Any help on this would be great.
Thanks in advance.
rogerzebra

 

[MisterMo]

hi rogerzebra,

the way I use multi values is this:

@MultiParam string

Multivalue option checked.
Value could be from select statement or you add the values manually.

@MultiParamList string
hidden
value: =Join(Parameters!MultiParam.Value,", ")
default value: non quered = Join(Parameters!MultiParam.Value,", ")


select ....field1,field2...
from ...tbl
where ....
and field1 IN (SELECT Item FROM dbo.Split1 @MultiParamList , ','))

I assume dbo.Split1 is a function that will create a table of values from the list you provide.

if so you need to set the default delimeter to ',' so you don't have to pass it and your code will look neater.




-Mo