オペレーション 活用編
企業の経営活動において、予実管理は欠かせない存在です。
今回は営業部門を想定し、担当者別に毎月の予算・実績・その予実差を集計し一覧表示するアプリケーションの作り方を解説します。
| プロジェクトファイル （作成バージョン：8.0.41.0） | budget-control-2_before.fgcp budget-control-2_after.fgcp（実装済みプロジェクトファイル） |
1.プロジェクトを確認する
今回の演習で使用するプロジェクトを確認します。
手順1-1.テーブルを確認
ナビゲーションウィンドウのテーブル欄には「部署マスタ」、「社員マスタ」、「営業予算管理」、「営業案件管理」の4つが存在しています。
営業予算管理テーブルは月単位で金額を集計しています。
対して営業案件管理テーブルでは、案件のフェーズごとにレコードが作成されています。
今回はこの営業案件管理テーブルから実績を集計し、営業予算管理テーブルと突き合わせることで予実管理表を作成します。
そのために、あらかじめフェーズが「受注」となったレコードのみを月単位で集計する、という作業が必要となります。
営業予算管理テーブルと営業案件管理テーブルでは、部署や担当者名のデータをコードでのみ保持しています。
一覧表にこれらの情報を表示するには、それぞれ部署マスタと社員マスタからデータを取得する必要があります。
それぞれのテーブルから必要な情報を集めるには、ビューを使用します。
ビューについては開発スタートガイドやヘルプに記事がありますので、詳細はそちらを参照してください。
また、今回はあらかじめForguncyに内部テーブルを用意していますが、ExcelやCSVなどのファイルからデータを取り込んで使用することもできます。
＞関連記事：ExcelやCSVから予実のデータをインポートする
手順1-2.ページを確認
ナビゲーションウィンドウから「予実一覧」ページを開きます。
ページには部署と書かれたコンボボックスが配置されています。
このコンボボックスは一覧表示するデータを絞り込むのに使用します。
その下の黒い枠線に囲まれたセルは、予実の一覧表を表示するリストビューを配置する範囲です。
2.案件データのデータ構造を整える
今回のアプリケーションでは営業予算管理テーブルの構造を基にして一覧表を作成します。
そのため、ビューを使てって営業案件管理テーブルのデータ構造を整えていきます。
手順2-1.案件テータから年度・年・日付を抽出する
営業案件管理テーブルのデータが持つ日時の情報は一日単位のため、そこからまず年度・年・月の3つを取り出します。
またこの時、フェーズが「受注」であるレコードのみに絞り込む作業も同時に行います。
これらは手順1-1で触れた、ビューを作ることで実現します。
手順2-1-1.ビューの作成
リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_案件管理_年度計算」と入力し、下記のSQL文をコピーして貼り付けます。
・SQL文
SELECT
CASE
WHEN
CAST(営業案件集計.月 AS INTEGER) BETWEEN 1 AND 3
THEN CAST(営業案件集計.年 AS INTEGER) -1
ELSE CAST(営業案件集計.年 AS INTEGER)
END AS 年度
,営業案件集計.年 AS 年
,営業案件集計.月 AS 月
,営業案件集計.部署コード
,営業案件集計.担当者社員番号
,営業案件集計.案件名
,営業案件集計.フェーズ
,営業案件集計.金額
FROM
(
SELECT
strftime('%Y',date((営業案件管理.日付-25569)*60*60*24,'unixepoch')) AS 年
,strftime('%m',date((営業案件管理.日付-25569)*60*60*24,'unixepoch')) AS 月
,営業案件管理.部署コード
,営業案件管理.担当者社員番号
,営業案件管理.案件名
,営業案件管理.フェーズ
,営業案件管理.金額
FROM
営業案件管理
WHERE 営業案件管理.フェーズ = '受注'
) 営業案件集計
ORDER BY
年, 月, 部署コード, 担当者社員番号
OKボタンを押下してウィンドウを閉じます。
手順2-1-2.ビューの確認
ナビゲーションウィンドウからv_案件管理_年度計算ビューを開き、構成を確認します。
営業案件管理テーブルから日付フィールドがなくなり、代わりに年度、年、月のフィールドが増加しています。
また、フェーズフィールドの値も「受注」であるレコード以外は省かれ、存在しなくなっています。
その他のフィールドは営業案件管理テーブルと変化はありません。
手順2-2.案件データを月次集計する
手順2で作成したビューv_案件管理_年度計算の金額データを月ごとに集計します。
この集計も新しいビューを作ることで実現します。
手順2-2-1.ビューの作成
リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_案件管理_月単位」と入力し、下記のSQL文をコピーして貼り付けます。
・SQL文
SELECT
年度
,案件集計.部署コード
,案件集計.担当者社員番号
,SUM(案件集計._4月金額) AS _4月
,SUM(案件集計._5月金額) AS _5月
,SUM(案件集計._6月金額) AS _6月
,SUM(案件集計._7月金額) AS _7月
,SUM(案件集計._8月金額) AS _8月
,SUM(案件集計._9月金額) AS _9月
,SUM(案件集計._10月金額) AS _10月
,SUM(案件集計._11月金額) AS _11月
,SUM(案件集計._12月金額) AS _12月
,SUM(案件集計._1月金額) AS _1月
,SUM(案件集計._2月金額) AS _2月
,SUM(案件集計._3月金額) AS _3月
FROM
(
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,金額 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 4
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,金額 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 5
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,金額 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 6
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,金額 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 7
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,金額 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 8
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,金額 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 9
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,金額 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 10
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,金額 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 11
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,金額 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 12
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,金額 AS _1月金額
,0 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 1
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,金額 AS _2月金額
,0 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 2
UNION ALL
SELECT
v_案件管理_年度計算.年度
,部署コード
,担当者社員番号
,0 AS _4月金額
,0 AS _5月金額
,0 AS _6月金額
,0 AS _7月金額
,0 AS _8月金額
,0 AS _9月金額
,0 AS _10月金額
,0 AS _11月金額
,0 AS _12月金額
,0 AS _1月金額
,0 AS _2月金額
,金額 AS _3月金額
FROM
v_案件管理_年度計算
WHERE CAST(v_案件管理_年度計算.月 AS INTEGER) = 3
) 案件集計
GROUP BY
案件集計.年度
,案件集計.部署コード
,案件集計.担当者社員番号
OKボタンを押下してウィンドウを閉じます。
手順2-2-2.ビューの確認
ナビゲーションウィンドウからv_案件管理_月単位ビューを開き、構成を確認します。
基になったv_案件管理_年度計算ビューから構成が大幅に変化しています。
年度、部署コード、担当者社員番号フィールドは変わらず存在していますが、その後には_4月～_3月という12個の新しいフィールドができています。
ここには、それぞれの月ごとに集計された金額フィールドの値が格納されています。
また、営業予算管理テーブルとビューv_案件管理_月単位を比較すると、フィールド構成が同じになっていることが確認できます。
これで案件データの加工が完了し、予算データと突き合わせる準備が完了しました。
3.予算と案件の実績データを突き合わせる
営業予算管理テーブルとv_案件管理_月単位ビューを合体させて、予実のデータを作成します。
この段階で部署マスタと社員マスタも使用し、部署や担当者の情報をコードではなく名称で表示できるようにしていきます。
手順3-1.ビューの作成
リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_営業予実管理」と入力し、下記のSQL文をコピーして貼り付けます。
・SQL文
SELECT
予実.年度
,予実.部署コード
,MAX(部署マスタ.部署名) AS 部署名
,予実.担当者社員番号 AS 社員番号
,MAX(社員マスタ.氏名) AS 営業担当者
,予実.分類 AS 分類
,SUM(予実._4月) AS _4月
,SUM(予実._5月) AS _5月
,SUM(予実._6月) AS _6月
,SUM(予実._7月) AS _7月
,SUM(予実._8月) AS _8月
,SUM(予実._9月) AS _9月
,SUM(予実._10月) AS _10月
,SUM(予実._11月) AS _11月
,SUM(予実._12月) AS _12月
,SUM(予実._1月) AS _1月
,SUM(予実._2月) AS _2月
,SUM(予実._3月) AS _3月
FROM
(
SELECT
年度
,部署コード
,担当者社員番号
,'予算' AS 分類
,1 AS SORTKEY
,_4月 AS _4月
,_5月 AS _5月
,_6月 AS _6月
,_7月 AS _7月
,_8月 AS _8月
,_9月 AS _9月
,_10月 AS _10月
,_11月 AS _11月
,_12月 AS _12月
,_1月 AS _1月
,_2月 AS _2月
,_3月 AS _3月
FROM
営業予算管理
UNION ALL
SELECT
年度
,部署コード
,担当者社員番号
,'実績' AS 分類
,2 AS SORTKEY
,_4月 AS _4月
,_5月 AS _5月
,_6月 AS _6月
,_7月 AS _7月
,_8月 AS _8月
,_9月 AS _9月
,_10月 AS _10月
,_11月 AS _11月
,_12月 AS _12月
,_1月 AS _1月
,_2月 AS _2月
,_3月 AS _3月
FROM
v_案件管理_月単位 AS 実績
UNION ALL
SELECT
予算.年度
,予算.部署コード
,予算.担当者社員番号
,'差異' AS 分類
,3 AS SORTKEY
,実績._4月 - 予算._4月 AS _4月
,実績._5月 - 予算._5月 AS _5月
,実績._6月 - 予算._6月 AS _6月
,実績._7月 - 予算._7月 AS _7月
,実績._8月 - 予算._8月 AS _8月
,実績._9月 - 予算._9月 AS _9月
,実績._10月 - 予算._10月 AS _10月
,実績._11月 - 予算._11月 AS _11月
,実績._12月 - 予算._12月 AS _12月
,実績._1月 - 予算._1月 AS _1月
,実績._2月 - 予算._2月 AS _2月
,実績._3月 - 予算._3月 AS _3月
FROM
営業予算管理 AS 予算
LEFT JOIN v_案件管理_月単位 AS 実績
ON 予算.年度 = 実績.年度
AND 予算.部署コード = 実績.部署コード
AND 予算.担当者社員番号 = 実績.担当者社員番号
) 予実
LEFT JOIN 社員マスタ
ON 予実.担当者社員番号 = 社員マスタ.社員番号
LEFT JOIN 部署マスタ
ON 予実.部署コード = 部署マスタ.部署コード
GROUP BY
予実.年度
,予実.部署コード
,予実.担当者社員番号
,予実.分類
,予実.SORTKEY
OKボタンを押下してウィンドウを閉じます。
手順3-2.ビューの確認
ナビゲーションウィンドウからv_営業予実管理ビューを開き、構成を確認します。
営業予算管理テーブルが持っていた年度、部署コード、担当者社員番号に加えて部署マスタから取得した部署名フィールドと社員マスタから取得した営業担当者フィールドが生成されました。
各レコードには「分類」というフィールドが追加されており、「予算」「実績」「差異」の3種類の値がセットされています。
分類フィールドの値が「予算」のレコードは、金額フィールドに営業予算管理テーブルから取得した金額のデータがセットされています。
その一方、分類フィールドの値が「実績」のレコードには金額フィールドにv_案件管理_月単位ビューのデータがセットされています。
最後の分類フィールドの値が「差異」のレコードは、v_案件管理_月単位ビューの金額（＝実績額） – 営業予算管理テーブルの金額（=予算額）がSQLで計算され、金額フィールドにセットされるようになっています。
これで担当者1人につき分類の値が予算・実績・差異3つのレコードが生成され、月ごとに比較して見られるようになりました。
4.一覧を表示する画面を作る
ここまでの手順で整形したデータを一覧表形式で表示するページを作成します。
手順4-1.リストビューの配置
ページ上の黒い枠線に囲われた範囲のセルを選択した状態で、ナビゲーションウィンドウからv_営業予実管理ビューをそのセル範囲にドラッグ&ドロップします。
リストビューがページに配置されたら、リボンの[ホーム]＞[フォント]＞[罫線]から枠線なしを選択して黒い枠線を消去してください。
手順4-2.項目をセット
リストビューにv_営業予実管理ビューの各項目をセットしていきます。
ナビゲーションウィンドウのv_営業予実管理ビューを展開し、フィールド名が表示されるようにします。
リストビューの列ヘッダーの真下の行に、下記のように項目を配置します。
| フィールド名 | セルサイズ |
|---|---|
| 部署名 | 4 |
| 営業担当者 | 4 |
| 分類 | 4 |
| _4月 | 4 |
| _5月 | 4 |
手順4-3.クエリーの設定
ページにあらかじめ作成されていたコンボボックスを使用して、表示するデータを絞り込む処理を実装します。
データの絞り込みにはリストビューにクエリーを設定します。
リストビューを右クリックし、[クエリー条件…]をクリックします。
クエリー条件ウィンドウが開くので、下記の3つの条件を設定します。
| And/Or | フィールド | 条件 | 値 |
|---|---|---|---|
| ─ | [部署コード] | =（等しい） | =E5 |
これで条件指定が完了しました。
手順4-4.外観の設定
ここまででアプリケーションの要件は満たしていますが、データを見やすくするためにリストビューの外観を整えます。
手順4-4-1.部署名・営業担当者を複数行表示に変更
部署名の列は、同じ部署名が複数行に渡って表示されます。
この状態だと表が見づらくなるため、同じ値が複数行に渡って続く時はセルを自動で結合するよう設定を行います。
部署名の一番上のセル、B8セルを選択した状態でリボンの[リストビューツール]＞[デザイン]＞[列設定]＞[セルの自動結合]にチェックを入れます。
この設定は列ごとにオン/オフが可能です。
営業担当者も3行分同じ値が続くので、同様の設定を行い自動結合されるようにします。
手順4-4-2.セルの書式設定
金額フィールドの値を見やすくするため、セルの書式設定を行います。
リストビュー内のセルの書式設定は明細の1行目に設定すると他の行にも適用されます。
そのため、8行目の各セルにのみ設定を行います。
設定を行うには各セルを右クリックし、[セルの書式設定]をクリックします。
セルの書式設定ウィンドウが開くので、[表示形式]タブで下記のとおりに設定を行います。
対象となる項目は_4月～_3月の、金額が入っている列です。
| 分類 | 小数点以下の桁数 | 記号 | 負の数の表示形式 |
|---|---|---|---|
| 通貨 | 0 | ￥ | ￥-1,234 |
これで、ページの設定は完了です。
5.デバッグを実行する
リボンの[ホーム]>[デバッグ]>[開始]ボタン、またはForguncy Builderの左上にある▶ボタンを押下してプロジェクトをデバッグ実行します。
作成したページがブラウザで表示されます。
担当者ごとに3行データが生成され、各月の予算、実績、差異が一覧表示されていること、またコンボボックスの値を変更することでデータの絞り込みができることを確認してください。
Forguncyで行う予実管理についてもっと知る
製品サイトでは、Forguncyで行う予実管理や今回の記事で使用した機能についてより詳しくご紹介しています。
こちらもぜひご活用ください。
＞Forguncyドリル – 日ごとに集計している実績データを月単位で集計し予実の一覧表を作る
