"Rob" wrote:

> Ok, I figured it out and I wanted to put my solution here for others in the
> same boat.
>
> Problem: In SSRS 2005 (non SP1) there is the Multi-value option for a
> parameter. If you want to display the parameter list in a text box, you use
> something like:
>
> =Join(Parameters!param_mso.Label, ", ") //this goes in textbox
> and (m.mso_num IN (@param_mso)) //this goes in the query
>
> This will display every select parameter in a textbox. But what happens if
> you have 100+ parameters? Then you have an unmanageable list of parameters
> being displayed on the report.
>
> So, if the user selects (Select All) from the dropdown list, only display
> "All" in the textbox.
>
> Solution: You need to create a new dataset that only returns the row count
> of the parameter list. So, for example: this is the SQL query that returns a
> list of MSO objects that are associated with a NETWORK parameter
>
> SELECT s.network_id, ms.mso_num, m.mso_name
> FROM service s, mso_service ms, mso m
> WHERE s.service_id = ms.service_id
> and ms.mso_num = m.mso_num
> and s.network_id = @param_network
> GROUP BY s.network_id, ms.mso_num, m.mso_name
> ORDER BY ms.mso_num
>
> I create a new DATASET just to get the count of the above query:
>
> SELECT count(*)
> FROM(
> SELECT s.network_id, ms.mso_num, m.mso_name
> FROM service s, mso_service ms, mso m
> WHERE s.service_id = ms.service_id
> and ms.mso_num = m.mso_num
> and s.network_id = :param_network
> GROUP BY s.network_id, ms.mso_num, m.mso_name
> ORDER BY ms.mso_num)x
>
> This way I know the max number of row items that the original query is going
> to return. I then use the following formula in my textbox:
>
> =IIf(Parameters!param_mso.Count = Parameters!param_count.Value, "ALL",
> Join(Parameters!param_mso.Label, ", "))
>
> So if the count of my MSO parameter equals the total count of the query,
> then display "ALL", otherwise display all of the parameters selected.
>
> I hope that this helps out.
> Rob
>
>
>
>
> "Josh" wrote:
>
> >
> > Rob,
> >
> > I have an idea, but I don't know how good it is...
> >
> > If there is a way to count the number of values selected, you could
> > compare that with a count of the records in the dataset. However, since
> > I don't know if that is possible, here is an idea...
> >
> > You are using a dataset to generate the "Available Values" for one
> > parameter. Use this same dataset as the default value for a new,
> > internal parameter. This parameter will not be used for anything other
> > than holding all of the values that could be selected.
> >
> > Then, use an expression to compare the 2 JOIN results.
> > Iif(JOIN(Parameters!mainParm.Value,",") =
> > JOIN(Parameters!internalParm.Value,","), "All",
> > JOIN(Parameters!mainParm.Value,",") )
> >
> > Hope this helps!
> >
> > -Josh
> >
> >
> > Rob wrote:
> > > I would like to display "ALL" in a text box if the (Select All) option is
> > > selected as a parameter.
> > >
> > > I can display 1, 2, 5 to every single selected value in the textbox using
> > > the JOIN function. But if the (Select All) parameter is selected, I would
> > > rather display "All" in my textbox instead of 50+ parameters.
> > >
> > > I have tried something of the following
> > >
> > > =IIf(Parameters!param.Value = "Select All" , "All",
> > > Join(Parameters!param.Value, " | "))
> > >
> > > But this does not work.
> > >
> > > Any suggestions out there?
> > >
> > > Thanks
> > > Rob
> >