Report Builder 3.0可选参数

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

是否有机会在报表生成器中使用可选参数?

for example: i have a query with 3 parameters

例如:我有一个包含3个参数的查询

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

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

如果我在没有通知的情况下运行View报告,那么我收到了消息:

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

选择参数@ Pa3的值(例如)

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

2 个解决方案

#1

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.

如前所述,您需要选择ALLOW BLANK VALUES,并允许NULL VALUE ..但您还必须确保您的SQL知道如何处理WHERE子句中的VALUES。

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.

对于日期范围,我建议在运行SELECT语句之前声明另一个计算日期值的变量。创建一个变量,计算值为空,空或输入时的值。

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

变量可以进入@ Pa1但是然后计算到@ fecha1,然后在WHERE子句中我们@fecha1。

#2

Allow Null Values or Blank values for your parameter.

允许参数的Null值或空值。

#1