How to show the results for only the user who is generating the report.

[JoelR]

Is there a way of making a Cognos report automatically (by default)generate a report for just the user who is logged in or generating the report?  So users can not run the same report to view other user's information?


Here is my code, maybe I could set the prompt to [Base].[R5USERORGANIZATION].[UOG_USER]=?R5_USER? If so how would you do that?

SELECT
   EXC_DATE, EXC_PERSON, EXC_HOURS, EXC_COMMENT, SHP_SHIFT, SHP_PERSON,

   CASE
      WHEN SHP_SHIFT IN ('A', 'B', 'C', 'LUBE') THEN (10-(EXC_HOURS))
      WHEN SHP_SHIFT IN ('D', 'E', 'F', 'G') THEN (12-(EXC_HOURS))
   ELSE
      (8-(EXC_HOURS))
   END TIMEUSED

FROM
   R5EXCEPTIONS LEFT OUTER JOIN R5SHFPERS ON EXC_PERSON = SHP_PERSON

WHERE
   (EXC_COMMENT = 'vacation' OR EXC_COMMENT = 'Vacation' OR EXC_COMMENT = 'VACATION') AND
   EXC_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE AND
   EXC_PERSON = #prompt('Parameter1')#

[Suraj]

May be this information helps you:

CSVIdentityName
Use the identity information of the current authenticated user to lookup values in the specified
parameter map. Each individual piece of the user's identity (account name, group names, role
names) is used as a key into the map. The unique list of values that is retrieved from the map is
then returned as a string, where each value is surrounded by single quotes and where multiple
values are separated by commas.
Syntax
CSVIdentityName ( %parameter_map_name [ , separator_string ] )
Example
#CSVIdentityName ( %security_clearance_level_map )#
Result: 'level_500' , 'level_501' , 'level_700'

CSVIdentityNameListReturns the pieces of the user's identity (account name, group names, role names) as a list of
strings. The unique list of values is returned as a string, where each value is surrounded by single
quotes and where multiple values are separated by commas.
Syntax
CSVIdentityNameList ( [ separator_string ] )
Example
#CSVIdentityNameList ( )#
Result: 'Everyone' , 'Report Administrators' , 'Query User'

[JoelR]

Thanks for the response but I have not been able to figure out how to apply your example to my code.  Do you have any further suggestions?  Thanks.

[Suraj]

There are other functions that give you user name as well such as:
#sq($account.defaultName)# gives you user name.
then you can compare that with your data item but make sure both data items are comparable as one may have first name first and another may have last name first.

You can also create a data item using the functions I mentioned above:
IF
(#sq(CSVIdentityNameList())# like '%Neupane%')  THEN ('Yes, the user is Neupane.')
ELSE ('No!!! The user is ' + #sq($account.defaultName)# )

etc...

[JoelR]

Thank you for your answer but perhaps this is a better way of approaching the problem.

How do I modify this sql code to do the following:
There is a table called R5PERSONNEL (Not yet included below). Inside of table R5PERSONNEL is a record called PER_CODE (like a social security number). I want the sql below to prompt on PER_CODE instead of EXC_PERSON (prompted by name, shown below). Table R5PERSONNEL can be joined to table R5EXCEPTIONS by record PER_USER (In R5PERSONNEL) and EXC_PERSON (In R5EXCEPTIONS).   PER_USER and EXC_PERSON are primary and foriegn keys.  Thank you I hope this is clearer.  This allows the user to enter a prompt number that is unique to him, like a social security number, instead of prompting by user name.

SELECT
EXC_DATE, EXC_PERSON, EXC_HOURS, EXC_COMMENT, SHP_SHIFT, SHP_PERSON,

CASE
WHEN SHP_SHIFT IN ('A', 'B', 'C', 'LUBE') THEN (10-(EXC_HOURS))
WHEN SHP_SHIFT IN ('D', 'E', 'F', 'G') THEN (12-(EXC_HOURS))
ELSE
(8-(EXC_HOURS))
END TIMEUSED

FROM
R5EXCEPTIONS LEFT OUTER JOIN R5SHFPERS ON EXC_PERSON = SHP_PERSON

WHERE
(EXC_COMMENT = 'vacation' OR EXC_COMMENT = 'Vacation' OR EXC_COMMENT = 'VACATION') AND
EXC_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE AND
EXC_PERSON = #prompt('Parameter1')#

[Suraj]

If you want to prompt user for ssn, you can filter the ssn column on what user enters.
In that case, you don't need to figure out user's login info.

[JoelR]

I am not getting any where with this this incident so I am closing it but I am left unresolved.  Thank you for your efforts.

[arturo2k]

Hi, in this URL they present a more clear approach to implement security using

(#CSVIdentityNameList()#

http://www.digitalviper.co.uk/blogs/Cognos/Cognos.html

Dynamic Filtering (CSVIdentityNameList)

This security set up is similar to the first option but is more dynamic in that the filter definition does not have to be altered every time the user class structure is changed. This option is also useful where a large number of user classes are used.

The security method can be used against table held entries or against a calculated query item and matches the user class in the LDAP to an entry in the table (or query item).

1. Select the model query subject that you wish to set up the security on.
2. Right click and select 'Edit definition'
3. Click on the filter button/tab (to add a new filter)
4. Add the required query item to the filter expression.
5. Add the CSVIdentify function to the expression. The end result should look like the following sample:
([Presentation Layer].[Countries].[Country_Code] in (#CSVIdentityNameList()# ))
6. Add any additional filters using a 'OR' to ensure that Administrators and report developers are not filtered:
('Administrators' in (#CSVIdentityNameList()# ))
OR
('Report Administrators') in (#CSVIdentityNameList()# ))
OR
([Business Layer].[Countries].[Country_Code] in (#CSVIdentityNameList()# ))
7.Click OK twice to enable the filter.

The security filter will become active once the package the query subject belongs to is published. At runtime the filter will activate when the query subject is used. The filter works because the CSVIdentifyNameList function lists all userclasses that the runtime user is a member of and then filters against the calculation or table entry. Again - this method is best used where large volumes of user classes are used such as account numbers.

Regards,