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.
A:
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.
Ryan
2010-08-24 15:53:24
I almost upvoted your answer, but then I realized this wasn't her question. If it was, though, you answered it perfectly.
PowerUser
2010-08-24 16:59:52
Yep, I considered that but made the assumption it was a snafu of semantics and that it was more likely that id1 was a row/record.
Ryan
2010-08-24 17:14:32
Thank you for answering my question. Id1 is a customer id, this custome bought products a1,b2,c3 at differnt time etc. I like to know which customer who bought a1 also bought d4. many thanks
Helen
2010-08-25 14:30:44
A:
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.
PowerUser
2010-08-24 17:04:36
Thank you for your comment. I have no access to the server and I was trying to create an array or matrix to hold the data, then display them. But it has not been successful. Thank you for your help.
Helen
2010-08-25 14:35:29
An array in Crystal sounds like it might do the trick for what you're wanting to do... What trouble did you have with it?You could group by customer, initialize the array and a numbervar to keep track of the size in the header, and dynamically grow the array (Using REDIM PRESERVE) with each new product you encounter in the details. In the footer you will have an array of each product the customer has purchased.
Ryan
2010-08-25 15:30:23
A:
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 := "";
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.
Anthony K
2010-08-26 04:03:21