エクセルと比較されても困らない。FileMakerでのクロス集計表示を再考してみた。 #SQL

一人情シス＆営業マンの二刀流です。

先日、某会社の事務職が受注データをエクセルに転記（もちろん手打ち）して、さらにそれをメールに添付して送信するって作業を数年間毎日!定期業務としてやっていたのを知って驚愕してしまいました。ええ、恥ずかしながら私の職場での事です。しかも3人でその作業を回していたという。
たまたま人の異動があってその作業にかかっている時間がわかったのですが、なんという時間と労力の無駄というか、何故今まで気づかなかったのか、一人情シスとしてやるせない気持ちになりました。
本人たちはその作業に何も疑問を持たず、ただただ大変だ〜っ、面倒だ〜、て思いながらやっていたみたいです。
すぐにボタン一つで集計から送信までのスクリプトを組んで対応したのですが、少し気づきがあったので記事にします。

エクセル利用の典型例

エクセル利用の典型としてこんなのがあると思います。
予め名前の決まった行・列に数字を打ち込んでいき、合計欄だけSUMが設定されている表。
初めてのエクセルでまず作る表ですが、つまり一番よく見るやつです。ちょっとした計算にはやっぱりこれです。
例えば行に支店名・列に納品アイテムがある例。

伝票から拾った各支店の納品数を延々と手打ちしている人を見かけますが、まさにそれが当方の現場で毎日の定期業務として行われていたのです。

ただ、こういったエクセルの表（クロス集計）をFileMakerで再現させるのって意外と手間です。同じデータを拾うのはわけないのですが、クロス集計として表示させるとなると途端に面倒になる。しかし、エクセルに染まった社会ではエクセルと同じように表示させないと、それはもうダメじゃんと不合格の烙印です。
手打ち用の表を作るのと集計したデータが自動で表示されるのとが同じ土俵で語られます。
なんでこんな簡単な事出来ないの？と見下されたようになるよくある話です。それはある意味ごもっとも。反論しても無意味。

今回の完成動画

Youtube：FileMakerでクロス集計動画

クロス集計表示のサンプル FileMaker Training Book応用編

気を取り直して、今回参考にしたのはFileMaker Training応用編のクロス集計のページ(642付近)、SQLのSELECTにCASE式を使った例です。
以下引用です。

動的なクロス集計レポートを生成できるこの機能には 1 つの大きな制限があります。
列の最大数を事前に決定して、各列に対して計算フィールドを作成する必要があります。
[次の場合にオブジェクトを 隠す] オプションを使って余分な空白列を非表示にします。
高度な SQL スキルがある場合、SQL CASE 関数を使って静的クロス集計レポートのデータを生成する方法もあります。

