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