iT邦幫忙

0

請教Reporting Service報表要顯示本月、前月及三個月前的資訊,應如何撰寫?

請問在MS SQL 2005 Reporting Services裡,想要顯示連續三個月及三個月前的合計,應如何撰寫?
EX:
在資料庫中有日期欄位(date)及金額欄(Amount),在Reporting中有A、B、C、D四個欄位,分別為A表示本月金額總和(假設為11月金額總和),則B欄就需為10月金額總和,C欄為9月金額總和,D欄則為9月之前的總和

這樣的話,條件是要在SQL中就寫好?還是在REPORTING裡面寫會比較好?又應該要怎麼撰寫呢?
請各位大大幫忙解答,謝謝!

jamesjan iT邦高手 1 級 ‧ 2008-12-05 13:15:48 檢舉
select 
p.charfield,
sum(isnull(p.sum1,0)) as sum1,
sum(isnull(p.sum2,0)) as sum2,
sum(isnull(p.sum3,0)) as sum3,
sum(isnull(p.sum4,0)) as sum4
from 
(select
charfield, 
case when left(convert(varchar(6),datefield,112),6)='200811' then sum(isnull(amt,0)) end as sum1, 
case when left(convert(varchar(6),datefield,112),6)='200810' then sum(isnull(amt,0)) end as sum2, 
case when left(convert(varchar(6),datefield,112),6)='200809' then sum(isnull(amt,0)) end as sum3, 
case when left(convert(varchar(6),datefield,112),6)<'200809' then sum(isnull(amt,0)) end as sum4
from Table1
group by charfield,datefield) p
group by p.charfield

1 個回答

20
jamesjan
iT邦高手 1 級 ‧ 2008-12-05 12:26:35
最佳解答

Reporting Service 我沒用過,不過使用 SQL Statement 應該就可以做到

假設 Table Schema 有 charfield, datefield 與 amt 欄位

首先,將各月的業績統計出來,置於暫存檔 tmp1

&lt;pre class="c" name="code">
select charfield,
  case when left(convert(varchar(6),datefield,112),6)='200811' then sum(isnull(amt,0)) end as sum1,
  case when left(convert(varchar(6),datefield,112),6)='200810' then sum(isnull(amt,0)) end as sum2,
  case when left(convert(varchar(6),datefield,112),6)='200809' then sum(isnull(amt,0)) end as sum3,
  case when left(convert(varchar(6),datefield,112),6)&lt;'200809' then sum(isnull(amt,0)) end as sum4
into tmp1
from Table1
group by charfield,datefield

因為有些月份沒有紀錄會形成 null,所以再針對 tmp1 整理即可做出好看的報表了

&lt;pre class="c" name="code">
select charfield,sum(isnull(sum1,0)) as sum1,sum(isnull(sum2,0)) as sum2,sum(isnull(sum3,0)) as sum3,sum(isnull(sum4,0)) as sum4 from tmp1
group by charfield

我要發表回答

立即登入回答