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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/23898f7c-324b-d98a-75bb-3e20ff0558b9.png)
また、SQL文が生成されるイメージではこのようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/192293d1-d7d1-87c5-1d75-ef5964454dbc.png)
## 通常動作の確認 ##
先ずプロンプトフィルターを使用しない極普通のフィルターの使用を説明します。
今回の例では既に４つの必須フィルターが設定されてます。これはSharperlight SAP B1データモデルの作成時に、**このテーブルが選択された場合、これらのフィールドを必須フィルターとして使用しますよ** って定義しているからです。
では早速、**Canceled**フィルターに**N**を設定します。出力には以下のフィールドを選択リストより選び設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/70d78159-ecd3-34fe-c02a-51ee90e3e5c9.png)
`OK`ボタンでクエリ結果をWorksheet上の表示します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5aaa737a-4d07-077c-7efa-8451e8f61e4b.png)
フィルターで指定した`Canceled？ = N`が正常に動作していますね。
Sharperlightエンジンで生成されたSQL文を見てみましょうか。
生成されたSQL文を見るためには、もう一度クエリを開いてプレビュー機能を使用します。
Sharperlight Table Formulaが作成されたセルをダブルクリックし、クエリ定義を開きます。
`プレビュー`ボタンを押して、クエリを試行します。
クエリ結果がグリッド表示されます。グリッド上部の情報アイコンから`SQL Statement`を選択し生成されたSQL文を表示します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4aea6b76-a420-f05f-0686-450f500db0a8.png)
このSQL文が、SharperlightエンジンによりSAP B1データモデルを利用して動的に生成されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/820d6383-e233-4dc4-1b6e-5049d4be1561.png)
## プロンプトフィルターでフィルターを無効化 ##
上記では簡単なフィルターの使用を見てきましたが、クエリビルダで複雑なフィルター定義を作成する場合など、何度もプレビューで結果を確認しながら作成を行います。
そういった中で、**このフィルターを外してプレビューしてみたいけど、このフィルター定義を作成するのに苦労したなあ、だから消したくないなあ**なんていうことがあります。そういった場合にプロンプトフィルターオプションを利用し一時的に無効化すること、つまりWHERE句から除外することができます。
では早速例を用いて見ていきます。
はじめに**CustomerCode**フィルターに**C20000**を設定し、プレビューします。当然Customer CodeがC20000のレコードしか戻ってきません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d36f9e80-be8a-8185-839c-f607188d7bfa.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e9c40d5c-560b-ccb7-5e20-2fdc33ec25b4.png)
SQL文もこのようになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57a20397-41ca-5339-7599-d7c9090edf8a.png)
ここでこの**CustomerCode**フィルターを、プロンプトフィルターオプションを使用して一時的に外したいみたいと思います。
CustomerCodeフィルターのこの部分をダブルクリックし、Filter Optionsを開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2c8680ef-8146-6c9f-04d7-b8e4bb4f9443.png)
`プロンプト専用フィルター`チェックボックスにチェックを入れます。これだけです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a4153310-41d3-6a3e-60b4-c49e644c54d4.png)
`OK`ボタンでFilter Optionsを閉じます。背景色が薄黄色になりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4dd9d4e7-b468-804b-8399-9c013835229c.png)
`プレビュー`ボタンで結果を見てみます。
**CustomerCode**フィルターに**C20000**を設定しいるにも関わらず、`プロンプト専用フィルター`がオンになっているので、**CustomerCode**フィルターはWHERE句から除外され、**C20000**以外のレコードも戻ってきました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7ef5d9b-0878-ef08-1666-b1c930ca0ac3.png)
SQL文もチェックしてみましょう。**CustomerCode**フィルターに関するコードはWHERE句には存在していません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8718c8c2-79d7-9e16-a8ba-52534936d465.png)
でもクエリ定義にはちゃんと存在しています。
## クエリへのパラメータとしての使用 ##
このようなプロンプトフィルターの性質を利用して、パラメータ的な使用方法もあるので紹介してみようと思います。
Sharperlightのクエリにはフィルタ値を、例えばサブクエリに渡すことができる仕様になっています。
その場合では、親クエリのフィルターから値を渡し、子クエリのフィルターで受けるという形になります。
そういった時に通常では、親クエリのフィルターから受けた値が、子クエリのフィルターで動作してしまいます。
動作しても良い場合はそれでいいのですが、動作して欲しくない場合、例えば受けた値をその他のフィルターに渡したいなんて言う場合は困ります。そこで、プロンプトフィルターオプションを利用して実現することができます。
先ほどのクエリを編集します。
**CustomerCode**フィルターの`プロンプト専用フィルタープロンプト専用フィルター`チェックボックスのチェックを外します。通常のフィルターに戻ります。
今度は出力領域で右クリックメニューを開き、**サブクエリーを追加**を選びます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/588fdc60-d901-ba9e-e27c-7c4c1562d6f8.png)
Sub Query用のクエリビルダが開きます。
### 実在するフィールドを使用する場合 ###
選択リストよりCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
次にフィルター値入力フィールド横の下矢印アイコンをクリック、親クエリのCustomerCodeフィルターの名前を検索し選択します。こうすることで親クエリのCustomerCodeフィルター値が、このクエリのCustomerCodeフィルターに渡されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fc6a911e-dc7d-aafd-9aa7-2331e27660f1.png)
次に、CustomerCodeフィルターの参照名を**SubQCusCod**として親クエリのCustomerCodeフィルター参照名と区別します。参照名の変更も右クリックメニューから行えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cab7cfff-983a-d575-d519-6fd38f0faae4.png)
最後にこのフィルターを`プロンプト専用フィルター`とします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c1ef4b59-a5a1-1a96-a331-d46a91713570.png)
もう一度、選択リストからCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
そして上記`プロンプト専用フィルター`であるCustomerCodeフィルターの参照名を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0a7261a5-22c7-50c1-e437-8b680fd1a906.png)
つまり親クエリからのフィルター値を、子クエリでは`プロンプト専用フィルター`のCustomerCodeフィルターで受け、それをそのまま実働CustomerCodeフィルターへ単に渡すという仕組みです。
実用的ではありませんが、イメージが伝わればいいかなと思います。
### パラメータ用に専用フィールドを用意する場合 ###
Text型のフィールドを専用フィルターとして新規追加します。
フィルター領域の右クリックメニューを開き、**その他** -> **テキスト**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e8841b92-5cd7-17fe-ae62-6f2e854bcf8a.png)
テキストフィールドがフィルターとして追加されます。摘要を**パラメータ1**、参照名を**P1**とします。
**このようにして追加されたフィルターは、デフォルトでプロンプトフィルターとなります。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1f58bc15-acba-cca6-d6eb-56b668438d8d.png)
値欄に親クエリのCustomerCodeフィルターの参照名を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bdea46e1-473b-ab01-4629-76e7a9a56c23.png)
選択リストからCustomerCodeフィールドをフィルター領域へドラッグ&ドロップで設定します。
そして**パラメータ1**フィルターの参照名**P1**を、CustomerCodeフィルターの値欄に設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/227575ea-2fc2-a3d2-d625-ec1df32b8a47.png)
これで親から子への値の受け渡しが行えます。
# おわりに #
今回はあまり実用的ではない単純な例で紹介してきましたが、イメージは伝わりましたか？
複雑なレポートを作成する場合に使える場面が多々出てくると思います。
試してみてくださいね。