missice's Blog

USER 需要一張報表，如下圖：
上方的 ISO9001 條款部份，是屬於固定欄位，而左方的單位名稱，則非固定欄位。

其實看到這樣的報表，想當然爾，直接拉個矩陣元件，可是這樣的話，
上方 ISO9001 條款的部份，就會變成非固定欄位了，
因此必須在 Report 上拉一個資料表，直接在表首把 ISO9001 那些東西畫出來，真是麻煩呀！

畫好之後，還有更麻煩的地方，就是怎麼統計資料，然後怎麼把資料餵給 Report，
主要是透過 SQL 的 fetch 指令來跑迴圈，並用 count 計算，把計算好的資料塞到暫存資料表(即時計算)，
再將暫存資料表當作 DataSource 餵給 Report，接著再把暫存資料表清空就好了。

以下是我研究出來的 SQL 指令，但是欄位的部份，作法真的還滿蠢的，
如有更好的作法，還請大家不吝於指教喔。

create table #t(c01 int,c02 int,c03 int,c04 int,c05 int,
				c06 int,c07 int,c08 int,c09 int,c10 int,
				c11 int,c12 int,c13 int,c14 int,c15 int,
				c16 int,c17 int,c18 int,c19 int,c20 int,
				c21 int,dep char(20))

declare @dep_name char(20)
declare dep_cursor cursor for 
select distinct audeptname from gf_bpm_au_audit where autype='03' 
       and applydate>=@datefrom and applydate<=@dateto 
open dep_cursor
fetch next from dep_cursor into @dep_name
while @@fetch_status=0
begin
	declare @ct01 int,@ct02 int,@ct03 int,@ct04 int,@ct05 int,
			@ct06 int,@ct07 int,@ct08 int,@ct09 int,@ct10 int,
			@ct11 int,@ct12 int,@ct13 int,@ct14 int,@ct15 int,
			@ct16 int,@ct17 int,@ct18 int,@ct19 int,@ct20 int,
			@ct21 int

	select @ct01=count(1) from gf_bpm_au_audit
	where autype='03' and basis='01' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct02=count(1) from gf_bpm_au_audit
	where autype='03' and basis='02' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct03=count(1) from gf_bpm_au_audit
	where autype='03' and basis='03' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct04=count(1) from gf_bpm_au_audit
	where autype='03' and basis='04' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct05=count(1) from gf_bpm_au_audit
	where autype='03' and basis='05' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct06=count(1) from gf_bpm_au_audit
	where autype='03' and basis='06' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct07=count(1) from gf_bpm_au_audit
	where autype='03' and basis='07' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct08=count(1) from gf_bpm_au_audit
	where autype='03' and basis='08' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct09=count(1) from gf_bpm_au_audit
	where autype='03' and basis='09' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct10=count(1) from gf_bpm_au_audit
	where autype='03' and basis='10' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct11=count(1) from gf_bpm_au_audit
	where autype='03' and basis='11' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct12=count(1) from gf_bpm_au_audit
	where autype='03' and basis='12' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct13=count(1) from gf_bpm_au_audit
	where autype='03' and basis='13' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
    select @ct14=count(1) from gf_bpm_au_audit
	where autype='03' and basis='14' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct15=count(1) from gf_bpm_au_audit
	where autype='03' and basis='15' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct16=count(1) from gf_bpm_au_audit
	where autype='03' and basis='16' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct17=count(1) from gf_bpm_au_audit
	where autype='03' and basis='17' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct18=count(1) from gf_bpm_au_audit
	where autype='03' and basis='18' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct19=count(1) from gf_bpm_au_audit
	where autype='03' and basis='19' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct20=count(1) from gf_bpm_au_audit
	where autype='03' and basis='20' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto
	select @ct21=count(1) from gf_bpm_au_audit
	where autype='03' and basis='21' and audeptname=@dep_name 
		  and applydate>=@datefrom and applydate<=@dateto

	insert into #t (c01,c02,c03,c04,c05,c06,c07,c08,c09,c10,
					c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,
					c21,dep)
    values (@ct01,@ct02,@ct03,@ct04,@ct05,@ct06,@ct07,@ct08,@ct09,@ct10,
			@ct11,@ct12,@ct13,@ct14,@ct15,@ct16,@ct17,@ct18,@ct19,@ct20,
			@ct21,@dep_name)
	fetch next from dep_cursor into @dep_name
end
close dep_cursor
deallocate dep_cursor
select * from #t
drop table #t