Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Handling Parameters from Oracle SQL to Cognos

    Posted Tue May 02, 2023 11:03 AM

    Hi guys,
    we have a report in Cognos that is purely SQL query based one,
    I am facing an issue with the query now, ETL team shared the updated query for the report

    the query looks like this:

    select
    i.company,i.vendor,i.idte as "IDATE",i.pdate as "PDATE",i.ddate as "DDATE"
    from Table i
    left join Table2 p ON
    i.com = p.com AND
    i.VENDOR = p.VENDOR AND
    p.DATE <= :vAsOfDate AND
    (p.vDATE > :vAsOfDate OR
    p.vDATE <= '01-JAN-1900' OR
    p.vDATE is NULL)

    the highlighted in yellow " :vAsOfDate " is the parameter/variable that the ETL team is dynamically giving inputs to query
    those parameters are in join conditions.
    how can we tackle these parameters at the report level?
    if they were in where condition in the query, I would have kept them in the Detail filter but those parameters are in join conditions.

    please help me figure this out, thanks!!


    ------------------------------
    P S
    ------------------------------


  • 2.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Wed May 03, 2023 03:33 AM

    Assuming that you will want the report consumer to pass in the value of vAsOfDate via a prompt of some kind.
    The answer depends on what modelling too you are using.

    If you are using FM, then use the join expression editor when defining the join between Table and Table2. In the join expression editor just define the condition like in your sql statement, but use either ?vAsOfDate? or a macro #prompt#.  

    Data Modules is a little trickier as you cant define an expression for a join (oh I wish you could). You have to use the clunky join column thing and the only thing you can do with that is match columns. You also cant do an OR in data module join editor, so i dont think this is possible within Data Modules!  If you didnt have the OR part of the expression then you could define a calculation on table as simply ?vAsOfDate? then use that calculation in the join between the two tables. 
    But its the OR part that is the hard bit




    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 3.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Wed May 03, 2023 03:50 AM

    Maybe I'm missing something but in my opinion you could solve it in several ways. To ideas:

    In reporting:
    Use two query objects. One for Table (alias i) and one for Table2 (alias p). You could use 'normal' prompts like ?vAsOfDate? to filter Table2.
    Join both tables via a left join in reporting based on the attributes com and VENDOR.

    In datamodules:
    You could do quite similar like in reporting except you should use a macro-prompt like #prompt( 'vAsOfDate'; 'date'; 'current_date' )#. Then you could create a default join without any complex expression.



    ------------------------------
    Michiel Schakel
    ------------------------------

    Original Message


  • 4.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Thu May 04, 2023 09:51 AM

    Thanks, Michiel
    As you guys said I' am using Macro's to handle those parameters in the query level itself.



    ------------------------------
    P S
    ------------------------------

    Original Message


  • 5.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Wed May 03, 2023 08:26 AM

    If this is in Framework Manager, you could try something like this:

    SELECT
    I.COMPANY,
    I.VENDOR,
    I.IDTE AS "IDATE",
    I.PDATE AS "PDATE",
    I.DDATE AS "DDATE"
FROM
    TABLE I
    LEFT JOIN TABLE2 P
    ON I.COM = P.COM
    AND I.VENDOR = P.VENDOR
    AND P.DATE <= #SQ(PROMPT('vAsOfDate',
    'date'))#
    AND (P.VDATE > #SQ(PROMPT('vAsOfDate',
    'date'))#
    OR P.VDATE <= '01-JAN-1900'
    OR P.VDATE IS NULL)


    ------------------------------
    Sanish John
    ------------------------------

    Original Message


  • 6.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Thu May 04, 2023 09:57 AM

    Thanks, John
    As you guys said I' am using Macros to handle those parameters in the query level itself.
    Is it possible to keep Static choices like "All" in the macros in the query level to select every option from the prompt page?



    ------------------------------
    P S
    ------------------------------

    Original Message


  • 7.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Thu May 04, 2023 10:20 AM

    Your original problem only has a single parameter - vAsOfDate.

    What would 'every option' for that date be?



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 8.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Thu May 04, 2023 10:31 AM

    Hi Marc,
    Thanks!!

    I have another parameter where we need to select companies at the query level.
    so far using macros I'  am able to achieve single or multiple companies using " in (#promptmany('Companies')#)",
    now how can we pass the "All"  static choice (selecting all companies as default) in macros at the query level?



    ------------------------------
    P S
    ------------------------------

    Original Message


  • 9.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Thu May 04, 2023 12:09 PM
    Edited by Marc Reed Thu May 04, 2023 10:07 PM

    #promptmany(   'p_ProdLine', 'string', '1=1' , '[Product Line] in (', '', ')' )#



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 10.  RE: Handling Parameters from Oracle SQL to Cognos

    Posted Tue May 09, 2023 11:33 AM

    Hi Marc,
    Thanks for the solution.



    ------------------------------
    P S
    ------------------------------

    Original Message


Related Content