SSRS畫甘特圖

看到這篇文章 ,

Gantt charting for the holidays

SSRS也能做出甘特圖,看了一下 screenshot,

把隨附的rdl檔下載下來

改了一下dataset 的內容,查看了里頭的圖表設置. 很有創意

SELECT     Project, Task, TaskSequence,
           StartDate, EndDate, PercentComplete,
           DATEDIFF(DAY, StartDate, EndDate)
                  * PercentComplete AS CompletedDays,
           DATEDIFF(DAY, StartDate, EndDate)
                  * (1 - PercentComplete) AS RemainingDays
from
(
select
 'Holiday Planner 2007' as Project
,'Make List' as Task
,1 as TaskSequence
,'2007-07-01' as StartDate
,'2007-10-01' as EndDate
,1 as PercentComplete 
union all
select 'Holiday Planner 2007', 'Check List Twice',2,'2007-08-01','2007-11-01',1
union all
select 'Holiday Planner 2007', 'Make Gifts',3,'2007-09-01','2007-12-01',.9
union all
select 'Holiday Planner 2007', 'Pack Gifts',4,'2007-12-10','2007-12-24',.65
union all
select 'Holiday Planner 2007', 'Deliver Gifts',5,'2007-12-24','2007-12-26',0
) as Project

實做上,主要是用bar stacked chart 設計了五個series data做出來的效果

Series1: 放StartDate, 但為了讓X軸在圖表呈現不那麼擁擠,所以將此日期依@LabelBuffer參數值(預設是10)往前推10天,可以更改一下這個數值,來觀看Label的變化,數值愈小,則Label在圖表上就愈顯擁擠,  這個series的color設成和backgroud一樣的白色


Series2:放@LabelBuffer,這是要放置開始日期StartDate的label顯示, 同時把Angle設成-35,這樣在呈現時比較有交錯不會有擠在一起的感覺,這個series的color設成和backgroud一樣的白色

Series3: 放 CompletedDays, 範例是PercentComplete 欄位來計算出完成天數

Series4:放RemainingDays, 範例是以1- PercentComplete來計算出剩餘天數
 
Serires3及4需給定不同的color , 因為這是主要要顯示二段bar


如果StartDate ~ EndDate是100天, PercentComplete 是0.7,則CompletedDays是70天, RemainingDays則為30, 當然,前提是資料來源必需要先計算出已完成的百分比,
所以這幾項欄位可以自已的實際情況來改寫


Series5:放 @LabelBuffer, 作用和Series2一樣, 這是要放置結束日期EndDate的label顯示,所以color也要設成白色

最後用現行的bug tracker依樣畫一張..挺不錯的