Please help with looping through records

[jhein]

The problem I am having is this...my user is seeing the below information on a report (there is a lot more info, but this should be good enough for an example)

Case Party D.O.B. Charge(s)
123 Smith, John 01/01/1973 124-A
123 Smith, John 01/01/1973 466-13C
123 Smith, John 01/01/1973 8673-A

Basically, my user wants me to be able to take the charges and separate them by commas instead of having them show up on each line (only wants to see one line for the case with the charges all together, separated by commas). I am pretty sure I can do this with a cursor in my stored proc, but I don't want to do that because my query is using dynamic sql and is already pretty complex. Is there a way to set up the case id as a group and based on that group loop through the records, taking the charges and stringing them together with commas? I am using Crystal Reports 9.0 Professional Edition.

Any help would be greatly appreciated...thanks in advance.

JHein

 

[lyanch]

in the report header:

global stringvar charges := "";

In the details:


global stringvar charges;

charges := iif(length(charges) > 0, charges, charges + ", "

charges = charges + {table.charges};

In the group footer

global stringvar charges := 0;


Lisa

 

[kenhamady]

There is a formula example that does this in faq149-243, and also prevents the string from hitting the length limit of 256 chars and generating an error. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[jhein]

I'm sorry, but I am kind of new at this...when you say put into report header, do you mean create a formula and put into the group header (same with the details and group footer)?

 

[jhein]

I did try the formula that you suggested Ken. I am having one issue with it though. I put the first formula in the group header, the second formula in the details and the third one in the group footer. If you refer to my original example, I am now seeing this on the report:

Case Party D.O.B. Charge(s)
123 Smith, John 01/01/1973 124-A
124 -A, 466-13C
124 -A, 466-13C, 8673-A

Then in the group footer I am seeing this 124 -A, 466-13C, 8673-A Is there something I am missing or not doing correctly?

 

[kenhamady]

The group footer is what you want to see, you suppress the Details and the Group Header. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[jhein]

As always, you have given me the answer that I have been looking for! Thanks for your time and knowledge.


JHein

 

[lyanch]

I didn't think that they would have to bother dealing with the 256 char problem since they are using crystal 9.

Is this not correct? I haven't started using 9 yet since it will be awhile before I can upgrade my CE to 9.

Lisa

 

[kenhamady]

You are correct, I missed the fact that it was v9. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com