Username in cognos sql

[mrcool]

Hi All,

We want to add a report name and user name in the query so that we can track the user running that report in the backend.
We are using oracle as our DB.
My solution to add username macro to all the reports and attach it to report properties. For SQL based reports I am planning to add a comment with report name and username macro in select clause.
Any other optimal solution to track? Please advice.

Thanks,
MC

[cognostechie]

I am not sure if using the Cognos macro will make it part of the SQL that goes to the DB. Moreover, you will have to put it into every report. You can create a filter in the query subjects in Framework Manager with this expression:

#sq($account.defaultName)#  = #sq($account.defaultName)#

All reports that use this query subject will inherit it and the SQL that goes to the DB will show the user name in the where
clause as:

where
   'John Doe' = 'John Doe'

then you can ask Mr Doe to stop running his reports if they are too slow and causing problems for the DB :)

[mrcool]

Thanks cognostechie..
I added a query item and made that part of the list properties..
Your logic sounds better.

Thanks
MC

[MFGF]

An alternative is to change the properties of the data source connection to Oracle so that every user's ID is used to log them into the database. Tat way you can track the queries for each database user. I'm willing to bet that at the moment all access to Oracle from Cognos uses a single Oracle signon?

MF.