Entitlement Owner Campaign - Entitlement Value Account

[Isyraf]

Hi Support Team,

We're experiencing a problem when trying to create analytic report for entitlement owner campaign. 

-We are only able to list out entitlement value with account and unable to include entitlement values that have no account linked to them.

-Is it possible to display only a single entitlement value on the analytic report instead of multiple values based on the account linked to them. We already tried from our end but still not able to get that.

Kindly assist on this matter. You may refer to the attached picture.

Thank you,

We want to retrieve endpoint and certifier action for entitlement values that have no account linked to them. We also want to display only a single entitlement value no matter how many accounts linked to them on the analytic instead of multiple values that based on the account linked to them.

[dgandhi]

Paste the query that you are trying.

[Isyraf]

Hi,

The analytic report for this data is 'Campaign Certifier Action Report' and please find the attached query.

Thank you,

[nimitdave]

do outer join on certification_account_entitlement1_status and certification_entitlement_value instead of inner as that will not give entitlements with no accounts in reports. or you can do union with other query only certification_entitlement_value with no exists on certification_entitlement_value.

And for accounts in same row as entitlements you can try with group_concat mysql function but that will make account column very clumsy as 1 entitlement can have hundreds to thousands accounts

[Isyraf]

Hi,

Thank you for the reply. I already try to outer join the table but still showing the same result. We want to retrieve endpoint and certifier action for entitlement values with account  and also without account linked to them. We also want to display only a single entitlement value no matter how many accounts linked to them on the analytic instead of multiple values that based on the account linked to them. Basically, we want to display what inside the attached picture. we also attached the query here. 

Hoping for your kind assistance on this matter.

Thank you,

SELECT
u.USERNAME AS 'Certifier Username',
u.FIRSTNAME AS 'Certifier First Name',
u.LASTNAME AS 'Certifier Last Name',
cm.campaign_name AS 'Campaign Name',
c.CERT_NAME AS 'Certification Name',
endp.ENDPOINTNAME AS 'Endpoint',
cev.ENTITLEMENT_VALUE AS 'Entitlement Value',
CASE
WHEN aces.certified = 1 THEN 'Retained'
WHEN aces.certified = 0 THEN 'Revoked'
ELSE 'No Response'
END AS 'Certifier Action',
c.CREATEDATE AS 'Certification Start Date',
c.ENDDATE AS 'Certification End Date'
FROM
campaign cm
INNER JOIN certification c ON cm.id = c.CAMPAIGNKEY
INNER JOIN users u ON c.certifier = u.USERKEY
INNER JOIN certification_account_entitlement1_status aces ON aces.certkey= c.certkey
LEFT OUTER JOIN certification_entitlement_value cev ON aces.CERT_ENTITLEMENT_VALUEKEY = cev.CERT_ENTITLEMENT_VALUEKEY
INNER JOIN entitlement_values ev ON cev.ENTITLEMENT_VALUEKEY= ev.ENTITLEMENT_VALUEKEY
INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
INNER JOIN endpoints endp ON et.endpointkey = endp.endpointkey
WHERE
cm.campaign_name = 'Application Role Review Campaign'