Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Challenge yourself. Aim high
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

20
March

How Do I Display ALL for Multi Value Dynamic Parameters in SQL Reporting Services 2008 R2?

During a recent training workshop I was asked the question "How do I display the words "ALL", when the Select All option of an multi value dynamic parameter is checked in SQL Server Reporting Services 2008 R2?"


Solution:

When dynamic multivalue parameters are designed for reports within SQL Server Reporting Services, one of the checkboxes, when the report is run, is the Select All option.

One of the downsides is that when there are a number of available values for the parameter, the resultant formula, indicating the user's selection, could result in a long list of values.

Unfortunately there is no direct function available within Reporting Services to enable this to be done easily.

However, as a workaround, one can use the Parameter field property collection, to achieve the required results.


To display the words "ALL" when the user checked the Select All option of a multi-value dynamic parameter:
  1. Create a new dataset as a data source for the dynamic parameter - Parameterds
  2. Create a new parameter - @Parameter_1
  3. In the Parameter Properties for @Parameter_1
  4. Select the All Multiple Values option
  5. Click on the Available Values Properties
  6. Select the Get values from a query option
  7. Populate the values from the dataset - Parameterds
  8. Create a new parameter - @Parameter_1_Hidden
  9. In the Parameter Properties for @Parameter_1_Hidden
  10. In the Select the Parameter Visibility - Hidden
  11. Click on the Default Values Properties
  12. Select the Get values from a query option
  13. Populate the values from the dataset - Parameterds
  14. Add a text box to the report canvas
  15. Right click on the text box, select Expressions
  16. In the Expression dialog box, enter the following syntax:
=IIF(Parameters!Parameter_1.Count = Parameters!Parameter_1_Hidden.Count, "ALL", Join(Parameters!Parameter_1.Value,", "))



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

jenny tseng

05
March
Do you have screen shot for each step. Its very confuse there I should add each step. Is under the dataset or is under parameter? Thanks Jenny
2

Julia

06
March
Hi Jenny. Sorry there are no screen shots for this How to.

Basically you need to create 2 parameters, (Parameter_1 and Paramater_1_Hidden) which are the same.

Create a dataset that will be used to populate the Available values for the Parameter_1 and the Default values for the Parameter_1_Hidden, using the get values from Query option.

These are accessed via the Parameter Properties dialog box.

Then you can a text box and the expression.
3

Andy

11
April
An alternative is not to use two parameters, but use one parameter and the dataset used to populate the list of values: e.g. =IIF(Parameters!Parameter_1.Count = Count(Fields!Parameter_1.Value, "Parameter_1_List"), "ALL", Join(Parameters!Parameter_1.Label,", ")) Hope this helps
4

Swati

20
January
Cool! great Idea. Thanks for sharing.
5

mario

01
August
Can you provide example of adding a dataset content? Thank you in advance!
6

BB

26
February
So you will have multi valued parameter that shows list of values when 'Select All' is chosen + text box in you report, that shows ALL in that case. Then what?
7

AA

21
September
How to show "All" in top in the header like where we are selecting parameters but send the comma separated values?
8

AA

21
September
How to show "All" in top in the header like where we are selecting parameters but send the comma separated values?

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

26 + 37 =