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)

salesforce.com multiselect picklist

 

So we need to create the individual formula fields for the options.

1. Select on create custom field, then Data Type: Formula

Reporting on Multi-Select Picklists in Salesforce.com

 

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.

Reporting on Multi-Select Picklists in Salesforce.com

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.)

Reporting on Multi-Select Picklists in Salesforce.com

 

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! Thumbs up

 

For more information on C5 Insight or this blog entry, please  Contact Us.