You can create an ALL parameter for your users by providing the following:

• A dataset to populate the parameter values
• A report parameter
• A list of values for the report parameter
• A dataset with a parameter

This how-to topic uses the Reels.mdb data source.

To create a dataset to populate the parameter values

1. Click the Add DataSet icon.
   
2. In the DataSet smart panel that appears, select the Query page.
3. Enter a SQL command like the following into the Query text box where a UNION SELECT statement combines the results of the ALL query with those of the query for the individual values.
    

   SELECT -1 AS ProductionID, "(All)" AS Name
   FROM Producers
   UNION
   SELECT ProductionID, Name
   FROM Producers;

4. Click the Accept button in the lower right corner to close the smart panel and see the dataset and the selected fields appear in the Data Explorer.
    

To add a Report Parameter

1. In Layout view, click in or below the report body to ensure that the report has focus.
2. From the Report menu, select Report Parameters.
3. In the Report Parameters smart panel that appears, click the Add button to add a parameter.
   
4. Ensure that the Data type matches that of the field (i.e. Integer for ProductionID).
5. Enter text to prompt users for a value.
6. Select the check box next to Multivalue to allow users to select more than one item from the list.

To provide a list of values for the Report Parameter

1. Just below the list of parameters, click the Values icon.
   
2. Under Available Values, select From query.
3. Under Dataset, select the dataset created above.
4. Under Value field, select the field given for -1 (i.e. ProductionID).
5. Under Label field, select the field given for "All" (i.e. Name).
6. Click the Accept button in the lower right corner to close the smart panel and add the parameter to the collection.
   

To add a dataset with a parameter

1. Click the Add DataSet icon.
   
2. On the Parameters page, add parameters like the following to the dataset to provide values for the parameters we add to the query in step 3 below.
   
   • Parameter Name: Param1; Value: =Parameters!ProductionID.Value
   • Parameter Name: Param2; Value: =Parameters!ProductionID.Value
3. On the Query page, enter a SQL command like the following in the Query text box:
    

   SELECT Movie.Title, Movie.YearReleased, Movie.UserRating, Producers.Name
   FROM Producers INNER JOIN (Movie INNER JOIN MovieProducers ON Movie.MovieID = MovieProducers.MovieID) ON Producers.ProductionID = MovieProducers.ProductionID
   WHERE (MovieProducers.ProductionID IN (?)) OR (-1 IN (?))
   ORDER BY MovieProducers.ProductionID, Movie.YearReleased;

4. Click the Validate icon to validate the query and to populate the Fields list.
   
5. Click the Accept button in the lower right corner to close the smart panel and see the dataset and the selected fields appear in the Data Explorer.
   

See Also