Comma Separated List to new rows

[Cardstang]

Hello all.

I'm not sure how to word the subject in a way that makes sense, so I apologize if it's confusing.

I have a report that contains a string field that lists anywhere from zero to 25 or more items of information. These items are separated by a comma.

What I need is a way to make a new row in my report for each item that is listed.

Here is an example of the data and how it is today:

Ticket  Location  Item
123     New York  A1, B2, C3
234     Chicago   A2
345     Atlanta   B3, C4, D5, E6

And here is how I'd like it to appear:

Ticket  Location  Item
123     New York  A1
123     New York  B2
123     New York  C3
234     Chicago   A2
345     Atlanta   B3
345     Atlanta   C4
345     Atlanta   D5
345     Atlanta   E6

I'm using CR 2008.

Thanks in advance for the assistance!

 

[SkipVought]

hi,

If you have no options in CR and if you have Excel available, this can be done in just a few minutes. makes no matter how many columns (comma separated values). Took me about 2 minutes to get this, using a little known process...
[tt]
Row Column1 Value
123 New York A1
123 New York B2
123 New York C3
234 Chicago A2
345 Atlanta B3
345 Atlanta C4
345 Atlanta D5
345 Atlanta E6
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Cardstang]

I'm not able to use Excel in this report. Hoping to find a solution from within Crystal.

 

[RTag]

What is your database ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[Cardstang]

It's a Remedy database using an OBDC connection.

My report fields are like this:

{Table.Field}

This particular field is a string field.

 

[lbass]

Can you explain why you want to do this? There is a way to get this display using multiple detail sections, but it might not be the right approach depending upon your ultimate goal.

-LB

 

[Cardstang]

The "Item" field is actually device names. The multiple devices can be listed in a single ticket. I need to get a count and trend on how many tickets each device has had opened on it.

It will be ongoing and automated, therefore manipulating it in Excel isn't a good option at this point. There are thousands of tickets and devices each week.

Thanks!

 

[lbass]

Do you happen to have a table that contains all device names as single values in the device name field?

-LB

 

[Cardstang]

No, there is no table containing those devices. The field is a free text field and the values are separated by commas.

 

[lbass]

I'm not sure whether this would work with Remedy, but try creating a table in Excel or Access that has a field "DeviceName" with all 25+ possible instances. Use this table in the main report and group on the field. Then add a subreport in the group header that is linked on the DeviceName field. In the subreport, use your Remedy table(s) and add a selection criterion like this:

{?pm-excel.devicename} in {table.textfield}

Add the Remedy fields to the detail section and add the desired sort fields.

By using subreports you can access the same row of data multiple times (once for each instance of device name).

-LB

 

[Cardstang]

That's really not an option. The list of devices is not static. Also, the device names are used multiple times in different areas. There may be a device name of A1 in New York, Chicago, and Atlanta. I'll combine the device with another field to create a unique ID.

Thanks for trying to help. I wasn't sure if what I was asking was possible or not.

I may have to resort to an Excel solution.

 

[lbass]

I understood that the device names were used multiple times, but not that the devices changed over time.

You could insert multiple detail sections up to the maximum number of devices, e.g., 30. Then create one formula per section like this:

//{@detail_a}:
stringvar array x := split({table.text},",");
if ubound(x)>=1 then
x[1]

//{@detail_b}:
stringvar array x := split({table.text},",");
if ubound(x)>=2 then
x[2]

//{@detail_c}:
stringvar array x := split({table.text},",");
if ubound(x)>=3 then
x[3]

Insert the same subreport (renamed to match the section) in each detail section, but change the links to match the formula in each section, e.g., {@detail_b}. In each sub, change the record selection formula to:

{?pm-@detail_a} in {table.text} //or {?pm-@detail_b}, etc.

Then in each sub you can insert counts or group by location, etc.

-LB