Using Custom Code To Create An 'ALL' Parameter Value Option In SSRS

We have not been using SSRS over here as long as we have Crystal, but it is clearly taking the throne as the defacto report writer ever since SAP bought it (if you are an Epicor or Infor the last thing you want to tell your customers to do is install a SAP product). So as we accept the inevitable there are a lot of little things one used to be able to do in Crystal that need to be figured out in SSRS. One that came up recently is a parameter that includes an “ALL” option - for example, you might have an “Order Status” parameter and you want options for “Open”, “Closed”, or “All”. Now if we only had to deal with the first two options this would be a trivial process - define the parameter and map it to the field. But that “All” option requires some additional SSRS custom code and logic for the parameter field value.

First, let’s look at the defined parameter:

So pretty basic stuff here, I have a parameter called ‘CustomerType’ with values for ‘Suspect’, ‘Customer’, and ‘All’. ‘Suspect’ and ‘Customer’ both map to actual database column values whereas 'All’ is a special state we will be testing for when applying this parameter within the filter. So now we swing over to the dataset properties / filters section - if all we cared about were ‘Suspect’ and ‘Customer’ we could get away with something really basic like this:

But since we need to accommodate for that ‘ALL’ condition we need to put in a little extra effort. There are almost certainly other ways one could go about this, but our approach was to basically say:

If the customer type is ‘CUS’ or ‘SUS’, match this value up to the CustomerType column. If the value is ‘ALL’, test for something that is true no matter what - in our case “ALL = ALL”.

So let’s see how that actually gets implemented. First we start by hitting the ‘fx’ button next to ‘Expression’. This is where we define the column name we are comparing against.

If you have ever written an Excel formula you will see exactly what is going on here. It is using an IIF function where the first argument is the test (is the parameter value “CUS” or “SUS”) and if that is true the second argument is returned (in this case it is the field in the dataset). The last argument is what the expression returns if the test is false - in this case the text string “ALL”.

At this point our value is just a pass-through of the parameter:

And our report does exactly what we’d expect it to do. You pick ‘Customer’ and you only get accounts defined as customers, ‘Suspect’ and you get accounts defined as suspects, and ‘All’ to get a combo of both.

In the end, this is almost identical to the way we used to do things back in Crystal before they added optional parameters. Anyway, hope this helps somebody out there. If so, please leave me a comment below!