Need formula to move price breaks to 3 columns

[grecon]

I am doing a product report and I am showing my item number, description and price. There are 3 prices for each item due to quantity breaks as yyou see below. I want to get the the report so that the item number and description only shows once and the prices are shown as price 1 2 and three (3 columns. Is there a formula I can use to create this?
ITEM DESCRIPTION PRICE
5-AL1218 ALABAMA REFLECTIVE HANDICAPPED $36.75
5-AL121 ALABAMA REFLECTIVE HANDICAPPED $35.00
5-AL121 ALABAMA REFLECTIVE HANDICAPPED $33.00

5-AR1218 ARKANSAS REFLECTIVE HANDICAPPED $36.75
5-AR1218 ARKANSAS REFLECTIVE HANDICAPPED $35.00
5-AR1218 ARKANSAS REFLECTIVE HANDICAPPED $33.00

5-AR3-1 REFLECTIVE NO RIGHT TURN SYMBOL $69.75
5-AR3-1 REFLECTIVE NO RIGHT TURN SYMBOL $66.00
5-AR3-1 REFLECTIVE NO RIGHT TURN SYMBOL $63.00

5-AR3-1HI 24" HIGH INTENSITY SYMBOL $78.75
5-AR3-1HI 24" HIGH INTENSITY SYMBOL $74.50
5-AR3-1H 24" HIGH INTENSITY SYMBOL $70.75

I want it to look like this:
ITEM DESCRIPTION PR1 PR2 PR3
5-AR3-1HI 24" HIGH INTENSITY SYMBOL $78.75 $74.50 $70.75

 

[FireGeek21]

PRICE_1 = MINIMUM({TABLE_NAME.PRICE_FIELD})

PRICE_3 = MAXIMUM({TABLE_NAME.PRICE_FIELD})

PRICE_2 =
IF {TABLE_NAME.PRICE_FIELD} < {@PRICE_3} AND {TABLE_NAME.PRICE_FIELD} > {@PRICE_1}
THEN {TABLE_NAME.PRICE_FIELD}

Hope this helps!


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[FireGeek21]

You can also use Median for PRICE_2

PRICE_2 = MEDIAN({TABLE_NAME.PRICE_FIELD})


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[briangriffin]

You can do this by building an array - place this formula in the detail section:

whileprintingrecords;
currencyvar array v_array;
numbervar v_counter;
redim preserve v_array[v_counter];

v_array[v_counter] := {Price Field};
v_counter := v_counter + 1;


You'll need a reset formula in the group header:

whileprintingrecords;
currencyvar array v_array;
numbervar v_counter := 1;
redim v_array[1];
v_array[1]


Then you'll need three formulas in the group footer to display the amounts:

whileprintingrecords;
currencyvar array v_array;
v_array[1]

Change the 1 to 2 or 3 for the other two formulas.

This can be streamlined a bit if there are always three prices, but this should work as is.

 

[grecon]

I am a bit confused my table name is {PRICELEVELS.PRICE} and I see where to plug that in but what is @price_1 I don't have that in my report. Where does the PRICE_1 come from? All my prices are in {PRICELEVELS.PRICE} they don't distinguish between price 1 2 and so on but the prices do go according to {PRICELEVELS.MINQTY} forgot to mention that, my MINQTY are 0 13 and 25. Also doens't the formula have to start with something like IF? Sorry I am not good with formulas. Thanks

 

[FireGeek21]

Formulas don't necessarily have to start with IF.

When you reference another Crystal Formula it presents itself with the @ symbol infront of the formula name. This is why I listed PRICE_1 and PRICE_3 first before addressing PRICE_2. You need to set up the formulas before calling them in another formula.

I posted a second item... you can use MEDIAN({TABLE_NAME.PRICE_FIELD})




RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[grecon]

ok I GOT IT TO PUT THE PRICE IN THE DIFFERENT COLUMNS BUT HOW DO I GET IT TO JUST BE ON ONE LINE, IT REPEATS THE ITEM 3 TIMES AS YOU SEE:

5-AR3-1 REFLECTIVE NO RIGHT SYMBOL 69.75 0.00 0.00
5-AR3-1 REFLECTIVE NO RIGHT SYMBOL 0.00 66.00 0.00
5-AR3-1 REFLECTIVE NO RIGHT SYMBOL 0.00 0.00 63.00

5-AR3-1H 24" HIGH INTENSITY NO RIGHT 78.75 0.00 0.00
5-AR3-1HI24" HIGH INTENSITY NO RIGHT 0.00 74.50 0.00
5-AR3-1HI24" HIGH INTENSITY NO RIGHT 0.00 0.00 70.75

5-AR3-2 REFLECTIVE NO LEFT SYMBOL 69.75 0.00 0.00
5-AR3-2 REFLECTIVE NO LEFT SYMBOL 0.00 66.00 0.00
5-AR3-2 REFLECTIVE NO LEFT SYMBOL 0.00 0.00 63.00

5-AR3-2HI24" HIGH INTENSITY NO LEFT 78.75 0.00 0.00
5-AR3-2HI24" HIGH INTENSITY NO LEFT 0.00 74.50 0.00
5-AR3-2HI24" HIGH INTENSITY NO LEFT 0.00 0.00 70.75

I want it all on one line.

 

[FireGeek21]

Put your fields into the footer section and then suppress the detail section.


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[grecon]

That didn't work it only shows one item

 

[FireGeek21]

Sum each of the price fields - you should see each then.


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

Please reread Brian's post. After creating the array, you use three separate fields in the group footer that each reference one array element.

-LB