Reporting on Multi Select fields

Are you trying to add the Value Field from the selected option? If so, what syntax are you using to get the Value Field to pull rather than the Value Label? This is a guess but I think the multi select values are technically text values and not number values, so maybe that is why the SUM function is not working? Kristy Musgrove Havertys

Hi Rick, How you set up your calculation depends on the whether you are change the each choice's value (5) or leave it the same as the label (Product A). Here are a couple examples. Number OR Text Format without values In this case, the Format of the multi-select is Text and each choice's label is the same as the value. The format doesn't matter as much here for this formula (see notes below). (I named my field Relevance1) In your calculated field, I recommend you change the format to Number. Then use this formula: SUM(IF(CONTAINS("Product A",Relevance1),5),IF(CONTAINS("Product B",Relevance1),4),IF(CONTAINS("Product C",Relevance1),3),IF(CONTAINS("Product D",Relevance1),2),IF(CONTAINS("Product E",Relevance1),1)) Notice that you'll need to add an option in the SUM() for each of the available choices in the Relevance1 field. SUM(choice 1, choice 2, choice 3, choice 4, choice 5). Number OR Text Format with values For this example, the format of the multi-select is Number and the choice values are different than the choice labels. The calculation is pretty much the same, but with one change in the formula. (I named this field Relevance2) Then in your calculated field, change the format to Number. Then use this formula: SUM(IF(CONTAINS(5,Relevance2),5),IF(CONTAINS(4,Relevance2),4),IF(CONTAINS(3,Relevance2),3),IF(CONTAINS(2,Relevance2),2),IF(CONTAINS(1,Relevance2),1)) Just like above, you need to add an IF(CONTAINS()) statement in the SUM() for each of the available options from the Relevance1 field. SUM(choice 1, choice 2, choice 3, choice 4, choice 5). Notes: If either field (multi-select or calculated field) are formatted as Text you cannot Aggregate that field up to groupings within a report (Sum, Average, Count, etc.) If the multi-select field is formatted as Number you CAN Aggregate, but because the field is a multi-select field, it will only look at the first value like you were seeing when you were trying to SUM("your fields name"), and the aggregation ignores the rest. If the calculated field's format is Number you CAN Aggregate. The multi-select format can be changed once the form it is on is closed, but the calculated field format cannot. Dan Perkins Rego Consulting

Thanks Dan - I'm set up like your second option with the value saved as a number. I was fearing the nested IF/CONTAINS. Makes it a bit more difficult to add options to the selectable list. I'll just need to remember to add it to the list and the IF/CONTAINS calculation as well. :( Rick MacDuffie Symetra Life Insurance Company