Cognos レポートでSQL直書き時にwhere句にプロンプトを仕込む例

#概要
Cognos BI & Analyticsで、レポート実行時にCognosが思うようなSQLを発行してくれず、自分でSQLを直書きしてパフォチューする事がありますが、where句にプロンプトを仕込む書き方の例です。

#内容
元々こんなレポートがあったとします。
単純なリストに2個アイテムを置いているレポートです。
![001.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/8517c9f8-0f11-6b95-c86b-fdb221395022.png)

クエリーには、pPLというパラメーター名でフィルターを置いています。
このフィルターの動作を、SQL直書きにした場合に、どういう風に書くかという話です。
![002.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/24fafd06-ce7f-6581-0b6c-3f932c91e1e2.png)

とりあえずこのレポートのSQLを抽出してみます。
![003.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/c435d158-aacc-41ff-1fb5-da4addc45a7f.png)

こんな感じにSQLが表示されます。
![004.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/a516a14d-e749-29d6-b6c1-ba7161215d87.png)

以下のSQLが発行されているので、これを元に考えてみます。
"coguda01"."PRODUCT_LINE_EN" = ?　のところを、どのように書き換えるかがポイントです。

```html

select "coguda01"."PRODUCT_LINE_EN" "Product_line" , sum("SLS_SALES_FACT"."QUANTITY") "Quantity" 
 from "GOSALESDW"."SLS_PRODUCT_DIM" "coguda00", "GOSALESDW"."SLS_PRODUCT_LINE_LOOKUP" "coguda01", "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "coguda02", "GOSALESDW"."SLS_PRODUCT_LOOKUP" "coguda03", "GOSALESDW"."SLS_PRODUCT_COLOR_LOOKUP" "coguda04", "GOSALESDW"."SLS_PRODUCT_SIZE_LOOKUP" "coguda05", "GOSALESDW"."SLS_PRODUCT_BRAND_LOOKUP" "coguda06", "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT"
 where "coguda03"."PRODUCT_LANGUAGE" = 'EN' and "coguda00"."PRODUCT_LINE_CODE" = "coguda01"."PRODUCT_LINE_CODE" and "coguda00"."PRODUCT_NUMBER" = "coguda03"."PRODUCT_NUMBER" and "coguda00"."PRODUCT_SIZE_CODE" = "coguda05"."PRODUCT_SIZE_CODE" and "coguda00"."PRODUCT_TYPE_CODE" = "coguda02"."PRODUCT_TYPE_CODE" and "coguda00"."PRODUCT_COLOR_CODE" = "coguda04"."PRODUCT_COLOR_CODE" and "coguda06"."PRODUCT_BRAND_CODE" = "coguda00"."PRODUCT_BRAND_CODE" and "coguda01"."PRODUCT_LINE_EN" = ? and "coguda00"."PRODUCT_KEY" = "SLS_SALES_FACT"."PRODUCT_KEY"
 group by "coguda01"."PRODUCT_LINE_EN" FOR FETCH ONLY

```

まずは単一選択でやりたい場合。promptマクロを使います。
クエリー・エクスプローラーでSQLを元のクエリーにリダイレクトするようにして、SQLを直書きします。
先ほど抽出したSQLから、"(ダブルコーテーション)を全部削除し、SQLの最後の FOR FETCH ONLY も削除しています。
先ほどの ? のところは、coguda01.PRODUCT_LINE_EN = #prompt('pPL','string','''Golf Equipment''')#　にしています。
SQLのプロパティで、「Data Source」を指定しています。
![005.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/b630ecc5-4e46-0e75-4dd8-9ea665dbdae2.png)

実際のSQLはこちらです。

