Report Builder 3.0 optional parameter

Question

is there a chance to use optional parameters in report builder?

for example: i have a query with 3 parameters

@Pa1 date
@Pa2 date
@Pa3 varchar(3)

if i run View report without inform one of then i got the message:

Select a value for the parameter @Pa3 (for example)

is it possible?

I tried to use a empty field but i got no data

select a.legajo,c.nombres,e.Descripcion,CONVERT (char(10), a.fecha, 103) as Fecha,a.hora as ENTRADA,
b.hora as SALIDA,
DATEDIFF(HOUR,a.hora,b.hora) as Horas_trabajadas,
c.hor_x_jor Horas_jornada,
 DATEDIFF(HOUR,a.hora,b.hora) -hor_x_jor as Diferencia
 from fichadas_in a, fichadas_out b, empleados c,sucursales d,Clasificacion e
where a.Legajo=b.Legajo
and a.fecha=b.fecha
and a.fecha between @fecha1 and @fecha2
and d.codigo=@sucursal
and a.legajo=c.legajo
and c.CCO=d.Codigo
and e.Codigo=c.Clasif
Order by a.fecha,legajo

Josh McGloghlon Josh McGloghlon · Accepted Answer · 2018-05-11T15:39:41

As already mentioned you need to select ALLOW BLANK VALUES, and ALLOW NULL VALUE.. But you also have to ensure your SQL knows what to do with those VALUES in your WHERE clause.

AND (
       ((@Pa3 IS NOT NULL AND @Pa3 != '') AND d.codigo = @Pa3)
     OR
       ((@Pa3 IS NULL OR @Pa3 = '') AND d.codigo LIKE '%'))
    )

There are other ways to do this, but make sure you account for those values/lack of values.

For the date range, I would recommend declaring another variable that calculates what the date value is before running the SELECT statement.. Create a variable that is calculates what the value is if the value is blank, null, or entered.

The variable may go in to @Pa1 but then calculates into @fecha1, then in the WHERE clause you us @fecha1.

Report Builder 3.0 optional parameter

2 Answers