I am new to Cognos Analytic 11 so I will do my best to describe our issue.

We have applied data security within the framework data model on a query subject based on a group and embedded filter.

Example

qryEmployee   Role: Union06  Filter: qryEmployee.unionCode = '06'

The data security works if we add a field from the qryEmployee on the report but we expected that if there were was a relationship created thru that query subject between two other tables the filter would be applied as well.

i.e qryPhones joined to qryEmployee 

     qryEmployee joined to qryPayDetails

We created a report only adding fields qryPhones.phoneNumber, qryPayDetail.employeeNumber but there is a join path thru the qryEmployee.  We see this in the generated SQL output of the report.

Our problem is that unless we add a field from qryEmployee to the report then the filter doesn't get applied.  

Is this what is expected?  Is there anyway to force the filter based on the relationship?

------------------------------
Danielle Robbins
------------------------------

I am new to Cognos Analytic 11 so I will do my best to describe our issue.

We have applied data security within the framework data model on a query subject based on a group and embedded filter.

Example

qryEmployee   Role: Union06  Filter: qryEmployee.unionCode = '06'

The data security works if we add a field from the qryEmployee on the report but we expected that if there were was a relationship created thru that query subject between two other tables the filter would be applied as well.

i.e qryPhones joined to qryEmployee 

     qryEmployee joined to qryPayDetails

We created a report only adding fields qryPhones.phoneNumber, qryPayDetail.employeeNumber but there is a join path thru the qryEmployee.  We see this in the generated SQL output of the report.

Our problem is that unless we add a field from qryEmployee to the report then the filter doesn't get applied.  

Is this what is expected?  Is there anyway to force the filter based on the relationship?

------------------------------
Danielle Robbins
------------------------------

Hi Danielle,

Like @Patrick Neveu mention you should add the filter to the fact table (query subject) as well. Cognos applies minimized SQL which basically means that only tables (and joins) are included that are actually necessary to retrieve the requested columns. By adding the filter to the fact table you force IBM to use that table and join.

Kind regards,

Michiel

I am new to Cognos Analytic 11 so I will do my best to describe our issue.

We have applied data security within the framework data model on a query subject based on a group and embedded filter.

Example

qryEmployee   Role: Union06  Filter: qryEmployee.unionCode = '06'

The data security works if we add a field from the qryEmployee on the report but we expected that if there were was a relationship created thru that query subject between two other tables the filter would be applied as well.

i.e qryPhones joined to qryEmployee 

     qryEmployee joined to qryPayDetails

We created a report only adding fields qryPhones.phoneNumber, qryPayDetail.employeeNumber but there is a join path thru the qryEmployee.  We see this in the generated SQL output of the report.

Our problem is that unless we add a field from qryEmployee to the report then the filter doesn't get applied.  

Is this what is expected?  Is there anyway to force the filter based on the relationship?

------------------------------
Danielle Robbins
------------------------------