Hi,

I have a report with one column of data that has about 300 rows in it. Is there a way to split the data from that one column into 5 columns each with 60 rows? Is there something equivalent to FOLD-LINES for columns?

Thanks,

Dan Pinault

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

Your best bet is to use a technique I commonly used in producing 3-up labels. You will basically define fields which will serve as X and Y for each row, and then sort by x and across y. I've seen similar code on the forum. My example is from way back from my early FOCUS years, and someone may be able to simplify it, but I also needed to control page-breaks as well as columns and rows.
This produces 3 columns of 53 records each, just like a newspaper column would read, with page-breaks.

DEFINE FILE FILENAME
XROW/I2=IF XROW GT 53 THEN 1 ELSE XROW + 1;
XCOLUMN2/I1=IF XROW EQ 1 THEN XCOLUMN + 1 ELSE XCOLUMN;
XCOLUMN1/I1=XCOLUMN2-((INT(XCOLUMN2/3))*3);
XCOLUMN/I1=IF XCOLUMN1 EQ 1 THEN 1 ELSE
           IF XCOLUMN1 EQ 2 THEN 2 ELSE
           IF XCOLUMN1 EQ 0 THEN 3;
XPAGE/I2=IF (XCOLUMN EQ 1 AND XROW EQ 1) THEN XPAGE + 1 ELSE XPAGE;
END
TABLE FILE FILENAME
PRINT FIELD1 AS ''
BY XPAGE NOPRINT
ACROSS XCOLUMN NOPRINT
BY XROW NOPRINT
ON XPAGE PAGE-BREAK
END

Dan

What would be the way to split it in 5 columns?

Suppose you have 300 products, give them two sequence numbers, one goes from 1 to 5 the second from 1 to 60. now you can present them by the second number and across the first.

Darin

IMHO your concept has one wrong line.
I would use SUM instead of PRINT.

Yes a label like report is the way to go, and if you use PRINT instead of SUM you get a rather strange looking report. Though I will admit the first time I used it I didn't have the experts out here to ask, then I discovered FOCUS-L list serve which is no more.

Hi,

I agree with Darin, define the columns by counter and SUM with ACROSS

Regards

Waz

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

Sure 'nuff. I went back to my code to see if I really had a print in there, and I had a WRITE! Old stuff! Anyway, that would be the equivalent of a SUM. I had changed that line to remove my fieldnames and just stuffed a PRINT back in there.

I used the code you provided to split the column into two columns. However, I cannot seem to figure out how to retain my css stylings for the second column. How do you reference the second column and rows?

Thanks,

slim007

Use the following code. The key is the ACROSSCOLUMN keyword. Change the word SALES in the stylesheet to be the field name of the data you are showing in the columns.

TABLE FILE CAR
SUM SALES
BY COUNTRY
ACROSS CAR
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SALES, COLOR=RED,$
ENDSTYLE
END

You Rock, Mickey! That seemed to fix the problem.

Thanks!

Stephanie

Here is a parameterized solution:

  
-* PP=No. of elements per page
-* CC=No. of cols; LL=No. of lines per col
-SET &LL=10;
-SET &CC=4;
-SET &PP=&LL * &CC;

DEFINE FILE FSEQ
PP/I3=(COUNTER - 1) / &PP;
CC/I2=(COUNTER - &PP * PP - 1) / &LL ;
LL/I2=COUNTER - &PP * PP - CC * &LL  - 1;
SP/A1=' ';
END
-RUN
TABLE FILE FSEQ
SUM COUNTER CHAR SP
BY PP NOPRINT PAGE-BREAK
BY LL NOPRINT 
ACROSS CC NOPRINT
ON TABLE SET NODATA ' '
END

the fseq file is my mcguyver file:

  
 FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\XFOCUS\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

Data:
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

Will ACROSS display a lot of empty 'cells' in the columns that are created that were not in the 'contiguous' list of 300 ?

