Trac ： 様々なレポートへのカスタマイズ

Trac ： T&Eプラグインを利用した、期間を限定したレポートの作り方

にて、Tracのレポート機能を用いて特定の期間でのチケットの集計などが出来る例を挙げてみましたが、その他にもTracには、レポート作成用のSQLを変更する事で様々な形で集計する事が出来ますので、その例を幾つか書いてみたいと思います。

業務でTracを利用する場合、チケットの集計（レポートの整形化）をして、週次や月次での報告会などで利用すると言う事は多いかと思いますのす。

こういったカスタマイズしたレポートを予め用意しておけば、報告が容易になるかと思います。

では、早速・・・とその前に、レポート作成用のSQLで問合せるテーブルの構造について簡単に解説。

Tracのチケット情報を格納するテーブルの構造

テーブル名 ： ticket

チケットの基本情報が登録されるテーブルです。

カラム名	解説
ID	チケットの管理ID。チケットごとに割り振られているユニークな番号です。 ticket_id_seqというシーケンスによって自動採番されます。
TYPE	チケットに登録された分類名（※）
TIME	チケット登録日時。Unixタイムスタンプで保管しています。
CHANGETIME	チケット最終更新日時。Unixタイムスタンプで保管しています。
COMPONENT	チケットに登録されたコンポーネント名（※）
SERVERITY	チケットに登録された重要度（※）
PRIORITY	チケットに登録された優先度（※）
OWNER	チケットに登録されたそのチケットの担当者
REPORTER	チケットを登録した人
CC	チケットに登録されたチケットの関係者
VERSION	チケットに登録されたバージョン情報です。（※）
MILESTONE	チケットに登録されたマイルストン（※）
STATUS	チケットの現在の状況。 new ： 新規登録 closed ： 解決済み reopened ： 一旦解決したチケットの着手再開 の何れかの値が入ります。
RESOLUTION	チケットの解決方法。チケットのSTATUSが「closed」の場合のみセットされます。 fixed ： 解決（修正） invalid ： 無効なチケット wontfix ： 対応（修正）しないチケット duplicate ： 重複しているチケット worksforme ： 報告が再現しないチケット の何れかの値が入ります。
SUMMARY	チケットの概要
DESCRIPTION	チケットについての完全な説明。Wiki文章のフォーマットでそのまま格納されます。
KEYWORDS	チケットに登録されたキーワード

※ trac-admin又はTracWebAdminで管理できるチケットに付属する要素です。

テーブル名 ： ticket_change

チケットの更新情報が登録されるテーブルです。

カラム名	解説
ticket	チケットの管理ID。ticketテーブルのカラムIDと同じ要素です。
time	変更日時。Unixタイムスタンプで保管しています。
author	変更者
field	変更箇所のカラム名。チケットが変更された際に該当する項目のカラム名が格納されます。 （主にtikcetテーブルのカラム名）
oldvalue	変更前の値
newvalue	変更後の値

ticketテーブルにはチケットの基本属性が、ticket_changeテーブルにはチケットの変更箇所の情報が保存されます。

チケットにコメントを加えた場合は、ticket_changeテーブルに格納されていきます。

では、レポートのカスタマイズを・・・。

Tracの実行環境は、「バグトラッキングシステム「Trac」インストール 」で書いたものと同様です。

全チケットを表示する

何故か存在しないので・・・。
チケットの状態関係なく全てを出力させます。

SELECT p.value AS __color__,
id AS ticket, summary, component, version, milestone, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
case
when status = 'closed' then '解決'
when status = 'new' then '未解決'
when status = 'assigned' then '未解決'
when status = 'reopened' then '未解決'
end as status
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
ORDER BY p.value, milestone, t.type, time

ここでは、チケットの状況（STATUS）が「closed」以外のものを「未解決」チケットとして表示していますが厳密にステータスを表示したければ、「case ～ end」の箇所を変更すれば対応できます。

未解決チケット（コンポーネント別）

コンポーネントごとにグルーピングした未解決チケットの一覧を表示します。

SELECT p.value AS __color__,
component AS __group__,
id AS ticket, summary, milestone, version, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened') -- # 全チケットを対象にする場合はここを削除
ORDER BY (component IS NULL),component, p.value, t.type, time

