Author Topic: Cognos 11 FM embedded security filters  (Read 145 times)

Offline BIengineer

  • Full Member
  • ***
  • Join Date: Sep 2020
  • Posts: 9
  • Forum Citizenship: +0/-0
Cognos 11 FM embedded security filters
« on: 28 Sep 2020 10:35:21 am »
Hello,

I am trying to add security filters to the package, but they don't seem to work due to some unknown reasons. The package deals with several dimensional and fact tables, with joins between them - In the Source layer.

Since the filters I try to add are in the same layer, I add them as embedded filters in the query subjects like so:

Code: [Select]
select col1, col2, col3 from Table_A
where (col1 not in (select col_value from Table_B where col_protected='1')
or (UPPER(#sq($account.personalInfo.userName)#) IN (select user_id from User_Table where user_access='1')))

The main idea is that there are two database tables that should:
1* Remove the col_protected rows in case they exist in Table_B
2* Select the rows in case the user has access to see the rows

The SQL itself works in Oracle, and running the Cognos Generated SQL in Oracle also works.
« Last Edit: 30 Oct 2020 04:15:16 am by BIengineer »