As of this writing, Salesforce provides no way of reporting on who belongs to which queues. To know who is a queue member, you must either (a) go to the Salesforce Setup page to manage the queue or (b) run a SOQL query and join on groupmember object then export the data using Data Loader or Enabler for Excel. But wouldn’t it be great if there was an easier way? Perhaps a visualforce page to let you browse members and provide an export to Excel button? Read on for the solution and code.

(A) Queue Setup Page
queuesetuppage.PNG

(B) SOQL Example

    SELECT  
         id, name, username, isActive  
    FROM  
         user  
    WHERE  
         id IN ( SELECT userOrGroupId FROM groupmember WHERE groupId = :groupId )  
    ORDER BY  
         name

The Solution: Visualforce and Apex

Inspired by Chris Kress‘ post asking how to write a SOQL query to get a list of users assigned to queues, I put together some basic visualforce pages to demonstrate how to use the query and to conveniently export user records to Excel. The source code and deployment options are available on my github repository: https://github.com/DouglasCAyers/sfdc-queue-members-excel. It contains a controller class for executing the SOQL to get queues and users, a test class with 100% code coverage for easy deployment into production orgs, a visualforce page for selecting a queue and viewing its members, and a visualforce page that renders the same queue members list in Excel.

The great thing is that this requires no special libraries or third-party code, it’s all standard Salesforce functionality! Just too bad actually reporting on queue members isn’t a standard report option…


public class QueueMembersController {
// selected queue whose members to view
public ID queueId { get; set; }
// provide queue name to show on page
public Group selectedQueue {
get {
return [
SELECT
id, name
FROM
Group
WHERE
id = :queueId
];
}
}
// list of all active queue members
public List<User> queueMembers {
get {
return [
SELECT
id, firstName, lastName, alias, username, email, userRole.name, profile.name
FROM
User
WHERE
id IN ( SELECT userOrGroupId FROM GroupMember WHERE groupId = :queueId )
AND
isActive = true
];
}
}
/**
* Controller
*/
public QueueMembersController() {
queueId = ApexPages.currentPage().getParameters().get('queueId');
}
public PageReference refresh() {
return null;
}
// picklist of queues whose members to view
public List<SelectOption> getQueueOptions() {
List<SelectOption> options = new List<SelectOption>();
options.add( new SelectOption( '', '– Select -' ) );
for ( Group grp : [
SELECT
id, name
FROM
Group
WHERE
type = 'Queue'
ORDER BY
name
] ) {
options.add( new SelectOption( grp.id, grp.name ) );
}
return options;
}
}

view raw

QueueMembersController.cls

hosted with ❤ by GitHub


<apex:page controller="QueueMembersController" title="Queue Members" sidebar="false" docType="html-5.0">
<apex:form id="mainForm">
<apex:pageBlock id="mainPageBlock" title="Queue Members">
<apex:pageMessages />
<apex:pageBlockButtons>
<apex:commandButton value="Export Details"
onclick="window.open('{!URLFOR($Page.QueueMembersExcelPage, null, [ queueId = queueId ])}');"
rendered="{!(queueId != null)}"/>
<apex:commandButton value="Manage Queue"
onclick="window.open('{!URLFOR("/p/own/Queue/d", null, [ id= queueId, setupid = "Queues" ])}');"
rendered="{!(queueId != null)}"/>
</apex:pageBlockButtons>
<apex:pageBlockSection>
<apex:pageBlockSectionItem>
<b>Queue:</b>
<apex:selectList value="{!queueId}" size="1">
<apex:selectOptions value="{!queueOptions}"/>
<apex:actionSupport event="onchange" action="{!refresh}" reRender="mainForm"/>
</apex:selectList>
</apex:pageBlockSectionItem>
</apex:pageBlockSection>
<apex:variable var="rowCounter" value="1"/>
<apex:pageBlockTable value="{!queueMembers}" var="user" rendered="{!(queueId != null)}">
<apex:column headerValue="#">
<apex:outputText value="{!rowCounter}"/>.
<apex:variable var="rowCounter" value="{!VALUE(rowCounter) + 1}"/>
</apex:column>
<apex:column headerValue="Name">
<apex:outputLink value="/{!user.id}">
<apex:outputText value="{0} {1}">
<apex:param value="{!user.firstName}"/>
<apex:param value="{!user.lastName}"/>
</apex:outputText>
</apex:outputLink>
</apex:column>
<apex:column value="{!user.alias}"/>
<apex:column value="{!user.username}"/>
<apex:column value="{!user.email}"/>
<apex:column value="{!user.userRole.name}" headerValue="Role"/>
<apex:column value="{!user.profile.name}" headerValue="Profile"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>

view raw

QueueMembersController.html

hosted with ❤ by GitHub


<!–
How to create excel file in visualforce.
http://help.salesforce.com/apex/HTViewSolution?id=000003176&language=en_US
–>
<apex:page controller="QueueMembersController" contentType="application/vnd.ms-excel#QueueMembersExport.xls" cache="true">
<apex:pageBlock >
<apex:pageBlockTable value="{!queueMembers}" var="user">
<apex:column headerValue="Queue" value="{!selectedQueue.name}"/>
<apex:column value="{!user.firstName}"/>
<apex:column value="{!user.lastName}"/>
<apex:column value="{!user.alias}"/>
<apex:column value="{!user.username}"/>
<apex:column value="{!user.email}"/>
<apex:column value="{!user.userRole.name}" headerValue="Role"/>
<apex:column value="{!user.profile.name}" headerValue="Profile"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:page>

view raw

QueueMembersExcelPage.html

hosted with ❤ by GitHub

(Originally published on BMC Communities at https://communities.bmc.com/people/douglascayers/blog/2014/07/05/how-to-export-queue-members-to-excel)