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.

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?

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' )