Hello All,

I received rather simple request that has me scratching my head. I have a report that lists the top 20 cities that our customers are located in. It has a layout like this:

City 1 1 5%
City 2 1 5%
City 3 1 5%
City 4 1 5%
City 5 1 5%
City 6 1 5%
City 7 1 5%
City 8 1 5%
City 9 1 5%
City 10 1 5%


And one of my users asked if I could display the report like this instead:
City 1 1 5% City 6 1 5%
City 2 1 5% City 7 1 5%
City 3 1 5% City 8 1 5%
City 4 1 5% City 9 1 5%
City 5 1 5% City 10 1 5%

What is the best way to approach this? All of the ways I can think of to make the report display like this seem overly complicated. My first thought was to hold the data with a LIST, then do a define file to group them based on whether or not their LIST value is 1-5 or 6-10. This seems too complicated, and I was wondering what other analysts do with WebFOCUS when your reports get too "long". Any and all feedback is appreciated!

This message has been edited. Last edited by: <Emily McAllister>,

You're right.
you're going to create a variable that you'll use as an ACROSS.
you can do this in a define or a compute, depending on how ordered your final data set is.
eg:
myvar/I1 = IF LAST myvar EQ 1 THEN 2 ELSE 1;
then
SUM CITY SOMETHING PERCENTAGEVALUE ACROSS myvar NOPRINT

Here is one approach.

If you are using something like BY HIGHEST 20 in your report program, you may get more than 20 cities when there are ties. This happens in the CAR example below, where BY HIGHEST 10 SALES returns 11 MODELs instead of 10. The -SET Dialogue Manager logic determines where to split the initial output into two columns. In any case, you need to determine both row and column positions for each set of values. If you know your output will always have exactly 20 cities, it becomes a little simpler - you can skip the Dialogue Manager logic and simply replace variable &COL_SWITCH with the number 10.

TABLE FILE IBISAMP/CAR
 BY HIGHEST 10 SALES
 BY MODEL
 ON TABLE HOLD AS CAR_DATA
END
-*
-RUN
-SET &RPT_LINES  = &LINES ;
-SET &HALF       = &RPT_LINES / 2 ;
-SET &WHOLE      = &HALF * 2 ;
-SET &COL_SWITCH = IF (&WHOLE EQ &RPT_LINES) THEN &HALF ELSE (&HALF + 1);
-*
DEFINE FILE CAR_DATA
 COUNTER/I5 = LAST COUNTER + 1 ;
 ROW_NBR/I5 = IF (COUNTER FROM 1 TO &COL_SWITCH) THEN COUNTER ELSE (COUNTER - &COL_SWITCH);
 COL_NBR/I5 = IF (COUNTER FROM 1 TO &COL_SWITCH) THEN 1 ELSE 2 ;
END
-*
TABLE FILE CAR_DATA
 SUM MODEL SALES
 BY ROW_NBR      NOPRINT
 ACROSS COL_NBR  NOPRINT
END

quote:

Originally posted by Dan Satchell:
Here is one approach.

If you are using something like BY HIGHEST 20 in your report program, you may get more than 20 cities when there are ties. This happens in the CAR example below, where BY HIGHEST 10 SALES returns 11 MODELs instead of 10. The -SET Dialogue Manager logic determines where to split the initial output into two columns. In any case, you need to determine both row and column positions for each set of values. If you know your output will always have exactly 20 cities, it becomes a little simpler - you can skip the Dialogue Manager logic and simply replace variable &COL_SWITCH with the number 10.

TABLE FILE IBISAMP/CAR
 BY HIGHEST 10 SALES
 BY MODEL
 ON TABLE HOLD AS CAR_DATA
END
-*
-RUN
-SET &RPT_LINES  = &LINES ;
-SET &HALF       = &RPT_LINES / 2 ;
-SET &WHOLE      = &HALF * 2 ;
-SET &COL_SWITCH = IF (&WHOLE EQ &RPT_LINES) THEN &HALF ELSE (&HALF + 1);
-*
DEFINE FILE CAR_DATA
 COUNTER/I5 = LAST COUNTER + 1 ;
 ROW_NBR/I5 = IF (COUNTER FROM 1 TO &COL_SWITCH) THEN COUNTER ELSE (COUNTER - &COL_SWITCH);
 COL_NBR/I5 = IF (COUNTER FROM 1 TO &COL_SWITCH) THEN 1 ELSE 2 ;
END
-*
TABLE FILE CAR_DATA
 SUM MODEL SALES
 BY ROW_NBR      NOPRINT
 ACROSS COL_NBR  NOPRINT
END

This is perfect, thank you!