Sorting report by a field with multiple values

[iamcalm]

I hope I can explain this in writing. Here goes...I have a data field that can contain multiple values. It's the department(s) field. Users may enter an individual dept value or multiples (separated by commas - ie. 24,26,59). I need to generate a report that summarizes the amount of records that have each dept within a certain time period. Data could look as follows:

record 1 - dept = 24,36,59
record 2 - dept = 24
record 3 = dept = 57, 59, 99
etc.

I can't figure out how to summarize this data since the field allows multiple values.

In the above scenario, I would want a result of:
dept 24 2
dept 36 1
dept 57 1
dept 59 2
dept 99 1

Could someone please tell me how to separate the values in this field so it summarizes properly? Thanks a lot for your help!

 

[lbass]

Do you have a small number of departments? You could create a formula for each department like the following {@dept24} and place in the details section:

if inStr({dept},"24&quot > 0 then 1 else 0

Then for each formula, you'd need to create a running total {#totaldept24}, in which you do a count of a unique field, e.g., customerID, evaluate on change of the following formula:

{@dept24} = 1

Reset never or on change of field or group, depending upon your needs. The running totals would have to go in a footer.

There might be an easier way using variables that someone else could help you with.

-LB

 

[synapsevampire]

It's best to correct the data by moving it into a separate value for each record, but you might also try initializing a numeric array with 0's, the number of elements being the maximum dept (assuming there are less than 1000).

@reportheader fromula
whileprintingrecords;
global numbervar array TheValues[1000];
numbervar Counter;
For Counter := 1 to 1000 do(
TheValues[Counter] := 0
);

Then in the Details add 1 to the value of the respecctive array element

@detailsformula
whileprintingrecords;
stringvar array TableValues := split({table.dept},",&quot
global numbervar array TheValues;
numbervar CellValue;
numbervar counter := 0
For counter := 1 to ubound(TableValues) do(
CellValue := val(TableValues[counter])
TheValues[CellValue] := TheValues[CellValue]+1
);

Now the array TheValues contains the count.

Now display in the report footer using as many formulas as is required to get through them all, which is version dependent, which you did not share, here's an example of one to handle all of them (if you have CR 9 this should work, you didn't share any database or CR version specifics which is a shame.):

@reportfooterFormula1
whileprintingrecords;
global numbervar array TheValues;
stringvar array DispValues[1000];
numbervar dispcounter;
numbervar counter := 0;
For counter := 1 to ubound(TableValues) do(
If tablevalues[counter] > 0 then
DispValues[ dispcounter]:= "Dept "+ totext(counter,0,"&quot + " " + val(TableValues[counter]);
);
join(dispvalues,chr(13))

I couldn't test the syntax right now, but the logic is sound.

-k

 

[iamcalm]

Thanks to you both for your suggestions. My report is now working correctly!