Examples :


TABLE FILE CAR
HEADING
"Without Using ACROSS..."
"Produces 10 rows with 1 cell each = 10 cells"
SUM SALES
BY COUNTRY
BY CAR
END

TABLE FILE CAR
HEADING
"Using ACROSS..."
"Produces 5 rows of 10 cells each = 50 cells"
SUM SALES
BY COUNTRY
ACROSS CAR
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SALES, COLOR=RED,$
ENDSTYLE
END

I'm trying to accomplish the same thing as Dan Pinault, maybe using column breaks (as in MS Word) ?

Is there a command such as ON ROW-COUNT COLUMN-BREAK ?

Charlz,

1. Please update your signature

2. If you looked at Darin's example and at mine, you will see that the occurences of our records in the file are numbered. It is this number that allows the creation of columns and rows, NOT the values of fields. If you don't have a record number in your data base you can create it either with a DEFINE or by creating a HOLD file using the LIST verb and then applying the column/row paradigm to the generated record number.

See (and try) this example:

  
-* Charlz.fex
-SET &LL=5;
-SET &CC=4;
-SET &PP=&LL * &CC;
-* &LL=number of lines per page; &CC=number of columns per page
-*
TABLE FILE CAR
LIST SALES
RANKED BY COUNTRY
BY CAR
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
PP/I3=(LIST - 1) / &PP;
CC/I2=(LIST - &PP * PP - 1) / &LL ;
LL/I2=LIST - &PP * PP - CC * &LL  - 1;
SP/A1=' ';
END
-RUN
TABLE FILE HOLD
SUM COUNTRY CAR SALES SP AS '.'
BY PP NOPRINT PAGE-BREAK
BY LL NOPRINT
ACROSS CC NOPRINT
ON TABLE SET NODATA ' '
END

Daniel,

Yes, that's an elegant solution that accomplishes it, without the extra empty NULL cells in the output.

It has a few components with which I was not familiar, so thanks for the intro !

Charles Lee

PS:
I'll check into my 'signature' and update it...
Thanks gain for the heads-up...
CL

Thanks...

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

Charles,

You're welcome. Another little insight:
1. In the DEFINE the calculations of the column, line and page are a bit of integer arithmetic.
2. In the first TABLE you will notice that I used both LIST and RANKED. This is because LIST numbers the output lines but when there is a BY clause, the numbering restarts each time the value of the first BY changes. However when RANKED is also used then LIST numbers all lines sequentially. Some of the hidden, but so useful, properties of Focus!

Thanks everyone for the insightful comments in this thread.

Daniel -

I have a slight twist to your approach of using the LIST and RANKED BY commands. I'm trying to get an aggregated data set and then break it into columns. When I use the code shown below the LIST numbers are 'out of order'. It appears that the LIST does its numbering before any final sorting.

I know I could get around this by using 2 HOLD files and waiting until the 2nd HOLD file to add the LIST numbering. I'm wondering if there is a way to get the numbers in the right order on the first pass. Any ideas?

Thanks!

Dan

  
TABLE FILE CAR
LIST
SALES
RANKED BY BODYTYPE
BY TOTAL HIGHEST SALES
BY CAR
END

The implementation of BY TOTAL requires two stages: The first stage assembles an answer set (or "internal matrix") in the normal manner, ignoring the BY TOTAL phrases.

Then a second internal matrix is formed (with room for the additional sort columns), and the rows of the original answer set are used as input transactions to populate the second one, in the process re-sorting them as appropriate.

All Computes take place as part of the first stage. (So all references to LAST are with respect to the first-stage sort order, based on the plain BYs and ignoring the BY TOTALs)

Assignment of the LIST values is implemented as a hidden Compute, and also happens within the first stage. -- Didn't have to be that way, but as it stands you'll have to use an intermediate HOLD: whether you use LIST or compute your own, it has to be done on the HOLD file in order to yield the correct integer series.