SSRS DB2 When parameter is null/empty return all values

Question

I have some report parameters that are not defaulted to anything. On start up of the report I would like it to show all results and give the user an option to filter out records by then entering parameters.

Here is the query I am trying which works if I hard-code some strings for the parameters but fails when I put the query into the report with the question marks:

with data as(
select product, productnumber, employee, dateran, timeran, 
       filename, standarddeviation, highdata, lowdata
from   schema.table x 
)
select * 
from data
where product like '%' || ? || '%' 
and employee like '%' || ? || '%' 
and productnumber like '%' || ? || '%'

Each of the parameters are text format and the query will 'work' in the report if do the where clause like this:

where product like ?
and employee like ?
and productnumber like ?

But all of the results then will not show on start up because the empty strings.

Any advise is appreciated. Thank you!

Hogan · Accepted Answer · 2017-02-09 17:39:28Z

2

There is a standard trick to use for null becoming wildcard which is the function coalesce -- like this:

   product like coalesce(?, product)

if there is a value this evaluates as

   product like value

if there is a null this evaluates as

   product like product

which is always true.

answered Feb 9, 2017 at 17:39

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSRS DB2 When parameter is null/empty return all values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related