Click HERE to see how Saviynt Intelligence is transforming the industry. |
12/09/2022 08:26 AM - edited 12/09/2022 08:28 AM
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.
Solved! Go to Solution.
12/09/2022 09:35 AM
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.
12/09/2022 10:07 AM
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
12/09/2022 10:09 AM - edited 12/09/2022 10:11 AM
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
12/09/2022 10:25 AM
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
12/09/2022 10:46 AM
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
12/09/2022 10:46 AM
Thank you SK, that worked.
12/09/2022 12:36 PM - edited 12/09/2022 12:37 PM
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
12/09/2022 01:21 PM
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
12/09/2022 02:05 PM - edited 12/09/2022 02:06 PM
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)
12/20/2022 01:16 PM - edited 12/20/2022 01:18 PM
${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
01/05/2023 11:22 AM
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
01/05/2023 11:35 AM
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
01/05/2023 11:41 AM
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
01/05/2023 12:37 PM
Its expected behavior when you filter data report won't be attached
01/05/2023 01:03 PM
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')
01/05/2023 02:27 PM
Are you getting values for all others expect vendorusername? and is it pulling all records?
01/06/2023 06:16 AM
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
01/06/2023 07:21 AM
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){%>
01/06/2023 01:36 PM
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>
01/06/2023 01:58 PM
Can you share the logs and also can you share the screenshot of template?
01/09/2023 10:50 AM
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.
01/09/2023 11:00 AM - edited 01/09/2023 11:01 AM
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>
01/09/2023 06:08 PM
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
01/10/2023 08:13 AM
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