Crystal report; Combining rows of data into a sing

Question

I have a data in rows like this for id 1

id1 a1

id1 b2

id1 c3

id1 d4

id1 e5

I like to combine it as "a1b2c3d4e5" into a single value for id1. Thanks in advance.

Answer 1

It will depend on the data type for the columns a1-e5 and what you're trying to do with the single value.

If you're just trying to display the value, you can create a formula that uses Crystal's ToText() function to first convert them to strings and then concatenate them together using '+'.

ToText({id1.a1}) + ToText({id1.b2}) + ToText({id1.c3}) + ToText({id1.d4}) + ToText({id1.e5})

You could accomplish the same thing on the DBMS side, also.

If you can provide more info, we might be able to come up with a better solution.

Answer 2

So, you want to concatenate data from multiple rows into a single row for display? Crystal Reports really isn't built for that kind of thing. Even if you could, I'd still suggest doing that server-side first, then feeding it into Crystal.

It won't be too difficult, a simple loop through the data in the language of your choice. But Crystal is built for displaying and formatting data, with a decent (but not extensive) set of data manipulation tools. I don't think it's the best way to go.

Answer 3

One approach is the "3 Formula" method. You set up an initialising formula in each header, with the evaluation time directive "WhilePrintingRecords". This approach was the only one available for doing running totals in the "good ol' days" before RunningTotal objects were available.
For example:
In the Group Header :- Have a Formula @InitiliseRT

WhilePrintingRecords;
StringVar ConcatenatedID;
If Not InRepeatedGroupHeader Then
    ConcatenatedID := "";

In the Details Section :- Have a Formula @UpdateRT

WhilePrintingRecords;
StringVar ConcatenatedID := ConcatenatedID + id1;

Finally, in the Group Footer you can see the result :- Formula @ShowRT

WhilePrintingRecords;
StringVar ConcatenatedID;

This should give you your final string of "a1b2c3d4e5".

If you need to, you can add extra formulas for extra groups, one variable for each group (e.g. ConcatenatedIDGroup1, ConcatenatedIDGroup2). The key is to always get the name right between the groups, and to initialise the variable in the group header.
There are limitations to the use of these formulas. The built in summary functions (Sum, Max, Count, ...) or RunningTotals cannot use them, and you cannot group on them.