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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to get the BY field and ACROSS field titles to line up?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to get the BY field and ACROSS field titles to line up?
 Login/Join
 
CoolGuy
Virtuoso
posted
Hi all,

Anyone know of a good/standard way of getting both the BY field and ACROSS field column titles to line up?

Currently, when it outputs to EXL2K, the BY field titles are on row 2, and the ACROSS column titles are on row 1 with blank cells for row 2 underneath.

Any ideas? A special ON TABLE SET command I don't about yet?

Thanks in advance!

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Waz
Expert
posted Hide Post
There is a SET command, but I can't remember what it is.

Can you check the Documentaiton ?


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
Waz
Expert
posted Hide Post
Have a look at ACROSSTITLE, and see if it does what you want.


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
Tony A
Expert
posted Hide Post
Per Waz, you need to set ACROSSTITLE = SIDE.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
CoolGuy
Virtuoso
posted Hide Post
Waz & Tony,

I tried the ACROSSTITLE SET command and it does nothing to help in my scenario. Neither does ACRSVRBTITL which does work in the way I want things to look if there is no BY fields present.

Example code that works when no BY field is present:

-*SET HIDENULLACRS=ON
-*SET ACROSSLINE=SKIP
-*SET ACROSSTITLE=SIDE
SET ACRSVRBTITL=OFF

TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
-*BY REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END


Example code that adds the blank line underneath as soon as the BY field is added back in:

-*SET HIDENULLACRS=ON
-*SET ACROSSLINE=SKIP
-*SET ACROSSTITLE=SIDE
SET ACRSVRBTITL=OFF

TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
BY REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END


I just want the ACROSS titles to stay on the same line as the BY titles. So, they would stay in the columns they are in still, just that blank line would not be created or present. The ACROSS titles would be where that blank line appears.

Any other ideas? *smh*

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Danny-SRL
Virtuoso
posted Hide Post
CoolGuy,

I'm afraid the blank line is here to stay.
However, no despair, there is always a workaround with WF:
  
TABLE FILE GGSALES
BY REGION
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &R=&LINES;
DEFINE FILE GGSALES
-REPEAT #D FOR &I FROM 1 TO &R;
-READ SAVE,®
®/D6S=IF REGION EQ '®' THEN DOLLARS ELSE 0;
-#D
END
-RUN
TABLE FILE GGSALES
SUM 
-REPEAT #T FOR &I FROM 1 TO &R;
-READ SAVE,®
®
-#T
BY ST
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
CoolGuy
Virtuoso
posted Hide Post
Danny,

Thanks for chiming in. Did your code get borked upon submission? It looks kind of off in some places... Or am I just goofy? Not sure what you're doing here.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Francis Mariani
Expert
posted Hide Post
It looked like a frown, until ,I zoomed in.

It's probably &REG being converted. Best to use a different variable name.


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
Francis Mariani
Expert
posted Hide Post
This is throwing a sledgehammer to a plush panda. It uses jQuery to copy the html within one cell into another, and then delete a row.
-*SET HIDENULLACRS=ON
-*SET ACROSSLINE=SKIP
-*SET ACROSSTITLE=SIDE
-*SET ACRSVRBTITL=OFF

TABLE FILE GGSALES
SUM DOLLARS AS SALES
ACROSS REGION AS ''
BY REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE

ON TABLE HOLD AS HREPORT1 FORMAT HTMTABLE
END
-RUN

-HTMLFORM BEGIN
<html>
<head>
<title></title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<!--<script src="/approot/web/jquery-latest-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:nth(1) td:nth(0)" ).css( { backgroundColor: "pink" } );     // for testing
    $( "body table:eq(0) tr:nth(1) td:nth(1)" ).css( { backgroundColor: "red" } );      // for testing
    $( "body table:eq(0) tr:nth(2) td:nth(0)" ).css( { backgroundColor: "fuchsia" } );  // for testing

    $( "body table:eq(0) tr:nth(1) td:nth(0)" ).html( $( "body table:eq(0) tr:nth(2) td:nth(0)" ).html() ); // Copy the Hire Date column title to the appropriate table cell
    $( "body table:eq(0) tr:nth(2)" ).remove();

});
</script>
</head>
<body>
!IBI.FIL.HREPORT1;
</body>
</html>
-HTMLFORM END

