【Question】

I am working on a Jasper report in which I am using a field “RACE_CODE” which contains values like ‘abc’,‘lmn’,‘pqr’,‘xyz’. For one person there can be multiple RACE_CODE assigned to it.

So I want the output in term of four digits like:

If a person has RACE_CODE ‘abc’ and ‘xyz’ then it should display ‘1001’ and so on.

I can use conditional statement like:

$F{RACE_CODE} == ‘abc’ && $F{RACE_CODE} == ‘xyz’ ?

‘1001’ : ($F{RACE_CODE} == ‘abc’ ? ‘1000’ : (so on…

But this is not a feasible way because for four digits I will be having 16 combinations.

In practice I have 7 race codes. So it’s not possible to have so many combinations in the default_value_expression clause.

Is there any other way in which I can achieve this? Please help to solve this.

What I want is given in the pseudo code below:

if RACE_CODE CONTAINS (‘abc’,‘pqr’)

then display 1010

if RACE_CODE CONTAINS (‘lmn’,‘pqr’,‘xyz’)

then display 0111

and so on

…

【Answer】

There’s a [pid, race_code] table, where the codes are fixed values in an alphabetical order and where one person points to multiple codes. You want to convert each person’s codes to a string consisting of 1 and 0. The rule is: a code in a certain position is recorded as 1 in the string at the corresponding position if one person has it; otherwise the character in the position is 0.

The source table:

There are 5 types of string code. abc is in position 1, def in position 2, lmn in position 3, pqr in position 4, and xyz in position 5. The person with pid being 1 should correspond to code 11000. The algorithm is this: group rows in the source table by pid, align the race_code values in each group to a sequence containing the unique string codes in alphabetical order, and then translate codes for each person into the code in specified format according to the sequence. Here I express the algorithm in SPL (Structured Process Language):

A1: Connect to test database.

A2: Retrieve rows from the source table as a table sequence.

A3: Define the sequence of ordered strings by which the codes for one person are aligned.

A4: Group A2 by pid and calculate code column values. align() function aligns race_code values in each group to A3’s sequence, record a string at the corresponding position as 1 if it appears and as 0 if it doesn’t exist, and then join the characters up into a string.

A4