There were recently two different threads in LinkedIn that discussed reporting on ‘multi-select’ or ‘multi-value’ fields. A multi-value field allows the user to enter more than one value, or a list, in a single field. But these discussions are usually a muddle because the term only describes how the data is entered or how it appears on the screen. But, to plan a report using multi-value fields you have to know how the data is stored which determines how it appears in a report. I know of four different methods for storing multi-value fields, so the first step is making sure you know how the data is stored.

A true multi-value field violates proper database design rules. When you need to store a list of items related to the same row the correct approach is to use another table that provides a one-to-many relationship. Some applications will do this and make it appear on the screen as a single, multi-value list, but others take different approaches. Below are the options that I am aware of.  As an example, think of assigning one, two or three categories to each order.

1) One field stored in multiple records, all in a separate “child” table.
This is the “correct” approach according to database theory.  You can have unlimited values this way but you get duplicate records when you bring in this table.  For our example you would have 3 records.  Each would have the order ID, and each record would have a different category in the category field.

2) Different fields stored in one record of the same table.
I have seen this presented on the screen as a multi-value field, even though each slot has a separate field in the database record.  This eliminates inflation because there is only on record. But that also means that you won’t be able to group the records independently without a little trickery.  And, this approach will only allow a fixed number of slots for you to fill.  For our example there would be 3 extra fields in the order table called CategoryA, CategoryB, CategoryC.

3) One field stored in one record.
This is a true multi-value field. Here the list is stored as items separated by commas or semi-colons. This was how it was done in Lotus Notes years ago. This also eliminates inflation because there is only on record. But again, you won’t be able to group the records independently.  Depending on how this is done the list may only allow a limited number of values. For our example there would be 1 extra field in the order table called Category and in it you would see all three values separated by commas.  Something like:   parts, service, support

4) MS Access Hybrid. This displays as if it is option 3 but in the background stores the data as option 1. I have never run into this one.

So before you can create reports on this data you have to know which storage option they are using.  To do that put the field or fields on the report and preview.  If there is only one field with delimiters than you have option 3.  If there are several different fields side-by-side then you have option 2.  If the data is all in one column on separate rows, then you have option 1.