未解決チケット（分類別）

チケットの分類ごとにグルーピングした未解決チケットの一覧を表示します。

SELECT p.value AS __color__,
t.type AS __group__,
id AS ticket, summary, component, milestone, version, 
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened') -- # 全チケットを対象にする場合はここを削除
ORDER BY (t.type IS NULL), t.type, p.value, time 

私と関連性のあるチケット

「未解決チケット(私のチケットを優先して表示)」というレポートはデフォルトでは存在しますが、ここで表示されるのは、自分が担当者（OWNER）に指定されたチケットのみです。

関係者（CC）に登録されているチケットも併せて一覧に表示させてみます。

SELECT p.value AS __color__,
(CASE owner WHEN $USER THEN 'Owned' ELSE 'CC' END) AS __group__,
id AS ticket, summary, component, version, milestone,
t.type AS type, priority, time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE owner = $USER OR cc = $USER
AND t.status IN ('new', 'assigned', 'reopened') -- # 全チケットを対象にする場合はここを削除
ORDER BY (owner = $USER) DESC, p.value DESC, (status = 'assigned') DESC,time DESC

最初に、自分が担当者に指定されているチケット（Owned）が、次に関係者に指定されているチケット（CC）が表示されます。

マイルストン別の未解決チケット件数集計

マイルストン別の未解決チケットは、デフォルトでレポートに登録されていますが、その一覧の最後に件数を

プラスしてみたいと思います。報告だと、チケットの詳細よりどの位の件数のチケットが発生したとかの方が

重要になりますからね。

SELECT __style__, milestone || ' Release' AS __group__, ticket, summary, component, type, _ord
FROM (
SELECT '' AS __style__, milestone, id AS ticket, summary, component, t.type AS type, 0 AS _ord
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened') -- # 全チケットを対照する場合はここを削除
GROUP BY milestone, t.id, t.summary, t.component, t.type
UNION
SELECT 'background-color:#DFE;' AS __style__, milestone, NULL AS ticket, 'Total: ' || count(t.id) AS summary, NULL AS component, NULL AS type, 1 AS _ord
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened') -- # 全チケットを対照する場合はここを削除
GROUP BY milestone
) AS tmp
ORDER BY milestone, _ord ASC 

ステータス別の全チケット件数集計

全てのチケットを対象にそのステータス別の一覧と、最後にその件数を表示します。

SELECT __style__,
case
when status = 'closed' then '解決'
when status = 'new' then '新規'
when status = 'assigned' then '着手中'
when status = 'reopened' then '差戻し'
end as __group__,
ticket, summary, component, type, owner, _ord
FROM
(
SELECT 
'' AS __style__, status, id AS ticket, summary, component, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, 0 AS _ord
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
GROUP BY status, t.id, t.summary, t.component, t.type, t.owner
UNION
SELECT
'background-color:#DFE;' as __style__, 
status, NULL AS ticket, 'Total: ' || count(t.id) AS summary, NULL AS component, NULL AS type, 
NULL AS owner, 1 AS _ord
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
GROUP BY status
) AS tmp
ORDER BY status, _ord ASC

概要やコメントを含んだ未解決チケット一覧

レポートで報告する際は、ある程度チケットの概要が分かったり、最後に入力されたコメントを備考として扱えば、報告もしやすくなるかもしれません。

コメントは、一番最後に付けられたものを表示するようになっています。

SELECT p.value AS __color__,
id AS ticket, summary, component, milestone, t.type AS type, owner,
time AS created,
changetime AS modified,
case
when status = 'new' then '新規'
when status = 'assigned' then 'アサイン済み'
when status = 'reopened' then '差戻し'
end as status,
description AS _description_,
coalesce((SELECT newvalue FROM ticket_change where ticket = t.id AND field = 'comment' order by time desc limit 1 offset 0), '') AS _comment_
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened') 
ORDER BY p.value, milestone, t.type, time 

ここからは、Timing and Estimationプラグインを使ったレポートです。

Timing and Estimationプラグインのインストールの仕方は下記を

