Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
ACROSS Title issue
 Login/Join
 
OliHewitt
Member
posted
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


WF Release: WebFOCUS 8.0.09
Windows 7, HTML
 
Posts: 9 | Location: Peterborough, UK | Registered: July 09, 2012Report This Post
Francis Mariani
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Waz
Expert
posted Hide Post
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


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
OliHewitt
Member
posted Hide Post
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


WF Release: WebFOCUS 8.0.09
Windows 7, HTML
 
Posts: 9 | Location: Peterborough, UK | Registered: July 09, 2012Report This Post
Martin vK
Platinum Member
posted Hide Post
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.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Roo
Member
posted Hide Post
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.


WebFOCUS 8.2.05
Windows Server 2016 Datacenter v1607
All outputs
 
Posts: 2 | Registered: July 17, 2008Report This Post
eric.woerle
Master
posted Hide Post
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


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Waz
Expert
posted Hide Post
Well thats what happens when you code quickly and don't really check.


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
OliHewitt
Member
posted Hide Post
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.


WF Release: WebFOCUS 8.0.09
Windows 7, HTML
 
Posts: 9 | Location: Peterborough, UK | Registered: July 09, 2012Report This Post
Francis Mariani
Expert
posted Hide Post
It does not overwrite the title of the first column in my example, see image:



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders