Filter SQL is different in Framework Manager compared with Report Studio

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


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