concatenate fields into one single row 2

[mhs377]

Hi
Given the following data being sent to Crystal....
ID Val
1 A
1 C
1 F
2 C
2 B

...can it be displayed as follows?
ID Val
1 A, C, F
2 C, B

I have searched and tried this formula but it did not work correctly

(Group Header section)
shared stringvar b;
b := '';

(Detail section)
whileprintingrecords;
shared stringvar b;
if not IsNull({DaySheetReport;1.Code}) then
(
if (b = '') then
b := b + {DaySheetReport;1.Code}
else
b := b + ', ' + {DaySheetReport;1.Code}
)


(Group Footer section)
shared stringvar b;
b

it shows the data like this

1 A
1 A,C
1 A,C,F
2 C
2 C,B

What is the problem with the code?

thanks

 

[Madawc]

The problem is in the second line of your detail line formula

(Detail section)
whileprintingrecords;
shared stringvar b;
if not IsNull({DaySheetReport;1.Code}) then...

This requests a display before adding the next details. I think it should be

(Detail section)
whileprintingrecords;
if not IsNull({DaySheetReport;1.Code}) then...

Failing that you might right-click and use Format Field to suppress the field.

There is also no need to use Shared Variables unless you are taking data from a subreport. But that just wastes resource, it still works.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

The problem is that you are displaying the detail section. if you place your display formula:

whileprintingrecords;
shared stringvar b;

...in the group footer and suppress the details, you should see the correct results.

-LB

 

[mhs377]

thanks for your answer lbas
there is another problem
when I have one table it works but when I use fields from two tables it does not work
I mean this:
tbl1 tbl2 tbl3
ID Val1 ID Val2 ID Val3
1 A 1 G 1 Z
1 C 1 H 1 W
1 F 2 L 2 M
2 C 2 J 2 N
2 B 1 K 1 O

when I want to use two fields from two different tables (or more) it does not work
how should I change the codes?

 

[lbass]

What results would you expect to get based on the three sets of table data in your last post?

How do these fields display after linking the tables and placing the fields in the detail section?

-LB

 

[mhs377]

the result must be like this:

ID Val1 Val2 Val3
1 A,C G,H,K Z,W,O
2 C,B L,J M,N
but it displyed like this:
ID Val1 Val2
1 A,C G,G,H,H,K,K

 

[lbass]

I would write the formulas like this:

//groupheader:
whileprintingrecords;
stringvar b;
if not inrepeatedgroupheader then
b := "";

//Detail section:
whileprintingrecords;
stringvar b;
if not IsNull({DaySheetReport;1.Code}) then
(
if not({DaySheetReport;1.Code} in b) then
b := b + {DaySheetReport;1.Code}+","
) else
b := b;

//groupfooter;
whileprintingrecords;
stringvar b;
if len(b) > 1 then
left(b,len(b)-1)

Repeat this logic for each variable (one per column).

-LB

 

[mhs377]

thanks a lot
it works

but it shows the data from IDs that has at one of three fields(Val1,val2,Val3) if I want to show the data from all IDs (that may not have any value for those three fields) what should I do?

thanks in advance

 

[lbass]

Do those IDs show up in the detail section? If they don't you will need to explain how you are linking the tables and also show the content of your selection formula.

-LB

 

[mhs377]

Oh my god
the link was left inner join
it was the problem
thank you very much

Your tips were very helpful
I have worked on that for days but I could not solve it

thanks again

 

[jemsmom]

I've used the formulas posted by lbass and it works perfect. Thank you lbass.

But... I need to take it one step further and provide the total number of records for the group footer formula results. I exported to excel and used a pivot table, but if I can avoid excel and do this in Crystal. Is there a way to accomplish this?

I would like the output to look like:

Scheduled CPT Codes Total
0078T,37205 10
01990 1
10060 5
10060, 20680 1

Thank you!

 

[lbass]

Are you saying you need a distinct count of records because codes are duplicating? If so, add another variable to the reset and accum formulas:

whileprintingrecords;
stringvar b;
numbervar cnt;
if not inrepeatedgroupheader then (
b := "";
cnt := 0
);

whileprintingrecords;
stringvar b;
numbervar cnt;
if not IsNull({DaySheetReport;1.Code}) then
(
if not({DaySheetReport;1.Code} in b) then (
b := b + {DaySheetReport;1.Code}+",";
cnt := cnt + 1
));

Then reference the count in a separate formula for the footer:

whileprintingrecords;
numbervar cnt;

-LB

 

[jemsmom]

I apologize for not being clear, please let me try again.

What I want is to be able to group by the string I've concatenated together "Scheduled CPT Codes" and provide the total number of times the same unique string of cpt codes was used. Currently my report is grouped by the patient with a detail line of one or more rows with one cpt code for each procedure a patient has scheduled and I am concatenating them together in the patient group footer.

 

[lbass]

I see what you are saying, but you can't group on a variable. I think you would have to set up add a string array variable that adds unique group footer values to the array, and then add a numbervar array that counts the matching values per array element. I don't have the skill with arrays to easily provide the specifics. Maybe someone else can.

-LB

 

[jemsmom]

LB - Thank you for your time.