Recently, I was presented with an issue by a client using multi-picklist fields to better cover all topics their sales assistance team was covering with customers during support calls. When time had come to report on these topics, it was not possible to create a simple report to break-out each topic and summarize by monthly support topics (or options in multi-select picklist field) covered with customers. Even though Salesforce.com enables users to create multi-select picklists directly from the user interface, they do not offer organic functionality for reporting on these types of fields. In order to break out the multi-select picklist field to report on each option selected, we created formula fields for each option (or “topics” in this case) to summarize through reporting.
In this specific case, we will use the “ Includes() “ functionality to get what we want, so for each option in your picklist field, you will have to create a formula field to breakout that specific option.
For example, we have a multi-select picklist value for “Support Topics”. The options under support topics are: Product questions, Order Status, General Inquiry, & Credit Process:
NOTE: (For multi-select picklists with numerous options, it may be worth looking at a field type change such as check boxes or multiple (single) picklist fields due to the amount of custom formula fields that may be required and limits on the number of functions that can be carried-out using formulas)
So we need to create the individual formula fields for the options.
1. Select on create custom field, then Data Type: Formula
2. Enter your field label and name. Example below as YourFieldName & Your_Field_Name
The formula Return Type: Number, and select “0” for Decimal Places.
3. Create your formula fields using the below syntax for each option in the multi-select picklist. **Note: this formula includes the base syntax not related to sample case**
Formula Field 1: For Option #1
IF(INCLUDES(Support_Topics__c, ‘Product Questions’), 1, NULL)
**IF(INCLUDES(My_MultiPicklist__c, ‘Option 1’), 1, NULL)**
Formula Field 2: For Option #2
IF(INCLUDES(Support_Topics__c, ‘Order Status’), 1, NULL)
**IF(INCLUDES(My_MultiPicklist__c, ‘Option 2’), 1, NULL)**
Formula Field 3: For Option #3
IF(INCLUDES(Support_Topics__c, ‘General Inquiry’), 1, NULL)
**IF(INCLUDES(My_MultiPicklist__c, ‘Option 3’), 1, NULL)**
Formula Field 4: For Option #4
IF(INCLUDES(Support_Topics__c, ‘Credit Process’), 1, NULL)
**IF(INCLUDES(My_MultiPicklist__c, ‘Option 4’), 1, NULL)**
Please make sure that all fields are “readable” for those who would like access to the reports/summarizations.
4. When creating the desired report, make sure to click and sum all columns you want totals (it should look something like below report in simple tabular format.)
(Note: in Support Topics Column you can see the actual multi-select picklist values, and in following formula field columns you can see them broken out and available to summarize.)
Now that you have your totals summarized by options in the picklist, you should be good for reporting on the option break-downs in multi-select fields in Salesforce.com!
For more information on C5 Insight or this blog entry, please Contact Us.
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.