Element level security in Cognos BI with TM1 as data source

[sreesuku]

Hi Team,

I am trying to implement element level security in Cognos BI (Ver 10). Cognos TM1 (Ver 10) is my data source, basically its a DMR model.

I have a 5 dimensions in my TM1 cube. Measure, Period, Country, Sector, Users.
I am loading 'Hours' measure for all dimensions except 'User'. (Since I dnt have hours data by users)

A dummy measure is loaded by 'User' dimension to mark which all elements that user has access, as shown below.

User   Country   Sector  Dummy Measure
A        India        XYZ          1
A        China       XYZ          1
B        India        PQR         1

My requirement is to display 'Hours' data to user A, only for 2 countries (India and China) as per the access level given above

In Report studio we using the function #sq($account.personalInfo.email)# to capture logged in user. We are using the below 2 queries

Query 1

Data items :-
Country

Detailed filters :
User = #sq($account.personalInfo.email)#
Dummy measure > 0

Query 2
We will use the above filtered countries to retrieve the 'Hours' value
Data items : Country(From Query1), Hours

But the second query is not working since 'Country' is derived using Query1 which has a 'User' filter

Any other way to refer between these 2 queries ??
We tried filter function also, but its not working along with the macro (#sq($account.personalInfo.email)#)

Regards
Sree