displaying 1 to many

[lunchbox88]

I have a report that shows information for products (product name, number, cost, sell, etc).

Each product can have one, or several, colors attached to it, with a quantity for each color.

So, if there are 4 colors attached to one product, I'll get 4 records returned for that product; one for each color.

Is there a way to dynamically display everything on one line? If not fully dynamic, with as little hard coding as possible? I do have a table that lists all the possible colors, and what order they should appear in on the report (they want to see them in a certain order).

Prod Num Prod Name Cost Sell Blue Green Red Yellow
1 test prod 1 2 3 2 3 4

I'm currently doing it with a sub report, but I can't get it to display like above. The best I can do is to get it to display like below:

Prod Num Prod Name Cost Sell
1 test prod 1 2 Blue: 3 Green: 2 Red: 3 Yellow: 4

Also, the only way I can figure to do totals at a group level is to use more subreports.

Also, I'm using the Crystal that is included with VS.NET 2k8 (I believe it's Crystal 2008 Basic).

Thanks,

 

[Charliy]

Group on the product. Suppress the Group Header and Detail line and put everything in the Group Footer. Create 4 Running Totals, one for each Color, and select Evaluate on Formula. For the Evalute formula put: {table.color} = "Green" Set the Running Total to Reset on Change of Group.

I couldn't tell from your example whether you would need to Count or Sum the data.

 

[lunchbox88]

Thanks for the reply.

I guess there isn't going to be a way for me to do it totally dynamically, huh?

Oh well.

Thanks again!

 

[Charliy]

You can do it with a CrossTab report.

You'll just need to play with that a little.