SSRS中实现用户输入查询条件组合查询

一 需求

　　用户查询条件为 ：

　　1. 起始工单号

       2. 起始开单日期

       3. 产品编号

       4. 材料编号

　　如下图 ，用户可以输入任意的查询条件查询。除日期外都不是必输项：

       

 

　DataSet 中的Query语句如下：

　　黄色语句部分是实现组合查询的逻辑代码

　　

SELECT WADOCO,WAMCU,PRODUCT,IMDSC1,ProjectName,WABREV,ORDERDATE,COMPONENT,COMPONENTDSC,MaterialRequestQTY,TotalIssueQTY,
LineNumber,IssueType, SUM(MaterialIssueQTY) AS MaterialIssueQTY ,issueLOT,vendorcode,vendorname,
WOORDERQTY,CompleteQTY,ScrapQTY,(WOORDERQTY-CompleteQTY-ScrapQTY) as OpenQTY, CompleteDate,
SALESORDER,SHIPQTY,SHIPPINGDATE,JLNUMBER,
Customer,CustomerDSC,ShipLocation
FROM (
SELECT DISTINCT WADOCO,WAMCU,WALITM AS PRODUCT,b.IMDSC1,b.IMDSC2 as ProjectName ,WABREV ,NTOD(WATRDJ) AS ORDERDATE ,
WMCPIL AS COMPONENT,d.IMDSC1 AS COMPONENTDSC,
c.WMUORG/100000 AS MaterialRequestQTY ,c.WMTRQT/100000 TotalIssueQTY,c.WMCPNB as LineNumber,e.ILDCT as
IssueType,e.ILTRQT/100000 as MaterialIssueQTY,
e.ILLOTN AS issueLOT,f.ILAN8 AS vendorcode,l.ABALPH as vendorname,
case when exists((SELECT WTUORG FROM proddta.F554801T WHERE WTDOCO = WADOCO))
then (SELECT WTUORG/100000 FROM proddta.F554801T WHERE WTDOCO = WADOCO) else WAUORG/100000 end AS WOORDERQTY ,
WASOQS/100000 AS CompleteQTY,WASOCN/100000 AS ScrapQTY,NTOD(a.WASTRX) AS CompleteDate, k.ILDOCO AS SALESORDER ,
g.ILTRQT/100000 as SHIPQTY ,NTOD(g.ILTRDJ) as SHIPPINGDATE,h.ILLOTN as JLNUMBER,
k.ILAN8 AS Customer,k.ILTREX AS CustomerDSC,m.illocn as ShipLocation
FROM PRODDTA.F4801 a join PRODDTA.F4101 b on a.WALITM=b.IMLITM
left join PRODDTA.F3111 c on a.WADOCO=c.WMDOCO AND a.WAMCU=c.WMMCU
join PRODDTA.F4101 d on c.WMCPIL=d.IMLITM and d.IMSTKT='P'
left join (SELECT ildoco,ilmcu,ildct,illitm,illocn,illotn,sum(iltrqt) as iltrqt,illnid FROM PRODDTA.F4111 e1
group by ildoco,ilmcu,ildct,illitm,illocn,illotn, illnid
) e on c.WMDOCO=e.ILDOCO and c.WMCPIL=e.ILLITM
and e.ILLNID=c.WMCPNB*10 and e.ILDCT IN('IM','IL')
--PRODDTA.F4111 e on c.WMDOCO=e.ILDOCO and c.WMCPIL=e.ILLITM and e.ILLNID=c.WMCPNB*10 and e.ILDCT IN('IM','IL')
left join PRODDTA.F4111 f on f.ILLOTN=e.ILLOTN AND f.ILDCT='OV' and f.ILAN8<>0
left join proddta.f0101 l on f.ILAN8=l.ABAN8
left join PRODDTA.F4111 g on g.ILDCT='IR' and SUBSTR(g.ILLOTN,1,7)=TO_CHAR(a.WADOCO)
left join PRODDTA.F4111 h on h.ILDOC=g.ILDOC AND h.ILDCT='IR' AND h.ILFRTO='T'
left join PRODDTA.F4111 k on k.ILDCT LIKE 'R%' AND k.ILKCO='00418' AND k.ILLOTN=h.ILLOTN
left join PRODDTA.F4111 m on m.illotn=k.illotn and m.ILDCT='OV'
WHERE
(to_char(a.WADOCO)>=(case when (to_char(:V_WADOCO_F) is not null or to_char(:V_WADOCO_F)<>' ') then to_char(:V_WADOCO_F) else to_char(a.WADOCO) end)
and
to_char(a.WADOCO)<= (case when (to_char(:V_WADOCO_T) is not null or to_char(:V_WADOCO_T)<>' ') then to_char(:V_WADOCO_T) else to_char(a.WADOCO) end) )
AND
(to_char(proddta.ntod(WATRDJ),'yyyy/mm/dd')>=case when (to_char(:V_WATRDJ_F) is not null or to_char(:V_WATRDJ_F)<>' ') then to_char(:V_WATRDJ_F) else to_char(proddta.ntod(WATRDJ),'yyyy/mm/dd') end and
to_char(proddta.ntod(WATRDJ),'yyyy/mm/dd')<= case when(to_char(:V_WATRDJ_T) is not null or to_char(:V_WATRDJ_F)<>' ') then to_char(:V_WATRDJ_T) else to_char(proddta.ntod(WATRDJ),'yyyy/mm/dd') end )
AND
(to_char(a.WALITM)=(case when ( to_char(:V_WALITM) is not null or to_char(:V_WALITM)<>' ') then to_char(:V_WALITM) else to_char(a.WALITM) end))
AND
(to_char(c.WMCPIL)=(case when ( to_char(:V_WMCPIL) is not null or to_char(:V_WALITM)<>' ') then to_char(:V_WMCPIL) else to_char(c.WMCPIL) end))
AND b.IMSRP8='01'
AND A.WAMCU IN(' 4183',' 4184')

)
GROUP BY WADOCO,WAMCU,PRODUCT,IMDSC1,ProjectName,WABREV,ORDERDATE,COMPONENT,COMPONENTDSC,MaterialRequestQTY,TotalIssueQTY,
LineNumber,IssueType, issueLOT,vendorcode,vendorname,WOORDERQTY,CompleteQTY,ScrapQTY,CompleteDate,SALESORDER,SHIPQTY,
SHIPPINGDATE,JLNUMBER, Customer,CustomerDSC,ShipLocation
ORDER BY WADOCO,PRODUCT,COMPONENT,issueLOT, SALESORDER,JLNUMBER,CUSTOMER,CUSTOMERDSC

 

二。 注意事项

　　一开始执行时报字符集不匹配，上网搜集资料发现 case 语句的比较字符必须都 用to_char()转换。