How do you create a range report with subtotals?

[Elliot Jolesch]

I have a statement that has about 675 lines that I have imported into FM7.

I am trying to create a report that will should the number of records (lines) based on different dollar ranges.

Example:

Range Records Amount

0 - 100 539 $14,250.00

101 - 200 78 6,350.00

201 - 500 45 8,550.00

> 501 8 9,450.00

Total 38,600.00

I need some guidance/suggestion on how to make this work.

Thank you.

Elliot

[spb]

I'm still an FM 6 guy, so it's possible that 7 does this stuff differently. But if I wanted to get the result you describe in FM 6, I'd do this:

Make a calculation field used for sorting, call it RangeSort. This field does not have to be visible on any layout. The calc would be something like:

Case (Range < 101, "A",

Range < 201, "B",

Range < 501, "C",

"D")

(The Case function will evaluate in order, so if Range is 150, it will fail the first case, move to the second, succeed, and not evaluate the third.)

Make a subsummary part that breaks on this new field, then sort on the new RangeSort field. Make a summary field that adds up the amounts and place this field in both your subsummary part and in your trailing grand summary. Make another summary field does does Count instead of Total, and place in both parts.

If you needed to display the physical range "0-100, 101-201, etc." you might try to replace these strings with my A, B, C, D, but the sort might get out of kilter. You could make another calc field for display purposes that decodes the latters back to the actual ranges:

Case (RangeSort = "A", "0-100',

RangeSort = "B", "101-200",

RangeSort = "C", 201-500",

>501)

(but what if the number is exactly 501?)

Place this second calc field, RangeDisplay, in the subsummary part, using RangeSort only for sorting purposes. Make a script that will do the sort & take you to the layout and it all happens with one button clicked.

This will be useful for printing only, though. You can't see the summary parts in browse mode. You could make your script go into preview mode, but this is clumsy, forcing the user to continue past a pause to get back out of the mode.

Steve Brown