How do I set up report parameters in Report Builder?

This article shows you how to add a parameter to your report. A parameter lets the End User have more control when running the report.

1. Add New Dataset

Report Builder requires a dataset be added that is specific to the parameter, even if the field(s) selected for the dataset are the same as in the other dataset(s) in the report already.

Right-click on Datasets. Click Add Dataset...

2. Dataset Properties

3. Select the Fields for this Dataset

4. Open Dataset Query as Text

If your parameters dataset will result in multiple results having the same value, the value will repeat in the parameter drop down for the end user. To prevent the repeating values, select the Function button to open the query as text.

5. Modify Query to Select Distinct (If Needed)

  1. When the Query opens as a text file, type the word "distinct" after SELECT. This will ensure that the parameters drop down menu only shows distinct options rather than repeating the same value multiple times.
  2. Click the OK button.

6. Close the Dataset Properties

When finished, click the OK button.

7. Dataset Added

The Dataset has been added under the Datasets heading.

8. Add Parameter

Right-click on Parameters. Click Add Parameter.

9. Parameter Properties - General

Select your settings for the General section:

  1. Name: Enter the back-end name for this parameter. Note: No spaces.
  2. Prompt: Enter the display name (prompt) that the end user will see when running the report.
  3. Data Type: Select your Data Type.
  4. Allow Blank Value: Select this check box if your users can skip selecting a parameter when running the report. Leave the check box blank to make the parameter selection required.
  5. Allow Null Value: Select this check box if users can select a null value.
  6. Allow Multiple Values: Select this check box if users can select multiple values in the parameter drop down menu.
  7. Parameter Visibility: Select whether this parameter should be visible, hidden, or internal. Select Visible if the end user is to be able to select the parameter for the report.

10. Parameter Properties - Available Values

Choose the available values for the parameter.

  1. Specify Values: For this option, you would enter the available options for selection. Note: If you change the values/options in your site later, they may not match the hard-coded values, and may cause the report to error.
  2. Get Values from a Query: This is the more dynamic option, since it pulls the available values based on a query as set in the Dataset previously. Select this option.
  3. Dataset: Select the Dataset you made in the previous steps.
  4. Value Field: Select the value field for this parameter.
  5. Label Field: Select the label field. This may be different than the Value Field, depending on the type of field selected as your Value Field.

11. Parameter Properties - Default Values

Choose from the following options for a default value, if desired:

  • No Default Value
  • Specify Value
  • Get Values from a Query

12. Parameter Properties - Advanced

  1. Select when to refresh the data. Select the default value Automatically determine when to refresh.
  2. When finished, click the OK button.

13. Parameter Added

The newly added parameter will show under the Parameters heading.

14. Open Dataset Properties

Now that the Parameter has been created, you will need to create a Filter so that the results of the report are filtered based on the selected Parameter.

  1. Right Click the report Dataset. Select Dataset Properties.

15. Open the Filters Section and Add Filter

  1. Select he Filters Section Heading.
  2. Click the Add button.

16. Build Filter

The filter you create will limit which data appears in your report.

  1. Expression: Select the Function key (fx) and select the field that you want to filter in/out for your report.
  2. Operator: Select In. This means that the only results will be those that qualify in your parameter.
  3. Value: Type in your parameter in the following format: [@parametername]. Ex: [@ProjectManagers]. The Parameter is the back end/internal field name.
  4. Click the OK button.

17. Run Report to Test

To test the report, click the Run button.



Please sign in to leave a comment.