by 前回の記事が参照していた、バーチャルリストによるレポート生成の詳細を解説する2回シリーズを順次ご紹介していきます。
Virtual List Reporting, part 1
(元記事はこちら)
Kevin Frank
2016/4/27
警告：読者が過去のバーチャルリストの記事を何度も参照するのを避けるため、本記事に過去の記事の内容が統合されていることをあらかじめお断りしておきます。
今回の記事は、バーチャルリストの技術(正確にはいくつかの技術の組み合わせ)を使ってレポートを生成する方法を紹介する、2回シリーズの1回目です。
デモファイル： VLR-part-1.zip
Bruce Robertsonによって発明され、広く知られることになったバーチャルリストは、とても柔軟性が高く、このブログでも以下にあげるように過去に何回か取り上げています。
- Long Documents in FileMaker 11
- Virtual List Charts, part 1 & part 2
- Summary List + Virtual List
- Conditional Subsummary Report in Browse Mode
基本的な考え方
バーチャルリストを知らない方向けに簡単にまとめると、バーチャルリストを使ったソリューションでは、ファイル内に特別な機能を持たせたテーブルを作り、必要になるよりも多めのレコードを予め作成しておきます(今回のデモでは1万件)。このテーブルのレコードは、ある形式の配列からデータを読み込むことで「仮想的に」作られます。通常その配列は、1つまたは複数の変数に入っています。
今回の実装
バーチャルリストによるレポート生成(virtual list reporting; 以下、VLR)を実装する方法はいくつもあります。今回は、私が最近使ってとてもうまくいったある方法に焦点をしぼって紹介します。具体的には、Multi-Findの手法を使って、販売データとWebアクセスのデータから値を集計して、ローカル変数([]によって繰り返し回数が設定されている)にそのデータを入れます。この変数は、バーチャルリストテーブルに設定された非保存の計算フィールドによって解析されます。このときにテーブルの「列」は個別に名前が付けられた$ローカル変数に対応し、「行」は$ローカル変数の[]内の番号に対応します。
すでにバーチャルリストの手法をご存知の方は、上の文章をある程度理解することができるでしょう。もしそうではなくても心配はいりません。サンプルを詳しく見ていくうちに、徐々に理解できるようになるでしょう。
なぜわざわざ？
あなたがそう思うのは自然なことです。一見VLRは、あなたがすでに知っている標準的なFileMakerのレポート機能でできることを、わざわざ複雑な方法で行おうとしているように見えるかもしれません。基本的なレポート作成についてなら、私も同意します。
しかし要件がより困難になってくると、通常であれば複数の補助的なフィールドやリレーションシップを追加で作成しなければいけないところを、VLRを使うことによって、テーブル定義を触ったりリレーションシップグラフに変更を加えることを一切せずに、レポートを生成できるようになります。
バーチャルリストテーブルを1つ作っておけば、力仕事は基本的にはすべてスクリプトレベルで行われます。もちろん、レイアウトレベルでの操作も必要ですが、それはFileMakerの従来のレポート作成の場合と同じです。実際にはVLRのレイアウトは、従来の方法で作成するレポートのレイアウトよりも簡単になります。
つまり、先回って予め少し複雑なしくみを用意しておくことで、後々いろいろなことがはかどります。ではデモファイルの構造を見てみましょう。その後、レポートのサンプルを詳しく見ていきます。
注：売上値はデモファイルの起動時に更新されるので、実際に表示される値はここで示すスクリーンショットとは一致しません。
デモ
デモは、2つの主要なデータテーブル(sales[販売]とweb visits[Webアクセス])、バーチャルリスト(virtual_list)、その他の補助的なテーブルで構成されています。
主要なデータテーブルは、ほとんど単純なデータだけで、レポート生成のための機能的なフィールドといえば、salesの場合は集計フィールドが一つあるだけです。
web visitsも同じで集計フィールドが一つあるだけです。
バーチャルリストのテーブルも、ミニマリズムの見本のようです。
テーブルビューで表示すればトラブルシューティングが楽になります。
最後はリレーションシップグラフですが、これも極めてシンプルです。
レポート1
まず最初は、シンプルなクロス集計レポートです。以下のような内容ですが…
…これをどう処理すれば、以下の集計結果にたどりつけるでしょうか?
前述したように、力仕事の大部分はレポート作成のスクリプトが行います。最初の部分は基本的な準備といくつかの$ローカル変数の初期化です。
その中の$listYと$listTには以下のような値が入ります($listTの最初の行が意図的に空になっていることに注意してください)。
次にスクリプトは二つのリストをループして、ローカル変数$col_x[rep]に値を入れます。ここでxは列番号を表し、[rep]は外側のループが何巡目かに対応します。例えばデモを2016年に実行した場合、$listYには値が8個あるので1から8が入ります。
この時点で、スクリプトは大量の$col_x[rep]に値を設定しています。
これらがバーチャルリストテーブルによって分析されて、非保存の計算フィールドcell_num_rに入ります。
cell_num_rは、Get( 計算式繰り返し位置番号 )の結果を前述の$col_xのx部分(列番号)に変換し、テーブルの各行のIDを[rep]部分に対応させて、$col_x[rep]からデータを抽出します。Get( 計算式繰り返し位置番号 )によって、計算式が自分が今何個目の繰り返しにいるかを知ることができるので、列への変換が可能になります。
計算式の中でIDの後ろに[1]がついていますが、これは計算の[rep]の部分が確実に非繰り返しのフィールド「ID」を参照するようにするためです。別の方法として、IDをextend関数で囲む方法もありますが、私は配列表記([n])の方を好んで使っています。
具体的な例として、上の図の8行目のcell_num_rの2個目の繰り返しでは、まず$col_2[8]という変数名が組み立てられ、それが評価されて1072493.34が返されます。
スクリプトの残りの部分は、もっぱらレポートの表示処理を行っています。
この図はレポートをレイアウトモードで表示しています。cell_num_rの繰り返しの1から5を左から順に配置し、繰り返しの2から5は読みやすいよう書式で桁数を揃えています。
最初に示したように、これが最終的な結果です。
レポート生成スクリプトは、途中一時停止するよう設定されていて、データビューアで$変数の中身を確認できます。「続行」ボタンを押して、次のサンプルに進んでください。
かなり長い説明になってしまいましたが、ここまでで全体に関わる共通の基本部分についてもカバーしました。この後は、レポート2から6のそれぞれのポイントに焦点を当てて説明していきます。
レポート2
これは年/月によるsales(販売量)のクロス集計レポートです。
レイアウトモードではこうなっています。レポート3でも同じレイアウトを使うので、決め打ちのテキストラベルの代わりに2箇所にマージ変数が使われています。ボディパートにはcell_num_rの繰り返しの1から14が配置されています。
後部総計に配置されている合計値は、対応する150個の繰り返しを集計する総計フィールドから来ていて、次のように定義されています。(総計フィールドで繰り返し数を指定する必要はなく、cell_num_rから自動的に引き継がれます。)
レポート1用のスクリプトは意図的に理解しやすいように書かれています。あえてカスタム関数を使わず、変数も静的な宣言だけを使っています。今回のレポート生成スクリプトの残りの部分では、2つのカスタム関数を使って効率を向上させます。
まずカスタム関数CustomListを使って、スクリプトの以下の部分を…
次のように書き換えることができます。
CustomListは、月の数字を生成するのも簡略化してくれます。
(これらはこの優れたカスタム関数ができることの一番シンプルな例です。基本的にカスタム関数は使わないという方も、この関数は例外として使用することをお勧めします。)
二つ目のカスタム関数SetVarByNameは、$col_3から$col_14まで(具体的には1月から12月まで)の生成と値の設定を簡略化します。ここではこのカスタム関数を使うことのメリットはそれほどないかもしれませんが、レポート4,5,6でより大きな役目を果たします。
最後に、カスタム用紙サイズのトリックを使ってレポートのレイアウトの横幅を稼いでいる点にも注意してください。
レポート3
前述したように、このレポートではレポート2と同じレイアウトを使っていますが、今回はsalesではなくweb visitsを集計しています。
これはVLRの長所をよく表しています。というのも、レポート2をすでに作ってあるので、レポート3のためにはスクリプトを複製し、黄色のハイライト部分を修正し、緑色のハイライト部分を追加しただけで、(文字通り)5分で完成してしまいました。
レポート4
これはレポート1の拡張版で、販売注文数と割合が追加されています。
カスタム関数SetVarByNameが効果的に使われています。
レポート5
ここではレポート4をさらに一歩進めて、後部総計に今日の日付から遡って3年間の平均値を追加しています。
ここでもVLRを利用していることによって、スクリプトに一部追加するだけで目的が達せられます。リレーションシップグラフやテーブル定義に修正を加える必要はありません。
スクリプトの63行目で、$cNumを22に設定しています。これはVLRの「奥の手」として、$変数の高い値の部分を利用するトリックです。レポートレイアウトはボディパートでcell_num_rの繰り返しの1から12を表示し、同じフィールド(集計フィールドではない)の繰り返しの22から32を後部総計に表示します。
レポートを生成する前にバーチャルリストテーブルを表示させて、以下のように右方向にスクロールすると、レポート生成中に8行目に図のような数字が見えます。
前述したように、ダウンロードしたデモファイルで表示されるデータは、ここで紹介している画面のものとは一致しません。salesのデータはファイルの起動時に自動更新しています。またデモを2016年に実行した場合、行は8行目になりますが、それ以外では行数はそれより高くなります。(もし低かった場合は…あなたのタイムトラベルの秘密を私に教えてください。)
レポート6
今回の最後の報告書は、レポートの4と5をさらに改良し、右端に列を2つ追加して、Webアクセス数(web visits)と、そのうち購入に至った割合を表示しています。
標準的なFileMakerのレポートは通常salesのようなデータテーブルをベースにして作成されます。それに対してバーチャルリストのテーブルは、元のデータがどのテーブルにあるかを気にする必要がありません。非関連のテーブルを自由に結合させることができます。スクリプトを使って$ローカル変数にデータを入れさえすれば、あとはバーチャルリストが勝手に処理してくれます。
まとめ
言うまでもないことですが、ここで紹介した方法が唯一の正しい方法というわけではありません。ここまでを読んで理解できた方であれば、おそらく自身のソリューションで使用するために、ここで紹介したアイデアを活用したり、改良したりする方法をすでに思いついていることでしょう。$ローカル変数ではなく$$グローバル変数を使う方法もあれば、1つの2次元の配列を変数($あるいは$$)に入れて、そこからデータを取り出すという方法もあります。これらのアプローチは、過去のバーチャルリストの記事で取り上げられていて、それぞれに長所・短所があります。
今回はバーチャルリストによるレポート生成でできることのほんの一部を紹介しました。次回パート2でこのテーマをさらに掘り下げます。