#概要
Cognos BI & Analyticsで、レポート実行時にCognosが思うようなSQLを発行してくれず、自分でSQLを直書きしてパフォチューする事がありますが、where句にプロンプトを仕込む書き方の例です。
#内容
元々こんなレポートがあったとします。
単純なリストに2個アイテムを置いているレポートです。
クエリーには、pPLというパラメーター名でフィルターを置いています。
このフィルターの動作を、SQL直書きにした場合に、どういう風に書くかという話です。
とりあえずこのレポートのSQLを抽出してみます。
こんな感じにSQLが表示されます。
以下のSQLが発行されているので、これを元に考えてみます。
"coguda01"."PRODUCT_LINE_EN" = ? のところを、どのように書き換えるかがポイントです。
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」を指定しています。
実際のSQLはこちらです。
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」に変更する事もお忘れなく。
また、詳細フィルターも削除しています。
レポート実行するとプロンプト画面が呼び出されるので、適当な値を入力して実行します。
ちゃんと絞り込まれました。
次に、複数選択の場合のSQLです。promptmanyマクロを使います。
例の箇所は、以下の記載にしています。
= ではなく in にしているのでご注意下さい。
coguda01.PRODUCT_LINE_EN in (#promptmany('pPL','string',sq('NoSelect'))#)
実際のSQLはこちらです。
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
レポート実行時のプロンプト画面です。
複数選択で絞り込まれました。
また、このプロンプトは「必須」になりますので、「オプション」にしたい場合は、こちらの記事をご参照下さい。
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