ReportServer Forum

PaulLobaugh

Member

Registered: 2022-04-28

How to use optional parameters

I know how to use parameters in the where clause of a SQL query to allow for null values (using $X{} ) but how do I allow for a parameter to be completely optional?

For example, If I have a report that pulls sales orders, and I want to allow users to filter by EITHER the sales order number or the data range, then the report would have to accept only the date range attributes being filled in (and the order field being blank and untouched) or vice versa (a sales order number given, but no dates).

I know this is doable, as I have seen it done before, but I am not sure how to go about doing it.

jalbrecht

Administrator

Registered: 2016-10-21

Re: How to use optional parameters

Dear PaulLobaugh,
you asked: "... but how do I allow for a parameter to be completely optional?"
The solution here is to provide NULL Handling to deal with the problem. You either provide a value or you do not. A solution on the query level  somehow works like this:
...
where
      NVL(MyTabColumn, '-') = CASE WHEN MyTabParm is NULL then NVL(MyTabColumn,'-') else MyTabParm END

The Condition is evaluated and MyTabParm is either NULL and the Condition is always true (beware of NULL <> NULL here as well) or MyTabParm has a value and the condition is true only for rows with MyTabColumn = MyTabParm.

Using Multiselection Datasource parameters (see https://reportserver.net/en/guides/admi … rameters/) together with $X{IN ...} (see https://reportserver.net/en/guides/admi … rameters/) will provide a correct null handling out of the box for dynamic lists.

Jan