Hi everyone,

I have a report that works perfectly fine, but now I have to allow the user to switch lines to columns. Is there any quick way of doing this?

Thanks in advance.

You are trying to pivot your data from lines to columns. How "quick" a way depends entirely on your data and what the layout is and has to be.

If the data lends itself to it, you may just use ACROSS.

If not, you'll have to DEFINE data buckets for each "column" using IF statements to decide what value goes into which column. You can then run a SUM request (probably with no BY's) and you would get a line with many columns ... each of them representing what was a separate line before.

Without knowing exactly what you want to see is sort of hard to make appropriate suggestions.

Can you work out an example with the CAR table simulating what you have today (lines-based report) and mock something up as to how the output would look now in column mode?

I tried a few things on my own, but didn't get anything satisfying... or even close to what I need...

So here's some code I made up to show you.

-* Building a table with fake data
-REPEAT LOOP.DEF FOR &I FROM 1 TO 5;

DEFINE FILE DUMMY
	DESC/A20 = 'Description' | '&I';
	COLOR/A20 = 'Color' | '&I';
	YEAR/A20 = '200' | '&I';
END

-SET &TBL = 'HOLDFILE' | &I;

TABLE FILE DUMMY
PRINT *
	DESC AS 'Description';
	COLOR AS 'Color';
	YEAR AS 'Year';
	ON TABLE HOLD AS &TBL.EVAL FORMAT ALPHA
END

-RUN
-LOOP.DEF;
-* Now we have 5 HOLDFILEx tables.

-* Putting all the data together
TABLE FILE DUMMY
PRINT *
	DESC
	COLOR
	YEAR
	WHERE YEAR = '0';
ON TABLE HOLD AS TBL_DONNEES
-SET &J = 1;
-REPEAT LOOP.PLUS FOR &J FROM 1 TO 5;
-SET &V_HLD01 = 'HOLDFILE' | &J;
MORE
FILE &V_HLD01.EVAL
-LOOP.PLUS;
END
-RUN

-* Displaying the data by column
TABLE FILE TBL_DONNEES
PRINT 
	DESC
	COLOR
	YEAR
END
-RUN
-EXIT

It gives something like this (sorry I couldn't format):
DESC . . . . COLOR . YEAR
Description1 Color1 2001
Description2 Color2 2002
Description3 Color3 2003
Description4 Color4 2004
Description5 Color5 2005

What I need would be to swap lines and columns:
DESC . Description1 . Description2 . ...
COLOR. Color1 . . . . . Color2 . . . . ...
YEAR . . 2001 . . . . . . 2002 . . . . . ...

Sorry I won't post the original code, it's a trillion lines long...

Try this:

TABLE FILE TBL_DONNEES
 SUM COLOR
 OVER YEAR
 ACROSS DESC
END

Or this:

TABLE FILE TBL_DONNEES
 SUM DESC
 OVER COLOR
 OVER YEAR
 ACROSS DESC NOPRINT
END

quote:

Sorry I won't post the original code, it's a trillion lines long...

No worries. We wouldn't been able to review it either

Fanfanprovok, your sample code does not work as it relies on a DUMMY table whose creation code you did not provide.

Anyway, this seems to be feasible with ACROSS and OVER.

See my sample code below to give you an idea of something you can start with:

TABLE FILE CAR
PRINT COUNTRY
      CAR
      MODEL
HEADING
"Vertical Layout"
ON TABLE SET PAGE NOPAGE
END


TABLE FILE CAR
SUM CAR OVER MODEL
ACROSS COUNTRY AS ''
HEADING
"Horizontal Layout"
ON TABLE SET PAGE NOPAGE
END

quote:

Originally posted by Dan Satchell:
Or this:

TABLE FILE TBL_DONNEES
 SUM DESC
 OVER COLOR
 OVER YEAR
 ACROSS DESC NOPRINT
END

That's EXACTLY what I needed! Thank you!!


...


But now the problem is that I can't SQUEEZE my columns...!!!

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

The documentation says that OVER applies to HTML and PDF. Any idea how to do it in Excel? SUM doesn't seem to apply to EXL2K either...

quote:

Any idea how to do it in Excel? SUM doesn't seem to apply to EXL2K either...

Did you actually try this in 7.6 and did not work?

I just ran the sample code I had provided in 7.7.03 using EXL2K output and it works as expected.

No i didn't have time to try it in EXCEL. I've been busy trying to make the real thing display in lines... still no satisfaction...! Here's what I have so far. The ACROSS doesn't seem to work. In which cases would an ACROSS not work? Sorry I'm not posting the whole thing, but as I said earlier, it's quite huge...

 
...
ON TABLE HOLD AS SOLDE_GL_FINAL_FOR_TESTS FORMAT ALPHA
END

TABLE FILE SOLDE_GL_FINAL_FOR_TESTS
SUM

-SET &CNT_COL = 0;
-SET &CNT_COLTOTAL = 0;

-FINAL_LP2

-SET &CNT_COL			= &CNT_COL + 1;
-SET &COL_NUM 			= 'COLUMN' || &CNT_COL || '_NUM';
-SET &COL_DENOM 		= 'COLUMN' || &CNT_COL || '_DENOM';
-SET &COL_TMP			= 'COL_TMP'|| &CNT_COL;
-SET &COND_AMOUNT_TOT = IF &IS_IN_PERCENTAGE.EVAL EQ 1 
-   THEN 'COMPUTE &COL_TMP.EVAL/D17.2B% = (&COL_NUM.EVAL/&COL_DENOM.EVAL) * 100;'
-   ELSE 'COMPUTE &COL_TMP.EVAL/D17.2B = (&COL_NUM.EVAL/&COL_DENOM.EVAL);';

-* For lines after the first, add "OVER".
-IF &CNT_COLTOTAL EQ 1 THEN GOTO FST_LINE;
OVER
&COND_AMOUNT_TOT.EVAL AS '&COL_TMP';
-GOTO NEXT_LINE;

-FST_LINE
&COND_AMOUNT_TOT.EVAL AS '&COL_TMP';
-NEXT_LINE

-* Goto next line.
-IF &CNT_COLTOTAL LT &NBCOL_TOTAL.EVAL GOTO FINAL_LP2;

-* Apply horizontal sort.
ACROSS 'COL_TMP1' NOPRINT

END
-RUN
-EXIT

When I run this code, the output is like this, which you be good if it didn't do a sum:
COL_TMP1 769,335.27
COL_TMP2 (481,135.99)
COL_TMP3 292,001.49


When I replace SUM with PRINT, the output looks like this, which would be perfect if it wasn't all on the same column.:

COL_TMP1 12,389,526.17
COL_TMP2 .00
COL_TMP3 .00
COL_TMP1 863,804.17
COL_TMP2 .00
COL_TMP3 .00
COL_TMP1 .00
...

Any ideas?

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