How to edit SQL with parameter 1

[BillOfPlano]

Hi,
I am a new user and just start to learning Cognos ReportNet.

How can I edit the report sql and add the following where condition into the report sql:

(
('*','$LOGIN$') IN (select perm_value,user_name from user_permission where permission_type = 'supplier')
OR
(im_supplier.supplier_name, '$LOGIN$') in select perm_value,user_name from user_permission where permission_type = 'supplier')
)

--> we mainly want to limit the certain user can see only certain supplier's data.

Please help.

 

[stacybost]

Can you add the user_permission table to your model, and add a filter for permission_type = 'supplier'?

 

[BillOfPlano]

Yes. But, we notice the sub query will have much better performance than the join. I just wondering how can I doing a subquery -- do I have to do it in framework manager?

Bill

 

[stacybost]

You can't put SQL in the filter in your query. However, you could add another tabular model or a tabular SQL as a child under your existing tabular model. Have the model or SQL return the list of logins, then in your parent tabular model, add a filter that sets the login in the parent model equal to the login in the child model/sql:

[LOGIN]=[Tabular Model1].[LOGIN]

In the Query Properties, you have to set Cross Product Allowed = 'Allow' for this to work.

Stacy

 

[BillOfPlano]

Thanks a lot.

Can the child has more than one column in select...So, in effect, I have

([field1], [login]) in ([tabular model1].[field1], [tabular model1].[login])

also, the setting of Cross Product to 'Allow' in the parent query or child query -- assume parent. Right?

 

[stacybost]

You can return more than one column in your child tabular model. If you need to use more than one returned value from the child query in your 'filter,' you would just need need to add the second field to the filter in the parent tabular model:

[LOGIN]=[Child Tabular Model].[LOGIN] and
[FIELD1]=[Child Tabular Model].[FIELD1]

The Cross Product is set at the Query level, not at the tabular model level. So yes, it is the parent.

 

[BillOfPlano]

Thanks a lot.