Capture logged in user into a report using extended query in QRP

unknown

Is there an input or variable or formula for the logged in user running the report that is usable in the extended query for a QRP?  I have a request to put the name of the user running the report into the report for a signature line.  There is no database field that will get me back to the use and ultimately I need to extend back to the employee table to get the first and last name.

Thanks,

Todd Dickinson  

Attachment_4462.zip

charles-hooper

Todd,
When a user other than SYSADM logs into Visual Manufacturing, Visual Manufacturing performs a second login using that user's "blind" user account. In older versions of Visual Manufacturing, that blind user account has a username of up to the first seven characters of the username followed by the # character. Current versions of Visual Manufacturing allow usernames longer than eight characters, so typically the "blind" user account is the regular username followed by the # character. This login connection using the "blind" user account is used for all Visual Manufacturing module processing, including modifying data in the database, using the Query Tool module, and running reports. The back end database engines typically allow selecting a function value from the database to retrieve the currently logged in username. If you remove the # character from the returned username, you would then have the opportunity to check the EMPLOYEE table for a matching row that has that username specified in the USER_ID column. The SQL statement to use differs depending on the database back end that you use. For example, if you run SQL*Plus, Oracle SQL Developer, or Microsoft SQL Server Management Studio, and connect to the database using a Visual Manufacturing username and password, you could then test the following:
Oracle:
SELECT FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME FROM EMPLOYEE WHERE USER_ID=REPLACE(USER,'#','');

SQL Server (one of the following):
SELECT FIRST_NAME+' '+LAST_NAME EMPLOYEE_NAME FROM EMPLOYEE WHERE USER_ID=REPLACE(CURRENT_USER,'#','');
SELECT FIRST_NAME+' '+LAST_NAME EMPLOYEE_NAME FROM EMPLOYEE WHERE USER_ID=REPLACE(USER,'#','');

To use the above in an extended query, remove EMPLOYEE_NAME and the trailing ; character from the SQL statements.

Charles Hooper
Senior Software Engineer
Meadville Forging Company