```html

select coguda01.PRODUCT_LINE_EN Product_line , sum(SLS_SALES_FACT.QUANTITY) Quantity 
 from GOSALESDW.SLS_PRODUCT_DIM coguda00, GOSALESDW.SLS_PRODUCT_LINE_LOOKUP coguda01, GOSALESDW.SLS_PRODUCT_TYPE_LOOKUP coguda02, GOSALESDW.SLS_PRODUCT_LOOKUP coguda03, GOSALESDW.SLS_PRODUCT_COLOR_LOOKUP coguda04, GOSALESDW.SLS_PRODUCT_SIZE_LOOKUP coguda05, GOSALESDW.SLS_PRODUCT_BRAND_LOOKUP coguda06, GOSALESDW.SLS_SALES_FACT SLS_SALES_FACT
 where coguda03.PRODUCT_LANGUAGE = 'EN' and coguda00.PRODUCT_LINE_CODE = coguda01.PRODUCT_LINE_CODE and coguda00.PRODUCT_NUMBER = coguda03.PRODUCT_NUMBER and coguda00.PRODUCT_SIZE_CODE = coguda05.PRODUCT_SIZE_CODE and coguda00.PRODUCT_TYPE_CODE = coguda02.PRODUCT_TYPE_CODE and coguda00.PRODUCT_COLOR_CODE = coguda04.PRODUCT_COLOR_CODE and coguda06.PRODUCT_BRAND_CODE = coguda00.PRODUCT_BRAND_CODE and coguda01.PRODUCT_LINE_EN = #prompt('pPL','string','''Golf Equipment''')# and coguda00.PRODUCT_KEY = SLS_SALES_FACT.PRODUCT_KEY
 group by coguda01.PRODUCT_LINE_EN

```

それぞれのデータアイテムの取得元を、「SQL1」に変更する事もお忘れなく。
また、詳細フィルターも削除しています。
![006.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/14caa87d-c24e-d978-1a93-a258ec781771.png)


レポート実行するとプロンプト画面が呼び出されるので、適当な値を入力して実行します。
![007.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/d907ce4c-53b6-e39a-fc59-9501e981552c.png)

ちゃんと絞り込まれました。
![008.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/b1254a9e-d573-0e35-0f99-4a718c75df45.png)

次に、複数選択の場合のSQLです。promptmanyマクロを使います。
例の箇所は、以下の記載にしています。
= ではなく in にしているのでご注意下さい。
coguda01.PRODUCT_LINE_EN in (#promptmany('pPL','string',sq('NoSelect'))#)

![009.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/a285b2f2-47dd-c184-512a-497a1f4d1418.png)


実際のSQLはこちらです。

```html

select coguda01.PRODUCT_LINE_EN Product_line , sum(SLS_SALES_FACT.QUANTITY) Quantity 
 from GOSALESDW.SLS_PRODUCT_DIM coguda00, GOSALESDW.SLS_PRODUCT_LINE_LOOKUP coguda01, GOSALESDW.SLS_PRODUCT_TYPE_LOOKUP coguda02, GOSALESDW.SLS_PRODUCT_LOOKUP coguda03, GOSALESDW.SLS_PRODUCT_COLOR_LOOKUP coguda04, GOSALESDW.SLS_PRODUCT_SIZE_LOOKUP coguda05, GOSALESDW.SLS_PRODUCT_BRAND_LOOKUP coguda06, GOSALESDW.SLS_SALES_FACT SLS_SALES_FACT
 where coguda03.PRODUCT_LANGUAGE = 'EN' and coguda00.PRODUCT_LINE_CODE = coguda01.PRODUCT_LINE_CODE and coguda00.PRODUCT_NUMBER = coguda03.PRODUCT_NUMBER and coguda00.PRODUCT_SIZE_CODE = coguda05.PRODUCT_SIZE_CODE and coguda00.PRODUCT_TYPE_CODE = coguda02.PRODUCT_TYPE_CODE and coguda00.PRODUCT_COLOR_CODE = coguda04.PRODUCT_COLOR_CODE and coguda06.PRODUCT_BRAND_CODE = coguda00.PRODUCT_BRAND_CODE and coguda01.PRODUCT_LINE_EN in (#promptmany('pPL','string',sq('NoSelect'))#) and coguda00.PRODUCT_KEY = SLS_SALES_FACT.PRODUCT_KEY
 group by coguda01.PRODUCT_LINE_EN

```

レポート実行時のプロンプト画面です。

![010.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/98371013-8828-b245-b950-5ffa8728298f.png)

複数選択で絞り込まれました。
![011.PNG](https://qiita-image-store.s3.amazonaws.com/0/147246/7f2d7074-c907-24d2-6439-85cf86565074.png)

また、このプロンプトは「必須」になりますので、「オプション」にしたい場合は、こちらの記事をご参照下さい。
**Cognos レポートでSQL直書き時に使用するpromptmanyをオプションにする方法**
https://qiita.com/shinyama/items/8f1ea0877033cdb44396

日付のプロンプトの場合の例も置いておきます。
= cast(#sq(prompt('pDate','Date','4000-12-31'))# as date)

期間で指定したい場合の例。
between cast(#sq(prompt('pDate','Date','4000-12-31'))# as date) and cast(#sq(prompt('pDate','Date','4000-12-31'))# as date) + 1 month