RDLC报表中的动态SQL

在报表中经常遇到动态的查询条件，比如一个下拉列表，里面有时间，“全部”，“上午”，“下午”。如果要查全部时段的数据，就选全部，那这个如果不用存储过程该怎么写呢？

我最近做了7，8个这样的报表，把这个写下来，以后做个参考

上图就是当条件输入ALL的时候，把所有的Program和Site都显示出来，这时候不能再写固定的SQL，而要根据条件拼接SQL

下面就是数据集里查询的写法，注意SQL语句前的这个等号是一定要写的

= " SELECT site, sum(plan_qty) plan_qty, sum(actual_qty) actual_qty, sum(var) qty_variance, avg(plan_qty) avg_plan,stddev(plan_qty) stddev_plan, count(1) combs, status from ( " & 
  " SELECT a.site, a.hsa_part_no, a.media_part_no, sum(plan_qty) plan_qty, sum(actual_qty) actual_qty,  sum(actual_qty) - sum(plan_qty) var, " & 
  " CASE WHEN (sum(actual_qty) - sum(plan_qty)) < 0 THEN 'UNDER' " & 
  " WHEN (sum(actual_qty) - sum(plan_qty)) = 0 THEN 'MATCH' " & 
  " WHEN (sum(actual_qty) = 0 AND sum(plan_qty) = 0) THEN 'NO BUILD' " & 
  " WHEN (sum(actual_qty) <> 0 AND sum(plan_qty) = 0) THEN 'NO PLAN' ELSE 'OVER' END Status " & 
  " FROM HM_SUMM_COMBO a " &
  " WHERE (DAY_ID >= to_char(sysdate - 5,'YYYYMMDD') AND DAY_ID <= to_char(sysdate + 2,'YYYYMMDD')) " &
  iif(Parameters!Program.Value = "ALL"," "," AND a.PROGRAM = '" & Parameters!Program.Value & "'") & " " &
  iif(Parameters!Site.Value = "ALL"," "," AND a.SITE = '" & Parameters!Site.Value & "'") & " " &
  " GROUP BY a.SITE, a.HSA_PART_NO, a.HSA_DESC, a.MEDIA_PART_NO )c " & 
  " GROUP BY c.SITE, c.status "

注意，这种写法

iif(Parameters!Program.Value = "ALL"," "," AND a.PROGRAM = '" & Parameters!Program.Value & "'") & " " 

根据参数的值，SQL会拼接不同的语句，当值为ALL的时候，相当于是个空字符串，也就是没有条件！