Grouping by multiple values in one record (multi-value field)

Tuesday 10 December 2013 @ 12:42 pm

One of my customers asked me if there was a way to group records by product code. The tricky part was that each record contained several product codes entered as a list like this:

A101, B201, C301

Fortunately, I have run into this requirement before. One time it was primary and secondary sales reps stored in two separate fields in the same record. Another was a list of characteristic keywords stored in a “multi-value field”.  The challenge is getting Crystal to show one record in multiple groups at the same time. Normally this isn’t possible. But I have found a technique that allows this to happen by duplicating the original record as many times as needed and then assigning each duplicate to a different element from the list. It takes some work so if you need to use this it might make sense to call for some support.  But here are the steps:

1) Determine the maximum elements that can appear in a single record. In the sales rep example there were always 2. In the product codes example we had to allow for up to 65. You can pick any number but there are trade-offs. If you pick too small a number you run the risk of missing elements when a list goes beyond that number. But higher numbers will slow down your report more than lower ones. This is because the first thing the report has to do is multiply the number of records that you start with by that number.

2) Find or create an ‘inflator’ table.  Assume the maximum number is 10. So you would need a table that has 10 consecutively numbered records. It could be 1 to 10 or it could be 57 to 67. Any consecutive series of numbers will work, as long as those records can be relied on to be there without changing. You can even use your selection formula to filter these records.  If they aren’t numbered 1 to 10 you will use a formula to adjust them so that the formula returns 1 to 10.  For example if I had 57 to 67 my formula would subtract 56 from the raw value and I would get 1 to 10.

3) While you are in design mode, add this table to the report.  Do not link it to other tables and ignore any Crystal Reports warnings you get.  With no link you will create a “cross-join” so that every current record in the original report is repeated 10 times. If your database driver won’t run without a join, you can try a ‘not equal to’ join type.  Use two fields that will never match as the join fields. Before you preview, add any selection criteria needed to limit the new table so that it returns only those 10 records.  If you add the consecutive field to the report you should get 10 times the number of records when you preview.  If you need a formula to turn these consecutive values into 1 to 10, create that here.

4) Create a formula that counts the number of values in the multi-value field. Assuming that the list is divided by comma the formula would look like this:

Count( Split ( {Your.ListField} , ', ' ) )

5) Add a selection criteria so that the inflator field is less than or equal to the count formula you created in number 4. So now the number of duplicate records for each original record will be equal to the number of elements in the field. A selection formula like this will work:

{inflator.key} <= {@CountFormula}

6) Write a formula field to extract one element for each duplicate record. You want the first element on duplicate record #1, the second on duplicate #2, etc.
It wil look something like this:

if {@CountFormula} >= {inflator.key}
then Split ( {FieldName} , ';' ) [ {inflator.key} ]
else ""

Now you can group on this last formula or use it in a cross-tab.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server