kind of like a cross tab - or formula

[jessieyull]

I have some data that I am reporting on for parts that includes description type columns and pricing columns.

If I bring back all the fields in the usual mannner af placing each of them in the detail section, I get multiple rows for each part as it returns each of the column prices.

I am wanting to take each of the price column headers up to the header area and have the corresponding price for each part print in its correct price column.

I attempted this by creating formula's such as...

if {PRICING_EXTRACT_V.DISPLAY} like "1-24"
then {PRICING_EXTRACT_V.PRICE}

This worked, as it would bring back one row for the part price equal to the 1-24 column. But it would also return the additional rows where the display did not equal 1-24 and would put zeros in for all of the prices.

Just trying a cross tab didn't seem to work as I had other detailed columns to return for each part.

Below are examples of what I am trying to get to.

If anyone could help - that would be great!
Thanks!

CURRENT FORMAT
name display price
part1 1-24 58.5
part1 25-99 11.7
part1 100-499 8.35
part1 500-999 7.8
part2 1-24 53.5
part2 25-99 14.7
part2 100-499 6.35
part2 500-999 5.8

IDEAL FORMAT
name 1-24 25-99 100-499 500-499
part1 58.5 11.7 8.35 7.8
part2 53.5 14.7 6.35 5.8

 

[kenhamady]

I am not clear on what fields you actually have available, but here is a shot.

How about a cross-tab that uses NAME as the row field and DISPLAY as the column field (I assume this is a field). The summarized field would be PRICE using the MAXIMUM operation. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jessieyull]

I was hoping that it would be just as easy as you described - using a crosstab. But my two issues I ran into there are...

1) I can't figure out how to suppress subtotalling on all the fields I am displaying. This report would not need any totals fields.

2) For some unknown reason, when I use the cross-tab wizard, one of my most important fields is no longer a choice for me to use. It shows up as a database field any other way I try to use the table within Crystal - just not with the crosstab.

Thanks for your help!

 

[MrBillSC]

Your formula above seems correct, but will return zeros in the empty columns in the detail section. I am assuming this is a pricing table that contains only one record for each cell in your cross tab table. If this correct, you might try creating a group on name, then subtotal your formula columns at that group. If that works, then supress printing the detail section.

good luck.

 

[kenhamady]

The field that doesn't show up is probably a memo field or a large VarChar. These can only be displayed, not used for any ohter feature in the report. faq149-276 may shed some light.

Using the Maximum operation with your price will simply show you the price without subtotaling it. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jessieyull]

yep, its a VarChar (description field) so that must be the problem. Thanks for the Maximun operaton tip too.

-jessie