Grouping on Runtime 1

[ulicki]

Hi,

I am using Crystal Reports 7.

I have a report that needs to be grouped on price ranges. However the ranges are not determined until the data is returned to the report. The grouping occurs as follows. First find the lowest price of the record set returned and multiply it by 1.5. This is the first price range. Now find the next price greater than the high price of the first range. This becomes the low price of the second range. Multiply this by 1.5 and this is the high price of the second range. Keep applying this formula until all prices fall within a range.

Example
Low price is 10. So first range is 10 to 15.
Lets say next price greater than 15 is 20. So next range is 20 to 30.
Say the next highest price is 40. So next range is 40 to 60.

I don't think there is any way to do this via just using Crystal Reports designer. I was thinking maybe find the ranges in the main report and pass them to a subreport, and have the subreport run for each range.

Any help would be appreciated.

 

[lbass]

First place your price field ({table.price}) on the report canvas and sort it in ascending order (report->sort order). Then create a formula {@range}:

whileprintingrecords;
numbervar counter := counter+1;
numbervar minrange;
numbervar maxrange;
stringvar output;

if counter = 1 then minrange := {table.price};
maxrange := 1.5 * minrange;
if {table.price} <= maxrange and
next({table.price}) >= maxrange then
counter := 0;
if {table.price} in minrange to maxrange then
output := totext(minrange,2)+" to "+totext(maxrange,2);

If you place this next to {table.price} in the details section, you will see the price ranges you wanted. You cannot group on this formula, but you can create the appearance of groups by inserting another detail section. Place {table.price} in detail_b, and {@range} in detail_a. Right click on {@range}->format field->common->check "suppress if duplicated". Then go to the section expert (format->section)->detail_a->check "Suppress blank section". This will give you a report that looks like this:

23.80 to 35.70
23.80
27.00
33.90
39.15 to 58.73
39.15
48.51
53.90
62.33 to 93.50
62.33
etc.

If you want to do calculations within these fake groups, you can use the running total editor, where you use the following formula in the reset section:

whileprintingrecords;
numbervar counter = 0;

You can then count records or add prices, etc. within these "groups".

-LB

 

[ulicki]

Wow, cool, thank you, and of course another question. I should have put this information in the original post, but I really was expecting to be told it could not be done.

Lets say I am reporting on blocks.

My report needs to print one line for every block that is the same color, same shape, made on the same date, and is the same price. These lines would be summary lines and each line would have a sum of the number of blocks with these like characteristics along with the characteristics (see example below).

So I am thinking I can use your logic to incorporate these requirements.

I am thinking I could group the report by Price, Date, Color and Shape (in that order) and put my totals in the Shape(Group 4). Now my group 4 line items can be considered the "Details" of the report and I can place your formulas/logic in Group 4. The price range would go in the group 4 header, the "Details" in the group 4 footer.

Next for each price range group I need to sum the number of blocks for all the "Details" in the group. Here is an example of how it would look.

Price Date Color Shape # of Blocks
23.80 to 35.70
23.80 2/5/02 Red Trangle 125
23.80 2/5/02 Red Square 598
27.00 2/5/02 Green Circle 102
Total for Range 23.80 to 35.70 825

39.15 to 58.73
etc
etc

So my question is: Is my logic / reasoning on the right track?

Thank you,

Mike

 

[lbass]

I would do it like this, using my previous suggestion and then also adding the following. This involves creating just one group:

Create a formula {@concat}:

totext({table.price},"0000.00") + " "+ totext({table.date},"yyyy/MM/dd")+ {table.color}) +" "+{table.shape}

Make sure that you format the price with the maximum number of places that can occur to the left of the decimal so that it will sort correctly. Insert a group on {@concat}, and in the section expert, suppress both the group header and footer. Next create a formula {@sumqty}:

sum({table.qty},{@concat})

Place {@sumqty} in detail_b instead of the quantity field, along with your other detail fields. (If there is no quantity field, then use:

count({@concat},{@concat})

...instead.)

Next insert a detail_c section. Using the running total editor, create a running total {#grpsum} by selecting {table.qty}, sum (or {@concat},count), evaluate for each record, reset on change of formula:

whileprintingrecords;
numbervar counter = 0;

Place the running total in detail_c. Then go to the section expert (format->section) and format detail_a->suppress->x+2 and enter:

whileprintingrecords;
numbervar counter <> 1

Then select detail_b and enter:

{@concat} = previous({@concat})

Then select detail_c->suppress->x+2 and enter:

whileprintingrecords;
numbervar counter <> 0;

-LB

 

[ulicki]

LB, excellent thank you so much for your help