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

sql reporting

supersleeper 2008-12-05 10:11:15 ‧ 10510 瀏覽

分享至

請問在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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

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