【クエリビルダ】プロンプトフィルターを使いこなす

はじめに

Sharperlightクエリビルダのフィルターの機能のひとつプロンプトフィルターの使い方を紹介してみようと思います。
本題に入る前にSharperlight Excelアドインとクエリビルダのちょっと解説。
SAP B1データモデルを利用して説明します。
Microsoft Excelを起動して、Sharperlightアドインを利用します。（ご利用の端末にMicorosoft OfficeおよびSharperlightがインストールされていることが前提です）
Sharperlightクエリ式を設定したいセルを選択してから、Sharperlightリボンのテーブルアイコンをクリックします。テーブルを作成するクエリを定義するためです。
このようなUIが表示されます。Sharperlightクエリビルダです。Sharperlightのどの製品においてもクエリを作成する場合は、このUIを利用します。
大きく分けて3つの領域に分かれています。

• 上部: フィルターを指定する領域
• 左下部: 選択したテーブルに属するフィールドやJOINの一覧を表示する領域
• 右下部: 出力フィールドを指定する領域

また、SQL文が生成されるイメージではこのようになります。

通常動作の確認

先ずプロンプトフィルターを使用しない極普通のフィルターの使用を説明します。
今回の例では既に４つの必須フィルターが設定されてます。これはSharperlight SAP B1データモデルの作成時に、このテーブルが選択された場合、これらのフィールドを必須フィルターとして使用しますよ って定義しているからです。
では早速、CanceledフィルターにNを設定します。出力には以下のフィールドを選択リストより選び設定します。

OKボタンでクエリ結果をWorksheet上の表示します。

フィルターで指定したCanceled？ = Nが正常に動作していますね。
Sharperlightエンジンで生成されたSQL文を見てみましょうか。
生成されたSQL文を見るためには、もう一度クエリを開いてプレビュー機能を使用します。
Sharperlight Table Formulaが作成されたセルをダブルクリックし、クエリ定義を開きます。
プレビューボタンを押して、クエリを試行します。
クエリ結果がグリッド表示されます。グリッド上部の情報アイコンからSQL Statementを選択し生成されたSQL文を表示します。

このSQL文が、SharperlightエンジンによりSAP B1データモデルを利用して動的に生成されました。

プロンプトフィルターでフィルターを無効化

上記では簡単なフィルターの使用を見てきましたが、クエリビルダで複雑なフィルター定義を作成する場合など、何度もプレビューで結果を確認しながら作成を行います。
そういった中で、このフィルターを外してプレビューしてみたいけど、このフィルター定義を作成するのに苦労したなあ、だから消したくないなあなんていうことがあります。そういった場合にプロンプトフィルターオプションを利用し一時的に無効化すること、つまりWHERE句から除外することができます。
では早速例を用いて見ていきます。
はじめにCustomerCodeフィルターにC20000を設定し、プレビューします。当然Customer CodeがC20000のレコードしか戻ってきません。


SQL文もこのようになっています。

ここでこのCustomerCodeフィルターを、プロンプトフィルターオプションを使用して一時的に外したいみたいと思います。
CustomerCodeフィルターのこの部分をダブルクリックし、Filter Optionsを開きます。

プロンプト専用フィルターチェックボックスにチェックを入れます。これだけです。

OKボタンでFilter Optionsを閉じます。背景色が薄黄色になりました。

プレビューボタンで結果を見てみます。
CustomerCodeフィルターにC20000を設定しいるにも関わらず、プロンプト専用フィルターがオンになっているので、CustomerCodeフィルターはWHERE句から除外され、C20000以外のレコードも戻ってきました。

SQL文もチェックしてみましょう。CustomerCodeフィルターに関するコードはWHERE句には存在していません。

でもクエリ定義にはちゃんと存在しています。

クエリへのパラメータとしての使用

このようなプロンプトフィルターの性質を利用して、パラメータ的な使用方法もあるので紹介してみようと思います。
Sharperlightのクエリにはフィルタ値を、例えばサブクエリに渡すことができる仕様になっています。
その場合では、親クエリのフィルターから値を渡し、子クエリのフィルターで受けるという形になります。
そういった時に通常では、親クエリのフィルターから受けた値が、子クエリのフィルターで動作してしまいます。
動作しても良い場合はそれでいいのですが、動作して欲しくない場合、例えば受けた値をその他のフィルターに渡したいなんて言う場合は困ります。そこで、プロンプトフィルターオプションを利用して実現することができます。
先ほどのクエリを編集します。
CustomerCodeフィルターのプロンプト専用フィルタープロンプト専用フィルターチェックボックスのチェックを外します。通常のフィルターに戻ります。
今度は出力領域で右クリックメニューを開き、サブクエリーを追加を選びます。

Sub Query用のクエリビルダが開きます。

実在するフィールドを使用する場合

選択リストよりCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
次にフィルター値入力フィールド横の下矢印アイコンをクリック、親クエリのCustomerCodeフィルターの名前を検索し選択します。こうすることで親クエリのCustomerCodeフィルター値が、このクエリのCustomerCodeフィルターに渡されます。

次に、CustomerCodeフィルターの参照名をSubQCusCodとして親クエリのCustomerCodeフィルター参照名と区別します。参照名の変更も右クリックメニューから行えます。

最後にこのフィルターをプロンプト専用フィルターとします。

もう一度、選択リストからCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
そして上記プロンプト専用フィルターであるCustomerCodeフィルターの参照名を設定します。

つまり親クエリからのフィルター値を、子クエリではプロンプト専用フィルターのCustomerCodeフィルターで受け、それをそのまま実働CustomerCodeフィルターへ単に渡すという仕組みです。
実用的ではありませんが、イメージが伝わればいいかなと思います。

パラメータ用に専用フィールドを用意する場合

Text型のフィールドを専用フィルターとして新規追加します。
フィルター領域の右クリックメニューを開き、その他 -> テキストを選択します。

テキストフィールドがフィルターとして追加されます。摘要をパラメータ1、参照名をP1とします。
このようにして追加されたフィルターは、デフォルトでプロンプトフィルターとなります。

値欄に親クエリのCustomerCodeフィルターの参照名を指定します。

選択リストからCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
そしてパラメータ1フィルターの参照名P1を、CustomerCodeフィルターの値欄に設定します。

これで親から子への値の受け渡しが行えます。

おわりに

今回はあまり実用的ではない単純な例で紹介してきましたが、イメージは伝わりましたか？
複雑なレポートを作成する場合に使える場面が多々出てくると思います。
試してみてくださいね。