Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Retrieve manager display name, from manager attribute which is mangers userkey

rajsannidhi
New Contributor III
New Contributor III

manager attribute returns only managers userkey, but it displays manager displayname in the UI. how  can I get managers displayname in my report, owner attribute has only manager username.

Appreciate any help in this regard. Thank You.

24 REPLIES 24

sk
All-Star
All-Star

Are talking about query where you want to pull manager displayname from manager attribute? if so then join with users table on manager column with userkey and then try to retrieve manager displayname.


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Enakshi
Saviynt Employee
Saviynt Employee

You can use the below query to fetch managers display name of the users.

select u.username, m.displayname
from users u, users m
where u.manager = m.userkey

rajsannidhi
New Contributor III
New Contributor III

Hi  SK thank you 

Yes I'm looking for a query and

1. Below query works, but when I add createdate to check users created last 24hrs it fails

select
u.username AS 'Username',
u.FIRSTNAME as 'First Name',
u.LASTNAME AS 'Last Name',
u.Companyname as company,
u.employeetype as employeetype,
u.CREATEDATE as 'date created',
m.username AS 'Manager Username',
m.displayname as 'Manager Name',
u.OWNER as Owner
from users u,
users m
where u.manager = m.userkey
and u.statuskey = 1
and u.employeetype in ('Vendor','Contractor')

CREATEDATE > NOW() - INTERVAL 24 HOUR - adding this it throws warning query returned no columns

2. I did try Join as below, it doesn't work

SELECT
u.USERNAME as username,
u.FIRSTNAME as firstname,
u.LASTNAME as lastname,
u.OWNER as Owner,
u.companyname as company,
u.CREATEDATE as createdate,
u.USERSOURCE as source,
u.STATUSKEY,
m.displayname AS 'Manager Name'
FROM
users u
inner join users m on (u.manager = m.userkey)
where
CREATEDATE > NOW() - INTERVAL 24 HOUR
and u.statuskey=1

3. If I don't use manager column, check userkey the create date condition works fine but I can't get managers displayname

since you added createdate with table alias which exists in both user and manager then it will be ambiguous

Try modify the query as below and it should work

select
u.username AS 'Username',
u.FIRSTNAME as 'First Name',
u.LASTNAME AS 'Last Name',
u.Companyname as company,
u.employeetype as employeetype,
u.CREATEDATE as 'date created',
m.username AS 'Manager Username',
m.displayname as 'Manager Name',
u.OWNER as Owner
from users u,
users m
where u.manager = m.userkey
and u.statuskey = 1
and u.employeetype in ('Vendor','Contractor')
and u.CREATEDATE > NOW() - INTERVAL 24 HOUR

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

SELECT u.USERNAME as username, u.FIRSTNAME as firstname, u.LASTNAME as lastname, u.OWNER as Owner, u.companyname as company, u.CREATEDATE as createdate, u.USERSOURCE as source, u.STATUSKEY, m.displayname AS 'Manager Name' FROM users u inner join users m on (u.manager = m.userkey) where u.CREATEDATE > NOW() - INTERVAL 24 HOUR


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rajsannidhi
New Contributor III
New Contributor III

Thank you SK, that worked.

rajsannidhi
New Contributor III
New Contributor III

Is it possible to set up email template and send a report directly to the respective managers in the report when a vendor/contractor created.

I'm able to send it to set of users, but want to send it directly to the manager

Yes it is possible make sure that in the report username is of manager user name instead of end user user name then it works. Because ${userEmail} will look for username column hence you need to make sure that column represents manager username.

select
u.username AS 'User Username',
u.FIRSTNAME as 'First Name',
u.LASTNAME AS 'Last Name',
u.Companyname as company,
u.employeetype as employeetype,
u.CREATEDATE as 'date created',
m.username,
m.displayname as 'Manager Name',
u.OWNER as Owner
from users u,
users m
where u.manager = m.userkey
and u.statuskey = 1
and u.employeetype in ('Vendor','Contractor')
and u.CREATEDATE > NOW() - INTERVAL 24 HOUR

 

Another one what you can try is use ${ANALYTICSDATA.'Manager Email'[0]} with below query but not sure if it works. If it doesn't work then maybe you try looping it with report size

select
u.username AS 'Username',
u.FIRSTNAME as 'First Name',
u.LASTNAME AS 'Last Name',
u.Companyname as company,
u.employeetype as employeetype,
u.CREATEDATE as 'date created',
m.username AS 'Manager Username',
m.displayname as 'Manager Name',
m.email as 'Manager Email',
u.OWNER as Owner
from users u,
users m
where u.manager = m.userkey
and u.statuskey = 1
and u.employeetype in ('Vendor','Contractor')
and u.CREATEDATE > NOW() - INTERVAL 24 HOUR


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Example 5 

https://saviynt.freshdesk.com/support/solutions/articles/43000622153-managing-email-templates

1 create email template

2 attach to analytics report

3 report should have username column where email will be sent

4 global configuration select group email so 1 manager will receive 1 email for all subordinate ( consolidated) 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

${userEmail} in email template & having username column in the report is sending email to the manager, but it is not including the attachment/analytics report to the email 

