Docurain Labo

Docurainサービス開発日記

明細行を集計して合計金額を算出する方法を理解する

帳票はヘッダーとフッター、そして明細行の組み合わせで作成されます。それぞれ、次のような区別があるでしょう。

  • ヘッダー
    帳票の上の部分に掲載される情報。全ページまたは最初のページだけに表示される。
  • フッター
    帳票の下の部分に掲載される情報。全ページまたは最後のページだけに表示される。
  • 明細
    購入した商品情報など、帳票の中で動的に繰り返される情報。帳票によって数が異なる。

この時、多くの帳票では明細行に記載された金額を集計して、ヘッダーやフッターに表示します。明細行は帳票によって行数が異なるので、増減に合わせて計算範囲が変わります。さらに言えば、帳票が単ページ、または複数ページによっても変わります。

今回はDocurainにおける明細行の集計方法をいくつかのパターンに分けて紹介します。

帳票について

今回は分かりやすくするためにヘッダー、フッターに数字以外の情報は載せていません。

f:id:moongift:20211222184153p:plain

データについて

データは次のようなJSONです。明細ごとに単価、数量が記載されています。項目数は可変です。

{
  "items" :  [
    {
      "note":  "商品1",
      "unit": 100,
      "amount": 5
    },
    {
      "note":  "商品2",
      "unit": 500,
      "amount": 3
    },
    {
      "note":  "商品3",
      "unit": 1500,
      "amount": 10
    },
  :
  ]
}

1ページの場合

f:id:moongift:20211222183350p:plain

帳票が必ず1ページの場合 #EASY_SHIFT_FORMULA を指定するのが最も簡単です。A1セルで次のように記述します。

#EASY_SHIFT_FORMULA
#set($e = $ROOT) ## 変数用

#EASY_SHIFT_FORMULA を記述しておけば、数式の簡易自動シフトモードになり、自動で計算範囲を調整してくれるようになります。そして、明細部分を次のように記述します。

A B C D E
10 詳細 単価 数量 金額
11 #foreach($line in $e.items)
12 ${line.note} ${line.unit} ${line.amount} =D12*E12
13 #end

さらにフッター部分の集計では次のように記述します。10%はExcelのパーセント表示を使っていますので、実際には0.1になります。

A B C
14 小計 =SUM(E12:E12)
15 税率 10%
16 集計 =C14 + C14*F15

この状態で帳票を作成すると、明細部分では行ごとにD12(またはE)がD13やD14に自動的にシフトします。これが簡易自動シフトモードです。フッターについても =SUM(F12:F12)=SUM(F10:F12) へ自動的に変更されます(明細が3つの場合)。もちろん、最後の計算についても =F13+F13*F14 となり、正しい計算結果が得られます。

f:id:moongift:20211222183650p:plain

複数ページの場合

複数ページの帳票(改ページがある帳票)を作成する場合には数式の簡易自動シフトモードは利用できません。範囲がページをまたぐので、どの範囲をシフトすれば良いのか自動では定義できないからです。そこで利用するのがスコープになります。

スコープの使い方

スコープはまず #scope("全て")#end の形で定義します。"全て" は自分で定義したものを利用できます。このスコープで区切られた範囲について、数式がシフトしたり、集計を行えるようになります。スコープは複数用いてネストさせられます。

帳票の作り方

では帳票を作ってみましょう。今回は1ページあたり10明細とします。そこでデータを10明細ごとに分割します。

#set($e = $ROOT)
#set($pages = $e.items.chunk(10))

例えば items が5行分のデータを持っていたとして、chunk(3) と指定すると、 $pages は次のようなデータになります。

[
    {
        items: [
        {"note":  "商品1", "unit": 100, "amount": 5},
        {"note":  "商品2", "unit": 200, "amount": 3},
        {"note":  "商品3", "unit": 300, "amount": 1}
        ]
    },
    {
        items: [
        {"note":  "商品4", "unit": 400, "amount": 5},
        {"note":  "商品5", "unit": 500, "amount": 3}
        ]
    }
]

そして、この $pages を繰り返し処理しますが、この時、全体に対してscopeを定義します。

#scope("全て")
  #foreach($page in $pages)
      : 省略
    #end
#end

そして、明細行を記述します。単ページの場合は $e.items でしたが、今度は $pages ごとに処理します。

A B C D E
14 詳細 単価 数量 金額
15 #foreach($line in $page)
16 ${line.note} ${line.unit} ${line.amount} =D16*E16
17 #end

フッター行では DR.SHIFT_FORMULA を使います。これはDocurain独自の仕組みで、スコープに合わせた計算式のシフトを行うための記述です。この時のスコープとして、先ほど定義した名前を指定します。

A B C
18 小計 =DR.SHIFT_FORMULA(SUM(E16),"scope=全て, target=all")
19 税率 10%
20 集計 =C19+C19*C20

targetall または nearest を指定します。 nearest はスコープ内の最近傍範囲になります。デフォルトは nearest です。

f:id:moongift:20211222183821p:plain

生成される帳票

明細は単ページの時と同じく計算されます。そして、フッターでは次のように変換が行われます(一例です)。

=SUM(F10:F19,F28:F33)

f:id:moongift:20211222183849p:plain

ページごとの計算対象範囲を自動的に判別し、SUM関数に複数の範囲を指定してくれます。これによって正しい集計結果が得られます。

ページごとの集計を出したい場合

帳票全体の集計もありつつ、ページ単位でも小計を出したい場合はスコープを複数定義します。スコープ名はページごとにユニークなものになっていなければならないので、#scope("ページ-$foreach.count") のように変数を使って定義します。

A B C D E
13 詳細 単価 数量 金額
14 #scope("ページ-$foreach.count")
15 #foreach($line in $page)
16 ${line.note} ${line.unit} ${line.amount} =D16*E16
17 #end
18 #end

そしてページ計を下に記述します。この時もスコープの範囲について、変数を用いて指定します。

A B C
19 ページ計 =DR.SHIFT_FORMULA(SUM(E16),"scope=ページ-$foreach.count, target=all")

このようにスコープを2つ使うことで、ページ内での集計と帳票全体での集計を同時に行えます。

f:id:moongift:20211222183943p:plain

まとめ

単ページの場合は数式の簡易自動シフトモードで解決できるでしょう。複数ページの場合にはスコープとDR.SHIFT_FORMULAを利用してください。スコープを使いこなせば、複雑な帳票も作成できるようになるでしょう。