Query to Get a List of User Profile Attributes

Use Case

User has a requirement to create a report which returns a list of user profile attributes and their values.

Resolution

TIBCO JasperReports^® Server stores profile attributes details in internal database, called repository.

In order to retrieve the information, you can create a connection to that database (called jasperserver by default) and use a query like this one:

select 
    u.username, u.fullname, t.tenantid, t.tenantname,
    pa.attrname, pa.attrvalue, pa.description
from 
    jiprofileattribute pa, jiuser u, jitenant t
where 
    pa.principalobjectclass = 'com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser'
    and pa.principalobjectid = u.id and u.tenantid = t.id

The query returns user details, tenant(organization) information and profile attributes data.

Please find attached sample report to demonstrate the idea and the report output:

Solution tested with TIBCO JasperReports^® Server v.7.1.0

Ref. Case 01660508

support_usersattributes.jrxml