Hello Rushikesh,

${userEmail} - I have this in the TO field in the email template and USERNAME column in the query/report, the email is being sent to respective managers, but report is not included, am i missing anything here.

the email with report is working only when I use full email address in the TO filed

What you mean by this statement? Are you saying you hardcorded the manager email in to address? 

the email with report is working only when I use full email address in the TO filed

  


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rajsannidhi
New Contributor III
New Contributor III

yes that is correct, when i use abc.xyz@gmail.com in the TO field, email is sent with report included

but with {useremail}, email is sent without the report

Its expected behavior when you filter data report won't be attached


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Okay thank you.

Also, I'm trying to include data as table in the body, using loop and VENDORUSERNAME its not working with below config, 

<table>
<tr>
<th>Vendor Username</th>
<th>First Name</th>
<th>Last Name</th>
<th>company</th>
<th>employeetype</th>
<th>Manager Name</th>
</tr>
<% int count=Integer.parseInt("${ANALYTICSDATA.VendorUsername.size()}"); for(int i=0;i<count;i=i+1){%>

<tr>
<td>${ANALYTICSDATA.'VendorUsername'[i]}</td>
<td>${ANALYTICSDATA.'First Name'[i]}</td>
<td>${ANALYTICSDATA.'Last Name'[i]}</td>
<td>${ANALYTICSDATA.'company'[i]}</td>
<td>${ANALYTICSDATA.'employeetype'[i]}</td>
<td>${ANALYTICSDATA.'Manager Name'[i]}</td>
</tr>
<% } %>
</table>

------------------------------

SQL Query

select
u.username AS 'VendorUsername',
u.FIRSTNAME as 'First Name',
u.LASTNAME AS 'Last Name',
u.Companyname as company,
u.employeetype as employeetype,
u.CREATEDATE as 'date created',
m.displayname as 'Manager Name',
m.email as 'Manager Email',
m.username AS 'USERNAME',
u.OWNER as Owner
from users u,
users m
where u.CREATEDATE > NOW() - INTERVAL 24 HOUR
and u.manager = m.userkey
and u.statuskey = 1
and u.employeetype in ('Vendor','Contractor')
and u.orgunitid in ('FPI')

Are you getting values for all others expect vendorusername? and is it pulling all records?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rajsannidhi
New Contributor III
New Contributor III

With for loop its not even triggering the email

Without for loop, email is triggered but not pulling all records, only first row is included in the email body.

if I just use ANALYTICSDATA in the email body, all records are included but data in not structured

Change the for loop as below and see if that works

<% int count=Integer.parseInt("${ANALYTICSDATA.size()}"); for(int i=0;i<count;i=i+1){%>

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rajsannidhi
New Contributor III
New Contributor III

I tried below config, email is not triggered

<table>
<tr>
<th>Vendor Username</th>
<th>First Name</th>
<th>Last Name</th>
<th>Company</th>
<th>Employeetype</th>
<th>Manager Name</th>
</tr>

<% int count=Integer.parseInt("${ANALYTICSDATA.size()}"); for(int i=0;i<count;i=i+1){%>
<tr>
<td>${ANALYTICSDATA.'Vendor Username'[i]}</td>
<td>${ANALYTICSDATA.'First Name'[i]}</td>
<td>${ANALYTICSDATA.'Last Name'[i]}</td>
<td>${ANALYTICSDATA.'Company'[i]}</td>
<td>${ANALYTICSDATA.'Employeetype'[i]}</td>
<td>${ANALYTICSDATA.'Manager Name'[i]}</td>
</tr>
<% } %>
</table>

Can you share the logs and also can you share the screenshot of template?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rajsannidhi
New Contributor III
New Contributor III

Hi Rushikesh,

I’m not seeing anything in the logs, please refer below screenshot, I’m editing first with HTML set to false, update and then set HTML to true and update.

rajsannidhi_0-1673290187675.png

 

Can you try to enable Advanced HTML CSS and see if that works?

 

<body><table>
<tr>
<th>Vendor Username</th>
<th>First Name</th>
<th>Last Name</th>
<th>company</th>
<th>employeetype</th>
<th>Manager Name</th>
</tr>
<% int count=Integer.parseInt("${ANALYTICSDATA.size()}"); for(int i=0;i<count;i=i+1){%>
<tr>
<td>${ANALYTICSDATA.'VendorUsername'[i]}</td>
<td>${ANALYTICSDATA.'First Name'[i]}</td>
<td>${ANALYTICSDATA.'Last Name'[i]}</td>
<td>${ANALYTICSDATA.'company'[i]}</td>
<td>${ANALYTICSDATA.'employeetype'[i]}</td>
<td>${ANALYTICSDATA.'Manager Name'[i]}</td>
</tr>
<% } %>
</table> <br></body>

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

What is global config set for 

Group emails by username

When this option is enabled, the analytics emails sent during control execution will be grouped based on the username else every user will receive separate emails for every line item


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rajsannidhi
New Contributor III
New Contributor III

SK - Enabling Advanced HTML CSS worked. Thank You

Rushikesh - Yes, I enabled group email by username. Thank You

It is working now; Single Email is being sent to respective manager with all records in one email