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.
(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 , 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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!– | |
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> |
Good Article.. Will data loader work for creating new queue and adding members to queue
LikeLiked by 1 person
Hi Nagdeep. Yes, data loaders can be used to insert/update/delete queues and queue members. The API objects are known as Group and GroupMember. “Queues” are simply Group records of type = “Queue”. https://www.salesforce.com/developer/docs/api/Content/sforce_api_objects_group.htm
LikeLike
How to export Queue Role and public group users?
LikeLike
Hi Azar,
The Group sobject has a picklist field named Type. “Queue” is just one of the many values. This blog post demonstrated how to query for group members that belonged to a group of type “Queue”. You can just as easily do similar query to get group members of a “Public Group” or “Role” by changing the query criteria.
Check out this documentation for more information about the Group object and the different Type picklist values: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_group.htm
LikeLike
Hi Doug,
Great Post..
similarly can you please how can we export more than 10k records using visual force page
LikeLiked by 1 person
Hi Raja,
Thanks for the kind feedback. Good question. By default, Salesforce limits the number of rows a collection can iterate on a visualforce page to 1,000 items. You can use the readOnly=true attribute on your apex:page tag to increase the limit to 10,000 rows. Beyond 10,000 rows you probably need to look at a custom integration to export the data such as with the Apex Data Loader or ETL tool like MuleSoft or Talend.
https://developer.salesforce.com/docs/atlas.en-us.196.0.pages.meta/pages/pages_controller_readonly_context_pagelevel.htm
Doug
LikeLike
Doug, I set this up in my sandbox, it works great but I was wondering if there was a way to make the Picklist on the VF Page, a Multiselect.
Reason: If there were Queue Owners that wanted to export all of their queues and it’s members in 1 swoop, they could select all their queues and export them in one shot. OR, there may be some managers (or admins) that want to export ALL queues w/ members. Either having an ‘All’ option or a multiselect.?
Whatcha think? π
Oh, one more thing… haha, I know if anyone can, you can… have a ‘lookup’ to Active Users (with BMCServiceDesk Staff check box = True) next to the Queue picklist.
Reason:
Maybe folks want to know ‘All the Queues that Doug is a member of”, They select ‘Doug’ and all the queues that he is a member of, will appear.
Whatcha think? π
LikeLiked by 1 person
Hi Mike, thanks for the kind feedback!
Those are some great ideas and use cases. Would you mind adding those as separate enhancement requests on the github repository please so I can track them? You may do so here: https://github.com/DouglasCAyers/sfdc-queue-members-excel/issues
Question, if manager selected 2+ queues to export members, would you expect a user to be listed once or as many times as they were members of the selected groups? So if Doug belonged to Queues A and B, and I choose to export both A and B’s members, is Doug listed once or is Doug listed twice with the queue name in another column?
Regarding the filtered lookup on BMCServiceDesk Staff checkbox (or any other criteria) is doable. The native look-up pop-up would likely get replaced by a custom autocomplete widget fetching data from the Apex controller. That would make it easier for other developers to tweak the filtering logic as needed in their org by simply updating the SOQL query.
Good ideas, thanks for sharing!
Thanks,
Doug
LikeLike
HI Doug-
I know this is an old article, but it’s a great idea and has been quite helpful. We are having issues giving it out to end users who don’t have system admin rights though. They have the visualforce pages and controller, but get an insufficient access message. Is there some hidden permission involved I’m missing?
LikeLiked by 1 person
Hi Megan, I see that Chirag Mehta was able to solve your question on the Success Community. Glad you all got this working =)
LikeLike