The first four JavaScript lines highlight relevant table cells.
The fifth line copies the text from the first table cell of table row three to table row two.
The sixth line deletes table row three.



If you have rows before the relevant rows, adjust the nth values appropriately.

And, of course, this sledgehammer only works for HTML reports.

This message has been edited. Last edited by: Francis Mariani,


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
CoolGuy
Virtuoso
posted Hide Post
Francis,

You rock! Thanks for this! Will try and get it working here shortly. Does this technique work with Excel output also though? I can't seem to get it working this way in Excel after the fact. Update: Just saw your post update about only in HTML. Thanks... Hmm.... Now to get it working in Excel too...


Appreciate your time and effort in helping me with this!

Love your sayings too! lol A sledge hammer to a plush panda eh? Paints the picture!

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Danny-SRL
Virtuoso
posted Hide Post
CoolGuy,
Well I used a variable & REG (without the space) and that produced the funny character.
So here:
  
TABLE FILE GGSALES
BY REGION
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &R=&LINES;
DEFINE FILE GGSALES
-REPEAT #D FOR &I FROM 1 TO &R;
-READ SAVE,&PLACE
&PLACE/D6S=IF REGION EQ '&PLACE' THEN DOLLARS ELSE 0;
-#D
END
-RUN
TABLE FILE GGSALES
SUM 
-REPEAT #T FOR &I FROM 1 TO &R;
-READ SAVE,&PLACE
&PLACE
-#T
BY ST
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

What this does is replace the ACROSS with a DEFINE.
In this way you are not a tributary to Javascript or Jquery.
Good luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
CoolGuy
Virtuoso
posted Hide Post
Danny,

Thanks for the updated code! No worries man. Appreciate you taking the time to help with this.

Will try and get your method working with this report we've got.

Thanks again!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Waz
Expert
posted Hide Post
You know I still clearly remember a SET command that does this.

Perhaps the grey matter is going gray


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
Crymsyn
Gold member
posted Hide Post
There is also the trick using the Sequence style that Dan Satchell showed here

Course for it to work how you want it need to move the ACROSS after the BY fields like this:

TABLE FILE GGSALES
SUM DOLLARS AS Sales
BY REGION NOPRINT
ACROSS REGION AS ''
COMPUTE XREGION/A15 = REGION; AS 'REGION'
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
TYPE=REPORT, COLUMN=XREGION, SEQUENCE=1, $
ENDSTYLE
END

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


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
CoolGuy
Virtuoso
posted Hide Post
Crymsyn,

Thanks for the example and link. Good stuff!

All,

I chugged through things and got Danny's example logic working with our existing report that needed the formatting change. So, thank you Danny! Works with Excel output as well. Thanks everyone else also for chiming in and for the great other options that are out there!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Greg G
Member
posted Hide Post
did you try SET ACROSSTITLE=SIDE


SET ACROSSTITLE=SIDE

TABLE FILE GGSALES
SUM DOLLARS AS SALES
ACROSS REGION
BY REGION AS ''
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE

END
 
Posts: 12 | Registered: June 08, 2010Report This Post
CoolGuy
Virtuoso
posted Hide Post
Greg,

Yes, tried that. No dice. Got a solution though, so no worries. Thanks though!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Tony A
Expert
posted Hide Post
quote:
Perhaps the grey matter is going gray

No Waz, it's because CG is not using a BY field, just an ACROSS!

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Dave
Master
posted Hide Post
I think this is what you need:

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


it seems to do what you want if the BY-field doens't have a title.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
CoolGuy
Virtuoso
posted Hide Post
Tony,

My report was using both BY fields and ACROSS fields. Daniel's method works to get the ACROSS fields lining up though.

Dave,

Thanks, but my scenario did have BY fields that had titles for each, so that wouldn't fly. Appreciate you chiming in though.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to get the BY field and ACROSS field titles to line up?

Copyright © 1996-2020 Information Builders