(Select All) value

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
RobRob,
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|||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
>|||You can just use Count(Fieldname, "Dataset") to retrieve the number instead
of creating a seperate dataset.
"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
> >
> >|||Won't let me edit my post. Something like this.
=IIF(Count(Fields!AcctCode.Value, "AcctCode") =Parameters!AcctCode.Count,"All",Join(Parameters!AcctCode.Label,","))
"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