ACROSS Title issue

January 13, 2015, 12:26 PM

OliHewitt

ACROSS Title issue

Hi Everyone,

I have a report that contains multiple BY groups and one ACROSS dimension:
--------------------------------------------

TABLE FILE VOLUMES_CUBE

SUM VOLUMES_CUBE.VOLUMES_CUBE.Net_Sales AS ''

BY VOLUMES_CUBE.VOLUMES_CUBE.Product_Type1
BY VOLUMES_CUBE.VOLUMES_CUBE.Risk_Class1

ACROSS VOLUMES_CUBE.VOLUMES_CUBE.Product_Class1

ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET BYDISPLAY ON

END

-------------------------------------------

What I'd like to be able to do is have the ACROSS title values on the same row as the GROUP BY titles.

Is there an easy way to do this?

Cheers,

Oli

January 13, 2015, 02:29 PM

Francis Mariani

This is an age-old request. The only way I've succeeded is with brute-force.

In the example below, I use jQuery to copy the titles of the two BY columns to the first row cells, then delete the second row.

If you're interested, you can comment-out lines to see what's going on - I use colours to indicate the cells being affected.

TABLE FILE CAR
SUM
SALES AS ''
BY COUNTRY
BY BODYTYPE
ACROSS SEATS AS ''

ON TABLE HOLD AS HREPORT1 FORMAT HTMTABLE
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET BYDISPLAY ON
END
-RUN

-HTMLFORM BEGIN
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function()
{
$( "body table:eq(0) tr:first td:first" ).css( { backgroundColor: "blue" } ); // for testing
$( "body table:eq(0) tr:first td:nth(1)" ).css( { backgroundColor: "green" } ); // for testing

$( "body table:eq(0) tr:nth(1) td:first" ).css( { backgroundColor: "orange" } ); // for testing
$( "body table:eq(0) tr:nth(1) td:nth(1)" ).css( { backgroundColor: "red" } ); // for testing

$( "body table:eq(0) tr:first td:first" ).html( $( "body table:eq(0) tr:nth(1) td:first" ).html() );
$( "body table:eq(0) tr:first td:nth(1)" ).html( $( "body table:eq(0) tr:nth(1) td:nth(1)" ).html() );

$( "body table:eq(0) tr:nth(1)" ).remove(); 
});

</script>

</head>

<body>
!IBI.FIL.HREPORT1;
</body>
</html>
-HTMLFORM END

January 13, 2015, 03:44 PM

Waz

The only other way is to define a field for each column in the across and do away with the across all together.

The example below is dynamic.

TABLE FILE CAR
BY SEATS
ON TABLE SAVE AS SAV_SEAT
ON TABLE SET HOLDLIST PRINTONLY
END

-RUN

-SET &SEAT_CNT = &LINES ;

DEFINE FILE CAR
-REPEAT SEATLOOP1 FOR &CNTR FROM 1 TO &SEAT_CNT ;
-READ SAV_SEAT &SEAT_VAL.A3.
 SALES_SEAT_&CNTR /I6 = IF SEATS EQ &SEAT_VAL THEN SALES ELSE 0 ;
-SEATLOOP1
END

TABLE FILE CAR
SUM
-REPEAT SEATLOOP2 FOR &CNTR FROM 1 TO &SEAT_CNT ;
-READ SAV_SEAT &SEAT_VAL.A3.
 SALES_SEAT_&CNTR AS '&SEAT_VAL' 
-SEATLOOP2
BY COUNTRY
BY BODYTYPE

ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET BYDISPLAY ON
END
-RUN

January 14, 2015, 06:24 AM

OliHewitt

Thank you both for your quick replies. I tried the JQuery method first and it worked like a treat! Just had to change the output FORMAT from "HTMLTABLE" to just "HTML".

Thanks Again,

Oli

January 14, 2015, 06:41 AM

Martin vK

There is another 'trick' you can use if you have only 1 BY column. This is not your case, but someone else might might be helped with this solution.

In short you use SET ACROSSTITLE=SIDE to put the Across title left of the across values (which is above the BY column) and change the title to be the title of the BY column.

SET ACROSSTITLE=SIDE
TABLE FILE CAR
SUM SALES
BY COUNTRY AS ''
ACROSS BODYTYPE AS 'Country'
ON TABLE SET STYLE *
TYPE=ACROSSTITLE, JUSTIFY=LEFT, $
ENDSTYLE
END

If you do need the Across title itself as well, you could add a higher level ACROSS

SET ACROSSTITLE=SIDE
DEFINE FILE CAR
ACRTITLE/A10 = 'Bodystyle';
END
TABLE FILE CAR
SUM SALES
BY COUNTRY AS ''
ACROSS ACRTITLE AS ''
ACROSS BODYTYPE AS 'Country'
ON TABLE SET STYLE *
TYPE=ACROSSTITLE, JUSTIFY=LEFT, $
ENDSTYLE
END

But this only works nicely with only 1 BY column.

Martin.

January 14, 2015, 02:21 PM

Roo

Waz, check your results. England, sedan should read 12,000 in sales under 5 seats, not 4. It dawned on me when I saw the output read 5 2 4 instead of 2 4 5. I added a -RUN in the define after -SEATLOOP1 to close the file to correct the ouput. That was fun. I enjoyed figuring it out.

January 14, 2015, 02:49 PM

eric.woerle

You can also easily do this by holding your output as Alpha. This will turn your across columns to fields. Just be sure to set ASNAMES=ON. This will work for all output types, not just HTML.

SET ASNAMES=ON

TABLE FILE CAR
SUM
SALES AS ''
BY COUNTRY
BY BODYTYPE
ACROSS CAR 

ON TABLE HOLD AS HREPORT1 FORMAT ALPHA
END

TABLE FILE HREPORT1
PRINT *
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET BYDISPLAY ON
END

January 14, 2015, 04:03 PM

Waz

Well thats what happens when you code quickly and don't really check.

January 15, 2015, 09:18 AM

OliHewitt

On closer inspection the JQuery method overwrote the 1st column title with a BY field title! I've seen the solution provided by Eric and this does exactly what I am after without the use of JQuery.

However, the JQuery method has many applications so Francis - do you how to stop the code from overwriting the title in the first column?
I tried to get round this by inserting an additional ACROSS column but couldn't get the correct results.

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!