TESTED IN BUILDS: 14700 (Postgres)
QUERY 1 - Without Account Additional Fields:
select ad.ORG_NAME As "Account",
org.description AS "DESCRIPTION",
ad.LOGIN_WEBURI AS "LOGIN WEB URL",
ad.LOGIN_URI AS "LOGIN URI",
ad.SUPPORT_EMAIL AS "SUPPORT EMAIL",
ad.SENDERNAME AS "SENDER NAME",
imdef.NAME AS "Business Impact",
inattr.ATTRIBUTENAME AS "ATTRIBUTENAME",
inattr.ATTRIBUTEVALUE AS "ATTRIBUTEVALUE",
post.COUNTRY AS "Country/Region",
post.DOOR_NO AS "Door",
post.STREET AS "STREET" from PortalAccounts pa
LEFT JOIN AccountDefinition ad on pa.ACCOUNTID = ad.ORG_ID
LEFT JOIN SDOrganization org ON ad.ORG_ID = org.ORG_ID
LEFT JOIN ci ci on pa.ciid = ci.ciid
LEFT JOIN instanceattributes inattr on ci.ciid = inattr.ciid
LEFT JOIN BaseElement be on ci.ciid = be.ciid
LEFT JOIN ImpactDefinition imdef on be.IMPACTID = imdef.IMPACTID
LEFT JOIN SDOrgPostalAddr org_post ON org.ORG_ID=org_post.ORG_ID
LEFT JOIN AaaPostalAddress post ON org_post.POSTALADDR_ID=post.POSTALADDR_ID
OUTPUT:
QUERY 2 - With Accounts Additional Fields (Admin > Account Additional Fields):
There might be multiple account additional fields based on the configuration. To list all these additional fields in the report, please follow the below steps,
>> Use the below query under Reports > New Query Reports. You can find the Account additional fields column names using the below query,
select attributes.attributeid,attributes.attribute,attributes.typeoffield,attributes.columnname from citype left join citypeattribute on citype.typeid = citypeattribute.citypeid left join attributes on attributes.attributeid = citypeattribute.attributeid where citype.typename = 'Account'
>> Make a note of the Attribute (which is the additional field name) and coresponding columnname. Add all the entries in following query like aci.columnname as "attribute". For example: aci.ATTRIBUTE_301 as "additional field", (Please refer to the highlighted area)
select ad.ORG_NAME As "Account",
org.description AS "DESCRIPTION",
ad.LOGIN_WEBURI AS "LOGIN WEB URL",
ad.LOGIN_URI AS "LOGIN URI",
ad.SUPPORT_EMAIL AS "SUPPORT EMAIL",
ad.SENDERNAME AS "SENDER NAME",
imdef.NAME AS "Business Impact",
inattr.ATTRIBUTENAME AS "ATTRIBUTENAME",
inattr.ATTRIBUTEVALUE AS "ATTRIBUTEVALUE",
aci.ATTRIBUTE_301 as "additonal filed",
post.COUNTRY AS "Country/Region",
post.DOOR_NO AS "Door",
post.STREET AS "STREET" from PortalAccounts pa
LEFT JOIN accountci aci on pa.ciid=aci.ciid
LEFT JOIN AccountDefinition ad on pa.ACCOUNTID = ad.ORG_ID
LEFT JOIN SDOrganization org ON ad.ORG_ID = org.ORG_ID
LEFT JOIN ci ci on pa.ciid = ci.ciid
LEFT JOIN instanceattributes inattr on ci.ciid = inattr.ciid
LEFT JOIN BaseElement be on ci.ciid = be.ciid
LEFT JOIN ImpactDefinition imdef on be.IMPACTID = imdef.IMPACTID
LEFT JOIN SDOrgPostalAddr org_post ON org.ORG_ID=org_post.ORG_ID
LEFT JOIN AaaPostalAddress post ON org_post.POSTALADDR_ID=post.POSTALADDR_ID;
OUTPUT:
In this way, multiple Additional Fields can be added.