Tracの使い方 －Timing and Estimationプラグイン－

Trac：Timing and Estimationプラグインを入れた後レポート作成できない件

Timing and Estimationプラグインの「Management」メニューへレポートを追加する方法は

Trac ： T&Eプラグインを利用した、期間を限定したレポートの作り方

を参考にしてみてください。

マイルストン別のチケットの進捗度合い

Timing and Estimationプラグインを有効にしている場合、チケット登録時に全体の作業見積り時間と日々の作業時間が入力可能になります。

ここから、マイルストン別のチケットの進捗度合いを表示してみます。

ここでは、見積り時間や作業時間が入力されているチケットのみが対象になります。

※ progressという列がチケットの進捗を表しています。（(総稼働時間 / 見積り時間) × 100）

　　小数点第2位で四捨五入しています。

SELECT __color__, __group__, __style__, ticket, summary, component ,
__milestone__, status, owner, Estimated_work, Total_work, progress, created, modified, _ord 
FROM (
SELECT p.value AS __color__, t.milestone AS __group__, '' as __style__,
t.id AS ticket, summary AS summary, -- ## Break line here
component, milestone as __milestone__, status, owner,
CAST(EstimatedHours.value as DECIMAL) as Estimated_work,
CAST(totalhours.value as DECIMAL) as Total_work,
ROUND((CAST(totalhours.value as DECIMAL) / CAST(EstimatedHours.value as DECIMAL)) * 100, 2) as progress,
time AS created, changetime AS modified, -- ## Dates are formatted
description AS _description_, -- ## Uses a full row
changetime AS _changetime, 0 as _ord
FROM ticket as t
JOIN enum as p ON p.name=t.priority AND p.type='priority'
LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
AND EstimatedHours.Ticket = t.Id
LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
AND totalhours.Ticket = t.Id
LEFT JOIN ticket_custom as billable ON billable.name='billable'
AND billable.Ticket = t.Id
WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED)
AND billable.value in ($BILLABLE, $UNBILLABLE)
UNION
SELECT '1' AS __color__,
t.milestone AS __group__,
'background-color:#DFE;' as __style__,
NULL as ticket, 'Total work' AS summary,
NULL as component,
t.milestone as __milestone__, NULL as status, NULL as owner,
SUM(CAST(EstimatedHours.value as DECIMAL)) as Estimated_work,
SUM(CAST(totalhours.value as DECIMAL)) as Total_work,
ROUND(SUM(CAST(totalhours.value as DECIMAL)) / SUM(CAST(EstimatedHours.value as DECIMAL)) * 100, 2) as progress,
NULL as created, NULL as modified, -- ## Dates are formatted
NULL AS _description_,
NULL AS _changetime,
1 as _ord
FROM ticket as t
JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
AND EstimatedHours.Ticket = t.Id
LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
AND totalhours.Ticket = t.Id
LEFT JOIN ticket_custom as billable ON billable.name='billable'
AND billable.Ticket = t.Id

WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
AND billable.value in ($BILLABLE, $UNBILLABLE)
GROUP BY t.milestone
) as tbl
ORDER BY __milestone__, _ord ASC, ticket

期間を指定、概要・コメント・見積り・作業時間・進捗付きレポート

まぁ、今まで紹介したレポートの良いとこ取りみたいなものです・・・。

今回のレポートは、先ほどのものとは違い、見積りや作業時間が入力されていない（チケット登録時の「Billable」にチェックが入っていない）ものも対象にしています。

期間が指定できますので、Managementメニューから

のように日付を指定して、今回の登録したレポート用のメニューをクリックすると・・・

「Total / Estimate」欄に実績時間と見積もり時間をまとめています。Progressが進捗率です。

（計算式は、一つ前のレポートのものと同様）

