Cognos Analytics

Cognos Analytics

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

 View Only
Back to discussions
Expand all | Collapse all

Filter in my Cognos Framework Manager that does not give the correct SQL result

  • 1.  Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Fri August 29, 2025 01:18 PM

    Hello,

    I have a filter in one of my query subject as follows :

    NOT (
    [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] in ('CF26'; 'CF27'; 'CF28'; 'CF29')
    and [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] = 'ZA036'
    )

    The problem is that Cognos interprets this filter incorrectly in SQL :

    WHERE 
    NOT ( D_COMPTE_SF_COMMUN_H.CD_REGRP_COMPT IN ( 
        'CF26', 
        'CF27', 
        'CF28', 
        'CF29' ) ) OR
    NOT ( D_HIERC_PRODT_SF_CALC_C.CD_PRODT = 'ZA036' )

    I want both conditions to be true to exclude this data in my table. Cognos don't to that at all. I think maybe it's because my two filters are in different dimensions. But both are link to the fact table.

    I tried also :

    NOT (
    [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] in ('CF26'; 'CF27'; 'CF28'; 'CF29')
    or [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] = 'ZA036'
    )

    [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] not in ('CF26'; 'CF27'; 'CF28'; 'CF29')
    and [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] <> 'ZA036'

    Cognos still interprets it wrong.

    What is the problem?

    Thanks.



    ------------------------------
    Guillaume Lafrance
    ------------------------------


  • 2.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Fri August 29, 2025 05:16 PM

    That filter issue in framework manager usually happens when the logic doesn't translate cleanly into sql. sometimes cognos applies the filter locally instead of pushing it into the sql, which can give mismatched results. It can also happen if the filter uses functions or data types the database doesn't support, or if the field you're filtering is part of the report layout-cognos might skip putting it in the WHERE clause unless it's prompted or wrapped differently.

    a good way forward is to simplify the filter into a direct comparison, test with the field removed from the layout, or force cognos to push the filter with a prompt or dummy expression. If that still doesn't help, parameter maps or macros in fm can sometimes make it work properly.

    Would you like me to suggest a couple of quick test queries you can run to confirm if cognos is filtering locally or at the sql level?



    ------------------------------
    Yunita Damanik
    ------------------------------

    Original Message


  • 3.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Mon September 01, 2025 07:27 AM

    Hi Guillaume, 

    Are you sure there is something wrong? IMHO the conditions of your filter and in the SQL are logically the same. 

    Best regards 

    Philipp 



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------

    Original Message


  • 4.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Tue September 02, 2025 02:45 AM

    the filters

    not  ( A and B )

    and 

    not A or not B

    are logically the same. So whilst Cognos is rewriting your filter it has exactly the same effect. Normally Cognos will do this to simplify the SQL.

    The easiest way to demonstrate equivalence is by :
    In this may the purple square is true for A and B. Therefore the orange squares must be NOT ( A and B )

    Repeat this for OR...
    The yellow squares are NOT A, the red sqaures are NOT B. If you OR the two together you get the same pattern as the orange squares.



    ------------------------------
    Marc Reed
    Reporting Lead
    ------------------------------

    Original Message


  • 5.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Tue September 02, 2025 09:31 AM

    You said that both are different dimensions and also referred to a Fact table. If your package is dimensional then MDX should be used, not filters. If it is a relational package then try this:

    (
     [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] not in ('CF26', 'CF27', 'CF28', 'CF29')
     
      and
     
     [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] <> 'ZA036'
    )
    Another way is to create two filters in the query:
    1> [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] not in ('CF26', 'CF27', 'CF28', 'CF29')

    2> [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] <> 'ZA036'



    ------------------------------
    cognostechie
    ------------------------------

    Original Message


  • 6.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted Tue September 02, 2025 09:31 AM

    Try this:

     
    (
     [Vue physique].[D_COMPTE_SF_COMMUN_H].[CD_REGRP_COMPT] not in ('CF26', 'CF27', 'CF28', 'CF29')
     
      and
     
     [Vue physique].[D_HIERC_PRODT_SF_CALC_C].[CD_PRODT] <> 'ZA036'
    )


    ------------------------------
    cognostechie
    ------------------------------

    Original Message


  • 7.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 21 days ago
    Edited by Guillaume Lafrance 21 days ago

    Hi all, 

    It's still not working after all. It's still does this :

       WHERE 
        NOT ( D_COMPTE_SF_COMMUN_H.CD_REGRP_COMPT IN ( 
            'CF26', 
            'CF27', 
            'CF28', 
            'CF29' ) ) AND
        D_HIERC_PRODT_SF_CALC_C.CD_PRODT <> 'ZA036'

    And I think it should be doing this instead :

       WHERE 
        NOT ( D_COMPTE_SF_COMMUN_H.CD_REGRP_COMPT IN ( 
            'CF26', 
            'CF27', 
            'CF28', 
            'CF29' ) AND
        D_HIERC_PRODT_SF_CALC_C.CD_PRODT <> 'ZA036')

    I know because I have a product ZA036 but with a group code CA89 (not in the list to exclude) and it does not appear in my result.

    Thanks.



    ------------------------------
    Guillaume Lafrance
    ------------------------------

    Original Message


  • 8.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 21 days ago

    The SQL is different to what you originally posted, so you must have changed something.
    Rather than get everyone to try and debug SQL you may be better off with us helping you to write the filter you want. You may need to provide some examples. 

    Provide us with a table that looks like this, otherwise we are all just guessing at what you are trying to acheive



    ------------------------------
    Marc Reed
    Reporting Lead
    ------------------------------

    Original Message


  • 9.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 20 days ago



    ------------------------------
    Guillaume Lafrance
    ------------------------------

    Original Message


  • 10.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 20 days ago
    Edited by Marc Reed 20 days ago

    I uploaded the file into a DM...

    And in the last example, which I think is the result set that you want, the filter in mu query is :

    not( 
       [PRODT] = 'ZA036' and 
       [COMPT] in ( 'CF26' , 'CF27' , 'CF28' , 'CF29' )
    )



    which cognos has translated into native sql of 


    SELECT
        `Sheet10`.`COMPT` AS `COMPT`, 
        `Sheet10`.`PRODT` AS `PRODT`
    FROM
        `ft2622526865_forum_xlsx_Sheet1` `Sheet10` 
    WHERE 
        `Sheet10`.`PRODT` <> 'ZA036' OR
        NOT ( `Sheet10`.`COMPT` IN ( 
            'CF26', 
            'CF27', 
            'CF28', 
            'CF29' ) ) 
    GROUP BY 
        `Sheet10`.`COMPT`, 
        `Sheet10`.`PRODT`

    All of the above looks very much like the filters you posted originally so I am unsure what is going on.

    I think the next thing to do is post the FULL sql statement from your query. You only posted the Where clause. 
    You mentioned that the filters are using columns from different tables. So a fact and 2 dimensions. We need to see that query as it may be the joins that are causing you issues, rather than the filter.




  • 11.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 20 days ago

    Hi Guillaume, Marc,


    If you are doing this often I would recommend you consider using Security 4X to secure Framework Manager.  S4X allows you to use queries (or dimensions) to secure most things in your FM model. It will automatically work out the expressions and perform the required updates into your FM model and then you can go check them just like you would if you were writing the filters manually.

    The reason you might want to use either dimensions or prebuilt queries to drive your filter expressions is that then you can automatically add, update or remove security filters in your FM model based on the categories that exist in your data at any point in time.

    If you are doing this a lot, have large or many FM models and your FM security is data driven (which seems to be your case), then S4X might be something to consider.  You can also ask S4X to leave existing security in place if your just trying to address a new model enhancement.

    S4X comes with Cognos reports that will tell you by FM model what is secured and what is not secured. These helps with confidence (and visibility to security) that security is how you want it (or that you haven't missed other queries that should be updated).

    Cheers Paul

    paul.hulford@attaininsight.com

    https://attaininsight.com/security-4x-for-data-security

    or info@attaininsight.com



    ------------------------------
    Paul Hulford
    ------------------------------

    Original Message


  • 12.  RE: Filter in my Cognos Framework Manager that does not give the correct SQL result

    Posted 19 days ago

    From my test, your original SQL code and FM code produce the same results. See below

    create table bxg_test1 (cd_regrp_compt varchar (4), cd_prodt varchar(5))

    insert into bxg_test1 (cd_regrp_compt, cd_prodt) values
    ('CF26', 'ZA036'),
    ('CF26', 'XX036'),
    ('XX26', 'ZA036'),
    ('XX26', 'XX036')

    /*** RESULTS #1
    cd_regrp_compt    cd_prodt
    CF26    XX036
    XX26    ZA036
    XX26    XX036
    ***/

    select * from bxg_test1 where
    NOT (
    [CD_REGRP_COMPT] in ('CF26', 'CF27', 'CF28', 'CF29')
    and [CD_PRODT] = 'ZA036'
    )

    /*** RESULTS #2
    cd_regrp_compt    cd_prodt
    CF26    XX036
    XX26    ZA036
    XX26    XX036
    ***/

    select * from bxg_test1 where
        NOT (CD_REGRP_COMPT IN ( 
            'CF26', 
            'CF27', 
            'CF28', 
            'CF29' ) ) OR
        NOT (CD_PRODT = 'ZA036' )



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


Related Content