Hello,

I'm creating a report where there is a "fixed" BY column" that shows a sales point range that needs to appear in every section of the report regardless of having data or not for a particular department, sub-department or class:

SALES RANGE (PP_DESC) SALES
0.01 - 50.00
50.01 - 100.00
100.01 - 150.00
150.01 - 200.00
200.01 - 250.00
|
|
|
10,000.01 - 9,999,999.99

The report is broken down by Division, Department, Sub-Department, and Class. I can get all the "sale ranges" to show at the Division level, but once I started going to the lower levels, I can only get some of the "sale ranges" to show. I've tried joins and MATCH, but can't get it to work. I've also have a DEFINE for each range using logic that includes "IS MISSING", but no luck either.

PP_DESC/A50 MISSING ON = IF ((PP_ID EQ 1) AND (SUBDPTID IS MISSING OR SUBDPTID_DESC IS MISSING)) THEN PRICE_POINT
ELSE
IF ((PP_ID EQ 2) AND (SUBDPTID IS MISSING OR SUBDPTID_DESC IS MISSING)) THEN PRICE_POINT
ELSE ....

Appreciate any suggestions you might have. Thanks

Luiz

This message has been edited. Last edited by: Kerry,

Read the fabulous manual section on "Creating Financial Reports", you'll find new uses for FOR and OVER. Also, referred to as FML (and before that FRL). Here's an example:

TABLE FILE CAR
SUM CNT.SALES SALES
BY COUNTRY
FOR SALES
0     TO  9999 AS '    0- 9999' LABEL R1 OVER
10000 TO 19999 AS '10000-19999' LABEL R2 OVER
20000 TO 29999 AS '20000-29999' LABEL R3 OVER
30000 TO 39999 AS '30000-39999' LABEL R4 OVER
40000 TO 49999 AS '40000-49999' LABEL R5 
ON TABLE SET PAGE NOLEAD
END

Advantage is the missing data will always show a row.

Disadvantage is you must hardcode the ranges.

Note that ranged grouping is based on detail sales value.

If you are using the FOR statement, you can have dialog manager generate the rows, with some quick analysis of the data in a prior table file.

Thanks guys for the suggestions. I'll try FML first, and see if works for the report.

Waz, do you have an example of using FOR and DM together? Thanks

L

Here is an example expanding on Daves one.

TABLE FILE CAR
SUM MIN.SALES
    MAX.SALES
ON TABLE SAVE AS SAV_SALE
END
-RUN
-READ SAV_SALE &MIN_SALES.A6. &MAX_SALES.A6.

-TYPE Minimum Sales &MIN_SALES
-TYPE Maximum Sales &MAX_SALES

-SET &BLOCK_SIZE = 10000 ;
-SET &FOR_BLOCKS = &MAX_SALES / &BLOCK_SIZE ;
-TYPE Blocks = &FOR_BLOCKS

TABLE FILE CAR
SUM CNT.SALES SALES
BY COUNTRY
FOR SALES
-SET &Over = ' ' ;
-SET &Cntr = 0 ;
-SET &FOR_END = &FOR_BLOCKS - 1 ;
-REPEAT FOR_LOOP FOR &Cntr FROM 0 TO &FOR_END ;
-SET &From = &BLOCK_SIZE * &Cntr ;
-SET &To   = &BLOCK_SIZE * (&Cntr+1) - 1 ;
&Over &From TO &To AS '&From - &To' LABEL R&Cntr
-SET &Over = 'OVER' ;
-FOR_LOOP
ON TABLE SET PAGE NOLEAD
END

Waz,

I was able to get the logic to work using some FML code. Thank you very much for the example though.

This issue has been resolved.

Luiz

Waz, Nice dynamic example!

Thanks Dave,

But it still is a quick one.