Creating Summary for Complex Text Field 1

[betachristopher]

I need to create summaries for text fields that contain data similar to the following:

998563; 458632; 164696; 886543 x2

Each record will have a field with similar data, but each of these numbers that are separated by a semicolon need to be summrized separately. Also, if there is an x2 or some other number, that indicates the number should be counted that many times. And since there are multiple users entering this data, it may sometimes appear as 154969x3 or 154969 x 3 or 154969 x3.

How can I create a summary this complex?

I am using Crystal Reports XI and pulling the data from a SQL server.

 

[lbass]

You haven't explained what the summary is. Is it across records or within the field itself? Please show a sample of the data and the results you would expect to see.

-LB

 

[betachristopher]

Does this help?

I am trying to summarize accross records. A record set will look similar to the below.

ID USER DATE RESULTS
A-1-1 JOHN 12/1/2007 998563; 458632; 164696; 886543 x2
A-1-2 JOHN 12/1/2007 998563; 458632; 899654 x4
A-1-3 JOHN 12/1/2007 456953; 158975
A-1-4 JOHN 12/1/2007 745953; 569645

The records are grouped by user, so I need a summary at the end of each group for the RESULTS field similar to the below.

JOHN
RESULT TOTAL
158975 1
164696 1
456953 1
458632 2
569645 1
745953 1
886543 2
899654 4
998563 2

Plus I need an overall summary for the RESULTS field from all users at the end of the report.

 

[lbass]

Can you clarify whether these codes are a limited, standard set or whether there could be a wide range of numbers?

-LB

 

[betachristopher]

There are probably less than 100 codes in use right now, but the list has the potential to grow. These codes are not stored in a data table right now, but we could possibly do that if it makes this easier.

Thank you for your time.

 

[lbass]

Here's a method for getting the group totals. Create three formulas:

//{@reset} for the group header:
whileprintingrecords;
stringvar y := "";
stringvar z := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar array w := split({table.results},";");
stringvar array v := "";
stringvar array x := "";
stringvar y; //unique values
stringvar z; //all values
numbervar i := 0;
numbervar j := ubound(w);
redim preserve w[j];
redim preserve v[j];
redim preserve x[j];
for i := 1 to j do(
v := replace(w," ","");
if not(v in y) then
y := y + left(v,6) + ","
;
if "x" in v then
x := replicatestring(left(v,instr(v,"x")-1)+",", val(mid(v,instr(v,"x")+1,1))) else
x := v;
z := z + x+","
);

//{@display} for the group footer:
whileprintingrecords;
stringvar y;
stringvar z;
stringvar array m := split(y,",");
stringvar array n := split(z,",");
numbervar array cnt := 0;
local numbervar i := 0;
local numbervar j := ubound(m);
local numbervar k := 0;
local numbervar p := ubound;
numbervar q := 0;
redim preserve cnt[j];
stringvar display := "";

for i := 1 to j do(
for k := 1 to p do(
if m in n[k] then
cnt := cnt + 1
));
for i := 1 to j do(
if not (m in display) then
display := display + m + " "+totext(cnt,0,"")+ chr(13)
);
display

Format {@display} to "can grow".

-LB

 

[betachristopher]

Thank you so much for this. This looks much more in depth than anything I've done so far in Crystal, but I will try it right away and let you know how it works. Hopefully I can go through it and understand everything that is going on also.

 

[betachristopher]

Hi lbass,

I need to re-visit this. The code has been working great, but the field it is counting is getting more complex.

It has been working fine for records with data displayed like this: 998563; 458632; 164696; 886543 x2

But sometimes there are records with data displayed like this: 88173 x1; TP=88112x1 or this CPT CODE: 99242x1, 10022x2, 88172x2, 88173x2, 88112x2

Also, a few of the records had commas instead of semi colons separating the data that needed totalling. I created the following formula to somewhat standardize the data:

if {Result.Result-Value} like "*TP=*" then
replace(replace({Result.Result-Value},"TP=",""),",",";") else
if {Result.Result-Value} like "CPT CODE:*" then
replace(replace({Result.Result-Value},"CPT CODE:",""),",",";") else
replace({Result.Result-Value},",",";")

Now this month, some of the records were displayed like this: CB=88305 x1. And some records have nothing in them but the word None, so of course I get a total for None.

I really don't want to keep modifying my formula everytime someone enters something different. And we don't have a programmer here that can modify their program to force consistent data.

Is there a way to create a formula that will remove any character except for the numbers and the "x" that represents the mulitplier?

 

[lbass]

You really need to get the users to standardize the data entry, as there is a limit to how much compensating you can do with code.

You could test the elements within the string (like "CB=88305 x1") by using a formula like this:

stringvar b := "CB=88305 x1"; //put in the array element here
stringvar c := "";
stringvar d := "";
local numbervar i;
local numbervar j := if instr(b,"x") > 0 then
len(trim(left(b, instr(b,"x")-1))) else
len(b);
if instr(b,"x") > 0 then
c := split(b,"x")[1] else
c := b;
for i := 1 to j do(
if isnumeric(c) then
d := d + c
);
d

This will return the number before the "x" if it exists, or else all numbers in the element.

-LB

 

[betachristopher]

You are super quick to reply as always. You are correct, we need standards, and their manager is going to go over it with them again.

I will try this formula to see what I can do with it. Thank you so much.