Yin Solutions: How to make prompt optional with default value in Cognos Framework manager

Check list of all posts

Problem:

As known, one of a major effort to enhance performance is to apply for minimized SQL concept, or using multiple facts, such as all these confirmation related 6 fact query subjects: CONFIRMED_TRADE_COUNT, UNCONFIRMED_TRADE_COUNT, MATCHED_TRADE_COUNT, UNMATCHED_TRADE_COUNT, LAST_STATUS_MATCHED_COUNT, LAST_STATUS_UNMATCHED_COUNT. Each query subject needs to join with about 15 dimensions. This is a pretty big effort in Framework manager. Now, these 6 query subjects needed to be based on two cases: 1) date range and 2) only end date. If we can’t put two cases in a single query object, then we need to define another 6 facts, which is a very big effort. Furthermore, the model becomes very difficult to maintain as well.

The idea is to explore a solution to make each fact query subject to cover both cases 1) date range and 2) only end date. In case when begin date is given, then use begin date, otherwise use default, which is end date -14 months.

Solution in detail:

Use failed_trade_count as sample.

Design

SELECT *
FROM (
       SELECT
             ROW_NUMBER() OVER(PARTITION BY F.TRADE_NUMBER, F.SOURCE_SYSTEM_KEY ORDER BY (CASE WHEN F.SETTLEMENT_COUNT = 1 THEN 'A' ELSE 'B' END) , F.SETTLEMENT_EVENT_DATETIME DESC, SETTLEMENT_EVENT_KEY DESC) LAST_FAILED_TRADE
             ,F.FAIL_COUNT
/* all Keys ….*/
             ,1 as Failed_Trade_Count
        FROM M3ADS_TRADE.F_SETTLEMENT_EVENT F
        INNER JOIN M3ADS_REF.D_DATE DT ON F.SETTLEMENT_EVENT_DATE_KEY =DT.DATE_KEY
        WHERE (F.FAIL_COUNT = 1 OR F.SETTLEMENT_COUNT = 1)
             AND DT.WORKDAY_INDICATOR = 1
             #prompt ('fmParam_Fail Start Date','date','AND _add_months(DT."DATE",14) > ' + prompt('fmParam_Fail End Date', 'date'), ' AND DT."DATE" >= ')#
             AND DT."DATE" <= #prompt('fmParam_Fail End Date','date')#
          ) F1
WHERE F1.LAST_FAILED_TRADE = 1
  AND F1.FAIL_COUNT = 1

1) Handle end date as below with cognos macro

AND DT."DATE" <= #prompt('fmParam_Fail End Date','date')#

2) Handle start date as below with cognos macro

             #prompt ('fmParam_Fail Start Date','date','AND _add_months(DT."DATE",14) > ' + prompt('fmParam_Fail End Date', 'date'), ' AND DT."DATE" >= ')#

We are using fourth argument of Prompt() function.

Case 1: If the user provides any value for the prompt 'fmParam_Fail Start Date' , then condition will be designed as

AND DT."DATE" >= <= #prompt ('fmParam_Fail Start Date','date')#

Case 2: If the user doesn’t provides any value for the prompt 'fmParam_Fail Start Date' , then condition will be designed

AND _add_months(DT."DATE",14) > ' + prompt('fmParam_Fail End Date', 'date')