Using multi-picklist data in Reports

posted Feb 25, 2011, 9:07 AM by Ezra Kenigsberg   [ updated Mar 26, 2011, 3:06 PM ]

#DataTipOfTheDay 2011-02-25

Q:    "How do I show Multi-Select Picklist (MSP) results in a Report?"

A:    If you've worked with MSPs, you've probably seen this:
The problem is that individuals with multiple favorite teams show up strangely (and shame on them for rooting for both Chicago teams. Gotta pick a side). Here's how I fix this:
    1. Create a new text field named "[MSP] Text". Make it a 255-character text field. It doesn't need to be visible to any users or shown on any Page Layouts.
    2. Create a Trigger to copy the value from "[MSP]" to "[MSP] Text". I've uploaded the sample Trigger and Test Class I used for this example--feel free to customize them for your own needs.
    3. Create a custom formula field for each value in the MSP.* Assuming each MSP value is named [Value1], [Value2], etc, I name each formula field "Count: [Value1]", "Count: [Value2]", etc. The formula for each field is:
      IF(FIND("[Value1]", [MSP Text]) > 0, 1, 0)
    4. Change the report from a Matrix to a Summary.
    5. Add each of the formula fields created in step 3 to the report, showing the sum for each field. My results look like the picture below. Notice that the Cubs and White Sox total each increased by one.

    . . . and hey: did you know Spring Training games have started?