Need to Create Analytics for Runtime User

[Sunny]

Hi Team,
We have a requirement where the logged-in user should be able to view the Analytics Report, which will consist of data including user details for users who are part of an organization where the logged-in user is an Organization Owner.

The above requirement query has been configured; however, we need an enhancement to check if the logged-in user has Application A (suppose endpointkey=41) and if in Application A, the customproperty1 equals 'ABC_XYZ'. If this condition is met, the analytics should be created through the Utility, which is already in place.

I have tried to merge the above query with customproperty1 details; however, when I tried, I was not able to fetch the total records. The query was fetching details in such a way that only users with customproperty1 as 'ABC_XYZ' were appearing in the result. The query was not checking the logged-in user's Application A customproperty1.

The Existing Query:

SELECT u.username as USER_ID, 
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME, 
u.email as EMAIL,
u.phonenumber as PHONENUMBER,
u.enddate as ENDDATE, 
u.owner as MANAGER,
c.customername as ORGANIZATION_ID,
c.displayname as ORGANIZATION_NAME, 
u.customproperty4 as POSTAL_CODE,
u.customproperty5 as MOBILE_NUMBER, 
(select distinct dv.ATTRIBUTE2 from dataset_values dv where dv.DATASETNAME = 'Supported_Language' and dv.ATTRIBUTE1 = u.customproperty1) as PREFERRED_LANGUAGE,
u.customproperty2 as USER_BRANDS, u.comments as USERCOMMENTS FROM users u JOIN customer_users cu on cu.userkey=u.userkey JOIN customer c on cu.customerkey=c.customerkey where c.customerkey IN (select customerkey from organization_owners where userkey=${user.id})

Enhance Query with wrong output:

SELECT u.username as USER_ID, 
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME, 
u.email as EMAIL,
u.phonenumber as PHONENUMBER,
u.enddate as ENDDATE, 
u.owner as MANAGER,
c.customername as ORGANIZATION_ID,
c.displayname as ORGANIZATION_NAME, 
u.customproperty4 as POSTAL_CODE,
u.customproperty5 as MOBILE_NUMBER, 
(select distinct dv.ATTRIBUTE2 from dataset_values dv where dv.DATASETNAME = 'Supported_Language' and dv.ATTRIBUTE1 = u.customproperty1) as PREFERRED_LANGUAGE,
u.customproperty2 as USER_BRANDS, u.comments as USERCOMMENTS FROM users u JOIN customer_users cu on cu.userkey=u.userkey JOIN customer c on cu.customerkey=c.customerkey Join user_accounts ua on ua.userkey=u.userkey Join accounts a on a.accountkey=ua.accountkey where c.customerkey IN (select customerkey from organization_owners where userkey=${user.id}) and a.ENDPOINTKEY=41 and a.customproperty1='ABC_XYZ'

[NM]

Hi @Sunny make a join in subquery to check endpointkey and customproperty 1

Subquery I am referring to

IN (select customerkey from organization_owners where userkey=${user.id}) 

[Raghu]

@Sunny  check below query

SELECT
u.username as USER_ID,
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME,
u.email as EMAIL,
u.phonenumber as PHONENUMBER,
u.enddate as ENDDATE,
u.owner as MANAGER,
c.customername as ORGANIZATION_ID,
c.displayname as ORGANIZATION_NAME,
u.customproperty4 as POSTAL_CODE,
u.customproperty5 as MOBILE_NUMBER,
(SELECT DISTINCT dv.ATTRIBUTE2
FROM dataset_values dv
WHERE dv.DATASETNAME = 'Supported_Language'
AND dv.ATTRIBUTE1 = u.customproperty1) as PREFERRED_LANGUAGE,
u.customproperty2 as USER_BRANDS,
u.comments as USERCOMMENTS
FROM
users u
JOIN
customer_users cu ON cu.userkey = u.userkey
JOIN
customer c ON cu.customerkey = c.customerkey
WHERE
c.customerkey IN (SELECT customerkey
FROM organization_owners
WHERE userkey = ${user.id})
AND EXISTS (SELECT 1
FROM application_users au
WHERE au.userkey = ${user.id}
AND au.endpointkey = 41
AND au.customproperty1 = 'ABC_XYZ')