SELECT __color__, __style__, ticket, summary, component , severity,
milestone, status, owner
, Total_work || ' / ' || Estimated_work as "Total / Estimate"
,progress
, created, modified
,_description_
,_comment_
,_ord
FROM (
SELECT p.value AS __color__,
'' AS __style__,
t.id AS ticket, summary AS summary, -- ## Break line here
component, severity, milestone, status, owner,
CASE EstimatedHours.value
WHEN '' THEN NULL
ELSE CAST(EstimatedHours.value as DECIMAL)
END AS Estimated_work,
CASE totalhours.value
WHEN '' THEN NULL
ELSE CAST(totalhours.value as DECIMAL)
END AS Total_work,
time AS created, changetime AS modified,
CAST(
ROUND(
(CASE totalhours.value
WHEN '' THEN NULL
ELSE CAST(totalhours.value as DECIMAL)
END /
CASE EstimatedHours.value
WHEN '' THEN NULL
ELSE CAST(EstimatedHours.value as DECIMAL)
END) * 100, 2) as TEXT) as progress,
description AS _description_,
coalesce((SELECT newvalue FROM ticket_change where ticket = t.id AND field = 'comment' order by time desc limit 1 offset 0), '') AS _comment_,
0 as _ord
FROM ticket AS t
JOIN enum AS p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
AND EstimatedHours.Ticket = t.Id
LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
AND totalhours.Ticket = t.Id
LEFT JOIN ticket_custom as billable ON billable.name='billable'
AND billable.Ticket = t.Id
WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED)
AND (time >= $STARTDATE AND time < $ENDDATE)
UNION 
SELECT '1' AS __color__,
'background-color:#DFE;' AS __style__,
NULL AS ticket, 'Total' AS summary, 
NULL AS component, NULL AS severity, NULL AS milestone, NULL AS status, NULL AS owner,
-- SUM(CAST(EstimatedHours.value as DECIMAL)) AS Estimated_work,
SUM(CASE EstimatedHours.value
WHEN '' THEN NULL
ELSE CAST(EstimatedHours.value as DECIMAL)
END) AS Estimated_work,
SUM(CASE totalhours.value
WHEN '' THEN NULL
ELSE CAST(totalhours.value as DECIMAL)
END) AS Total_work,
NULL AS created, NULL as modified,
-- CAST(ROUND(SUM(CAST(totalhours.value as DECIMAL)) / SUM(CAST(EstimatedHours.value as DECIMAL)) * 100, 2) as TEXT) as progress,
CAST(
ROUND(
(SUM(CASE totalhours.value
WHEN '' THEN 0
ELSE CAST(totalhours.value as DECIMAL)
END) /
SUM(CASE EstimatedHours.value
WHEN '' THEN 0
ELSE CAST(EstimatedHours.value as DECIMAL)
END)) * 100, 2) as TEXT) as progress,
'' AS _description_,
'' AS _comment_,
1 AS _ord
FROM ticket AS t
JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
AND EstimatedHours.Ticket = t.Id
LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
AND totalhours.Ticket = t.Id
LEFT JOIN ticket_custom as billable ON billable.name='billable'
AND billable.Ticket = t.Id

WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED)
AND (time >= $STARTDATE AND time < $ENDDATE)
) as tbl
ORDER BY _ord ASC, ticket

SQL次第で幾らでもカスタマイズできますので、ここで紹介したものを参考にして独自のレポートを作ってみても

良いと思います。

[PR]ファイル共有・ファイル保存はOCNマイポケット！【初月利用料無料】

[PR]レンタルサーバの決定版ファーストサーバ

[PR]Microsoft Onlinestore（マイクロソフト直販サイト）

関連記事

保守・運用で使うTrac

バグトラッキングシステム「Trac」インストール

Tracの使い方　- trac-adminの使い方、チケットの管理とロードマップの確認 -

Tracの使い方 - TracWebAdminインストール -

Tracの使い方 - プロジェクトの追加・削除 -

Tracの使い方 －Timing and Estimationプラグイン－

Trac：Timing and Estimationプラグインを入れた後レポート作成できない件

Trac ： コメントの追記や属性の変更などチケットに加わった変更を追跡する方法

Tracの使い方 －TracBurndownインストール－

Trac ： T&Eプラグインを利用した、期間を限定したレポートの作り方

Tracの使い方 - Trac上で管理されている権限一覧 -

Trac： Trac利用ユーザーへのアクセス制限をする方法

Trac： チケットの要素である「バージョン」の使い方がわかりました