ExecuteSQL (
"SELECT Salesperson,
SUM (CASE z_Quarter WHEN 1 THEN \"Total\" ELSE 0 END) , 
SUM (CASE z_Quarter WHEN 2 THEN \"Total\" ELSE 0 END) ,
SUM (CASE z_Quarter WHEN 3 THEN \"Total\" ELSE 0 END) , 
SUM (CASE z_Quarter WHEN 4 THEN \"Total\" ELSE 0 END)
FROM \"ORDER\"
WHERE DateOrdered>=? and DateOrdered<=? GROUP BY Salesperson" 
;"|" 
; "¶" 
;Date ( 1 ; 1 ; 2013) 
;Date ( 12; 31 ; 2013)

このクエリーは次の結果を返します。
Alec|0|0|22632|73589.63 
Donaver|0|22179.19|80379.01|24658.71 
Gabe|0|52850.24|58023.68|0 
Jeffrey|0|0|42736.89|126.36 
Kylin|0|70.79|824.22|29898.75 
Marie|0|20710.57|54534.77|102556.33 
Nicole|0|89213.41|153904.17|20863.68 
Reilly|0|0|19734.44|39540.27 
Scott|0|22141.15|29013.12|82662.25 
Winnie|0|2001.43|91823.7|41692.97

クエリーは個々のレコードではなく販売員ごとに四半期の合計を含む 1 つの行を返します。

上記は四半期ごとの営業マンの実績を出していますが、CASE式を利用することでFileMakerでもエクセルの表ライクな列表現が可能です。
月や日などの期間が決まっている場合はCASE式を月数または日数連ねていけばヴァーチャルリストを使ってクロス集計表が出来上がりますが、行は動的に作れても動的な列表示はどうする？ってなります。上記TrainingBookにも
『動的なクロス集計レポートを生成できるこの機能には 1 つの大きな制限があります。列の最大数を事前に決定して、各列に対して計算フィールドを作成する必要があります。』と。運用場面は考慮してねと、静的クロス集計レポートですよと書いてあります。

列表示には繰り返しフィールドで対応

この解決策として列フィールドは繰り返しフィールドにするってのがあります。VL（ヴァーチャルリスト）は所詮データ表示用で元データそのものを扱っているわけではないですから、ハンドリングの良い繰り返しフィールドで代用するのが楽です。VLは素晴らしいのですが、計算フィールドを延々と作るのが面倒。
その点繰り返しフィールドならわずか一つのフィールドでGet(計算式繰り返し位置番号)関数で値を取り出すだけで延々と水平方向表示が可能。
今まで日陰者扱いだった繰り返しフィールドもVLの登場、そしてインデックスを扱うJSON関数の登場で活躍の場が一気に増えた気がします。

話を元に戻すと、今回の事例は、ほぼ決まった行名と列名を持つエクセルの表で時おり列が増える事がある事例への対処方法です。エクセル手打ちなら簡単に列を追加できますが、単純なVLではそうはいきません。そこを楽にできるようにしようというのが今回のネタです。

サンプルとして

伝票テーブル(伝票ID・日付・得意先ID)
明細テーブル(伝票ID・商品ID・納品数)
商品テーブル(商品ID・商品名）
得意先テーブル（得意先ID・得意先名）

というテーブル構成のオーソドックス伝票ががあるとします。
エクセルは行に納品先・列に商品名があり、それぞれほとんど変化なしの固定アイテムです。クロスする部分は単純に納品数合計。ただこのほとんど変化なしというのが問題で、月に1回程度で列の増減があるという事です。手打ちならわけないですね。そうなんです皆そんなのできて当たり前と思っています。

最初に手当てした方法は真っ当なVLで計算式で列の値を静的に作成する方法でした。あくまでも列の数は固定として。たまにあるイレギュラーはメールで追加で報告でもせいとしていたのですが、やっぱりスッキリしません。なんか負けた感を抱きます。

動的列表示にはCASE式をただひたすら重ねるだけ

行はいくらでも動的に取得できるのだから列もなんとかしたい。自分の知識では単純にCASE式を丸ごと動的に増減させるしかないかなと。それしか思いつかない。
ググる前にとりあえずこの方法でやってみました。

SELECT \"得意先\",
SUM (CASE \"商品ID\" WHEN 101 THEN \"納品数\" ELSE 0 END),
SUM (CASE \"商品ID\" WHEN 102 THEN \"納品数\" ELSE 0 END),
SUM (CASE \"商品ID\" WHEN 103 THEN \"納品数\" ELSE 0 END)
FROM　テーブル名

と連なっているこのWHENの値（商品ID）、当初はこの値つまり列の数を固定していましたが、動的に取得する場合、この商品IDの値を（101,102,103,...)と増減させながらLOOPで延々とCASE式を取得するだけで良いのかなと考えました。

列名フィールドの設定

まず
必要な商品IDのリストは繰り返しフィールド（グローバル）に入力するようにします。
商品名はこれもグローバルで、フィールドの計算式には以下のような式を設定

Let(
_list = List ( gr_商品ID );//繰り返しフィールドに入った商品IDリストをリスト化
ExecuteSQL(
"
SELECT \"商品名\"
FROM \"商品\"
WHERE \"商品_ID\" = ?
"
;"|" 
;"¶"
;GetValue ( _list;Get ( 計算式繰り返し位置番号 ) )
)
)

下図のように繰り返しフィールドに入った商品IDの場所に対応した商品名が取得できます。これを列名として表示させます。

スクリプト例

この商品IDの入る繰り返しフィールドの値の数が増減するわけなので以下のようなスクリプトでCASE式を動的に作成できます。
（話が逸れますが、Loopを抜けるExit Loopの条件ですが、このような改行区切りのデータの塊を扱う場合、ダルマ落とし式に処理が終わった行は次々に消去しながら繰り返し処理を進めていきます。データの塊が繰り返し処理が進むに連れて小さくなるので、処理が進むに連れて速度が上がります。少し冗長に見えますが、改行区切りのデータ塊を処理する際は効果的なので自分は少ないデータでもこのパターンを使うようにしています。海外板でよく目にして勧められただけですが。）

変数を設定[$itemList;値:List(gr_商品ID)]　　//繰り返しフィールドにある商品IDリスト
Loop
　　Exit Loop If [ Let( [
　　$item = GetValue ( $itemList;1 );
　　$itemList = RightValues ( $itemList ; ValueCount ( $itemList ) - 1 ) ];
　　IsEmpty ( $item )
　　)]

　　#case式の平文を作成 商品コードの数だけ改行区切りで作成していく（改行でなくても可）
　　変数を設定 [ $result; 値:"SUM(CASE b.\"kf_商品ID\" WHEN " & $item & " THEN b.\"納品数\" ELSE 0 END)," ]
　　変数を設定 [ $itemLists; 値:Case ( $itemLists;
　　$itemLists & ¶ & $result; $result
　　)]
End Loop

この$itemに商品IDを一つづつ放り込んでいけば商品IDリストの個数分のCASE式のリストができます。それを変数$itemListsに入れていきます。

"SUM(CASE b.\"kf_商品ID\" WHEN " & $item & " THEN b.\"納品数\" ELSE 0 END),"

気をつける点は上の$itemに入る変数の中身が数字ならこのまま& $item &でいいのですが、テキストならシングルクォート'& $item &'で囲みます。なのでここがテキストの場合は

"SUM(CASE b.\"kf_商品ID\" WHEN '" & $item & "' THEN b.\"納品数\" ELSE 0 END),"

となります。ダブルクオーテーションの内側に配置です。
このWHEN句の中のテキストはダブルでなくてシングルクォートで囲むってのは昔ドツボにはまりました。

最終的に$itemListsの中身は

SUM (CASE \"商品ID\" WHEN 101 THEN \"納品数\" ELSE 0 END),
SUM (CASE \"商品ID\" WHEN 102 THEN \"納品数\" ELSE 0 END),
・
・
・
SUM (CASE \"商品ID\" WHEN 120 THEN \"納品数\" ELSE 0 END),

となるはずですので、
最終行の最後のカンマは削除します。
（データビューワーで変数の中を覗くと文字エスケープのバックスラッシュはカットされています。）

#最後のカンマを削除
変数を設定 [ $itemLists; 値:Left ( $itemLists;Length ( $itemLists )-1 ) ]

次に
行名となる得意先IDリストを作成するには
直接得意先テーブルから取ってもいいのですが、得意先が特定の伝票上に存在するものだけを想定していますので、ここでは伝票テーブルから取得します。
UniqueValuesで単一になるようフィルタリングします。

変数を設定 [ $targetList; 値:UniqueValues ( ExecuteSQL(
"
SELECT \"kf_得意先ID\" FROM \"伝票\"
"
;"|"
;"¶"
)
)]

列と行のリストが確定したので最後にSQLで取得します。（JOINの部分は適当に読み飛ばしてください。ポイントはSELECTの部分の"& $itemLists &"だけです。ここではちゃんとダブルクォートで囲みます。）

Loop
　　Exit Loop If [ Let( [
　　$data = GetValue ( $targetList;1 );
　　$targetList = RightValues ( $targetList ; ValueCount ( $targetList )-1) ];
　　IsEmpty ( $data )
　　)]

#select文のcase式リストを含んだ変数$itemListsを""で囲んで挿入
　　変数を設定 [ $sql; 値:ExecuteSQL( "
　　SELECT c.\"得意先名\","& $itemLists &"
　　FROM \"伝票\" AS a
　　JOIN \"明細\" AS b ON a.\"伝票_ID\" = b.\"kf_伝票_ID\" 
　　JOIN \"得意先\" AS c ON a.\"kf_得意先ID\" = c.\"得意先_ID\" 
　　WHERE a.\"kf_得意先ID\" = ?
　　GROUP BY c.\"得意先名\"
　　"
　　;"|"
　　;"¶"
　　;$data
　　)]
#$sqlが空でなかったらリストに追加
　　If [ $sql ]
　　　　変数を設定 [ $$sqlResults; 値:Case ( $$sqlResults; $$sqlResults & ¶ & $sql;
　　　　$sql
　　　　)]
　　End If 
End Loop

としてやればCASE式の個数分の列が作成できます。
もちろん繰り返しフィールドは余裕を持った回数を確保しておきます。

最終的に得た変数$$sqlResultsをVLテーブルから参照すると完成しているはずです。

VLテーブルのフィールド構成は

と僅か6つです。（商品IDを入力するグローバルの繰り返しフィールドは別テーブルに設定しています。）

こうしてグローバルフィールドの商品IDを入力する繰り返しフィールド上にスクリプトトリガー(onObjectSave)で上記一連のスクリプトを仕込めば、商品IDを打ち込むだけで各セルの値も動的（擬似ですが）に変化するクロス集計表が完成しました。なんか思ったよりすんなりできて拍子抜けしたのですが。

商品IDを打ち込んでいく度に列の値が全て計算されて動的に右に伸びていきます。

上の画像を動画にしたのが最初にも貼った下記リンクです。再度貼っておきます。
Youtube：FileMakerでクロス集計動画

これで列の商品名を変更する場面があっても、必要な商品IDを追加・削除する事で瞬時に各セルの値が変化するクロス集計表が完成です。
見た目も皆が安心するエクセルライクなものになりました。列は繰り返しフィールドなので計算フィールドを追加しなくてもいくらでも横に伸ばせます。

ちなみに繰り返しフィールドの色分けは条件付き書式で。値が空欄の場合は塗りつぶし色はナシで。

また、行データとしての空白レコードの表示を隠すには、次の場合にオブジェクトを隠すでそれぞれのフィールドに対してIsEmpty ( VL::row_data )を指定、もしくは単に対象レコードの絞り込みでrow_data = @を指定すれば空白レコードは隠れます。

もっとスムーズなやり方があるのかもしれませんが、ググる前に仮説を立ててやってみたら、意外と少ない手数でやれたのでネタとしました。
（正直ググり出すとそれでかなりの時間が潰れてしまう、なかなか本質にたどり着けないってのが本音です）

注)ここでは速度は考慮していません。ローカル環境だと感じませんが、ホスト上のファイルを直接操作する場合は速度が気になる場面もあるはずです。SQLのWHERE句で対象を絞り込めれば高速ですが、それでもある程度のボリュームがある場合は都度サーバーサイドに投げて計算した方がいいです。