A report shows the employee names by region and country. We need to implement data security in this report such that a user can see the records only for the country he belongs to. There are already User Groups defined on the Cognos server (in the directory) and users are made members of appropriate groups.
For this sample, I have added my user account to a user group called 'Spain'.
- Drag the appropriate columns (Region, Country, and Employee name) on to the report from Employee by Region query subject.
- Go to Query Explorer and drag a new detail filter.
- Define the filter as:
[Country] in (#CSVIdentityNameList(',')#)
- Run the report to test it. You will notice that a user can see only the rows of the country/countries of which he is a member.
Here we are using a macro function called CSVIdentityNameList. This function returns a list of groups and roles that the user belongs to, along with the user's account name. Hence, when I run the report, one of the values returned will be 'Spain' and I will see data for Spain.
The function accepts a string parameter which is used as a separator in the result. Here we are passing a comma (,) as the separator.
If a user belongs to multiple country groups, he will see data for all the countries listed in the result of a macro.
This solution, conspicuously, has its limitations. None of the user accounts or roles should be same as a country name, because that will wrongly show data for a country the user does not belong to. For example, for a user called 'Paris', it will show data for the 'Paris' region. So, there need to be certain restrictions. However, you can build upon the knowledge of this macro function and use it in many practical business scenarios.