はじめに
私たちはSAPデータモデルを使用して、州単位の顧客別シートを毎月作成し、それをファイルシステムに保存したいと考えています。
- データモデル: SAP Business One
- データベース: SAP Business One デモデータベース (Microsoft SQL データベース)
目的
テンプレートであるExcelワークブックを異なるパラメータで再計算し、実行日と州名をファイル名に含む新しいExcelワークブックとして作成します。
- テンプレートワークブック
- 生成されたワークブック
実装
1. テンプレートワークブックの準備
分割のための基本クエリを準備する必要があります。
このクエリは、A/R 売上請求書テーブルからすべての顧客の要約情報を返します。
- セル D3 にテーブル式が定義されています
- クエリビルダーによるテーブル式の定義
- フィルター: BP Typeは顧客のみのため C である必要があります
- フィルター: Customer Nameはセル B3 を参照し、分割のための重要なフィルターです
- フィルター: Document Dateはセル B4 を参照します
- フィルター: Stateはセル B5 を参照します
a. 顧客リスト
別のシートに、Sharperlightのテーブル式を使用して顧客リストを準備しました。
- セル C3 にテーブル式が定義されています
- クエリビルダーによるテーブル式の定義
- フィルター: Stateはセル B2 を参照しています。最終的にはセル B2 の値は、スケジュールされたタスクから渡される値により動的に変化します
- 年/月の値がセル B1 にスケジュールされたタスクから渡されます
さらに、Templateシートのセル B4 に =Filters!B1 を、セル B5 に =Filters!B2 を入力しました。
b. スプリット公式
セル A1 にスプリット公式を定義します。式は以下の形式になります。
=@mdSplitSheet("By Customer",AR_Sales_Invoice_Customer_Name[List of Customers],"",$B$3,"Green",0)
第1パラメータ
スプリッ公式の名前を指定します。
第2パラメータ
顧客のリスト。この値を使用して基になるシートが分割されます。別のシートにリストを準備し、式に記載します。
第3パラメータ
シート名のリスト。各スプリットシートの名前を指定できます。空白のままにすると、フィルタ値が使用され、この場合は顧客名が使用されます。
第4パラメータ
顧客名リストから各顧客名が渡される場所を指定します。
第5パラメータ
分割シートの色を指定します。
第6パラメータ
スプリット公式はデフォルトで親ワークシートの直系の子として抽出され、階層レベルは0です。レベルを1に設定すると、スプリットシートの式は子から抽出され、レベル2では孫からのみ抽出されます。任意のレベル番号をスプリットシートの式に追加できますが、その階層レベルに達したときのみ抽出されます。
c. テスト
定義が完了したので、式を実行してみます。
Filtersシートのセル B2 に QLD を入力し、ここで ワークシート - 再計算 アイコンをクリックして式を実行します。
顧客ごとに分割ワークシートが作成されます。
式が正常に動作することを確認したら、分割ワークシートを削除してテンプレートとして使用する必要があります。
ツリーアイコンをクリックしてツリーペインを開き、それを使ってワークシートを削除できます。
分割シートがぶら下がっているノードを選択し、削除アイコンをクリックすることで、そのノードを削除できます。
分割シートを削除したら、テンプレートワークブックを保存します。
2. 毎月、州単位で実行されるタスクの定義
テンプレートワークシートを再計算し、Report_{State}_{YearMonth}.xlsx という名前で指定されたフォルダーに保存するスケジュールされたタスクを作成します。
このタスクは毎月実行され、州および年/月の値がテンプレートワークブック内のクエリに渡されます。
a. スケジュールされたタスク
Sharperlightアプリケーションメニューからスケジューラを開きます。
タスクは毎月実行され、ワークブックは州ごとに再計算されます。
a.1 クエリの組み合わせ追加とクエリの組み合わせ終了
クエリの組み合わせとクエリの組み合わせ終了を選択します。これにより、州ごとにワークブックの再計算を繰り返し実行することができます。
クエリの組み合わせにはクエリが必要で、州のリストを返す必要があります。
クエリ定義
プレビュー結果
クエリから返された値は、その名前で参照することができます。
a.2 州ごとのExcelワークブックの再計算
クエリの組み合わせとクエリの組み合わせ終了の間にExcelワークブックアクションを追加します。
Excelワークブックは、Sharperlightで指定されたセキュアフォルダー内に存在する必要があります。これは、Sharperlightのサイトセットアップで行うことができます。
上記のケースでは、C:\Temp\、C:\QiitaSampleTemp\ および C:\QiitaSampleCopy\ フォルダがSharperlightからアクセス可能です。
テンプレートExcelワークブックは、これらのセキュアフォルダーのいずれかに配置しておく必要があります。
テンプレートワークブックの指定
話をスケジューラのタスクに戻します。
タスクに使用するテンプレートExcelワークブックを指定します。
パラメータの設定
テンプレートExcelワークブックに2つのパラメータを渡したいと思います。1つは {年/月} の値、もう1つは州です。ここで指定できます。
{年/月} の値はFiltersシートのセル B1 に渡されます。
州はFiltersシートのセル B2 に渡されます。
これは、以下の構文で実現できます。
最初のものは、Sharperlight式によって生成された現在の年/月をFiltersシートのセル B1 に渡しています。
2つ目は、クエリの組み合わせで生成された州の値を渡しています。
結果の保存
ワークブックを再計算した後、各州ごとにExcelワークブックとして保存します。
ここで指定することができます。
C:\QiitaSampleTemp\Document_{_Date}_{%State}.xlsx
その他のオプション
このオプションを有効にすると、結果が保存される際にすべてのSharperlight式が削除されます。
a.3 トリガーの追加
トリガータブでトリガーを設定します。SharperlightとMicrosoft Excelが同じPC上にある場合、Windowsスケジューラーよりもサービスタイマー（Sharperlightサービス）を使用する方が、設定が簡単です。
あとがき
これは、Sharperlightのスケジュールされたタスクを使用したオフィス自動化の簡単な例です。ただし、Microsoft Officeは自動化のために十分に設計されていないことが多いため、この種の自動化の設定は非常に難しい場合があります。そのため、いくつかの問題を経験しました。それに関するソリューションやワークアラウンドについては、別の投稿で紹介したいと思います。