Filter SQL is different in Framework Manager compared with Report Studio

Cognos 8 Business Intelligence Framework Manager
1

Why does the filter defined in Framework Manager generate different SQL than in Report Studio? ???

A filter is defined on a query subject within Framework Manager then Report Studio generates multiple SELECT statement. But if the same filter is defined within Report Studio, then it will create a single SELECT with outer join and WHERE clause as expected.

Who has seen this before and what is the explanation for this… I’m working with Cognos 8.3

thanks

2

This is expected behavior. A filter in a query subject is not equivalent to a filter in a report, this is by design. :wink:

  1. Filter Applied in Framework Manager:
    A filter embedded in a query subject is always applied before the query subject is joined to the rest of the query subjects in the
    model. Whenever Report Query Processing (RQP) sees a query subject with an embedded filter, it will treat the query subject as a derived table, i.e. it will generate a separate select in the Cognos SQL.

  2. Filter Applied in Report Studio:
    A filter specified in the report is applied to the generated query.
    The exact point where RQP applies the filter depends on many factors, such as: filter type (detail vs. summary), query type
    (single-fact vs. multi-fact), the usage property of the filtered query item (identifier/attribute vs. fact), etc.
    The query subject that required a separate select in case 1 may or may not require a separate select here. The behavior depends on “Generate SQL” property of the query subject specified in FM model. If the property is set to “As View”, we generate a separate select for the query subject. If the property is set to “Minimize”, we try to avoid a separate SQL.

Hope this helps