Have you every come across a reporting requirement where the user of the report needs to be able to choose between two sets of criteria to filter on a value in the main report. I recently had a requirement where the report I was developing listed tasks by default that had a status of Active or On Hold but I needed to be able provide the user with the ability include tasks with a status of Initiation, Closed or Cancelled.
The standard report parameter configured for multivalue could meet the underlying requirement by setting the default selections to Active and On Hold and allowing the user to add in the remaining 3 status’ if they required for example:
but for this report the requirement specified that the user should only have to make a yes/no decision regarding whether to include the additional status tasks in the report i.e.
I looked closely at the configuration options for Boolean report parameters and tried a few different things try to configure it so that the two possible values it could be set to were the two status value sets but given it is a boolean the only values it can set the parameter to are 1 or 0. A logical restriction but not helpful for meeting my requirement.
I looked at the options for configuring other types of parameters, such as the Multivalue type, to display like a boolean but again found nothing helpful for meeting my requirement.
I eventually solved my problem using the Boolean type parameter and expressions within the filter criteria on the main report dataset.
Here is how I set it up…
1. First set up the boolean report parameter:
and configure the default value to False (for my requirement)
2. Now create two report parameters that will provide the two value sets for the filter. Set the Name of each parameter so it is evident which one includes and excludes the values. In my case I used StatusExcludeSet and StatusIncludeSet. Set the Data Type to Text and check Allow multiple values. I also selected Internal for visibility as we don’t want either of these parameters to appear to the end-user or be available for setting within SharePoint.
To complete the configuration of the parameter select Default Values from the left panel, select Specify Parameters and then add the default values. In my case these are the actual strings Active and On Hold but check in your dataset to verify you don’t need to specify the actual Id values here rather than the display text.
Don’t forget to repeat this step for the second parameter that includes the additional values in the set that were excluded in the one you’ve just created.
3. Next open properties dialog for the primary dataset, select Filters on the left panel, click Add to create an empty filter and select the relevant field, in my case Status, for the Expression e.g.
4. Next we need to configure the value that will be used for filtering. We will be filtering on a set of values so change the filter Operator to In (as shown in the picture above) and then click on the function (ƒx) button to the right of the Value field. For the expression I used the IIF conditional function to test the value of the boolean parameter IncludeExtraStatusParameter and based on its value return either the value set contained in the Internal parameter StatusExcludeSet or StatusIncludeSet e.g.
=IIF(Parameters!IncludeExtraStatusParameter.Value = CBool("False"), Parameters!StatusIncludeSet.Value, Parameters!StatusExcludeSet.Value)