[SOLVED] How to get the BY field and ACROSS field titles to line up?
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.
September 12, 2016, 07:54 PM
Waz
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.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!
September 12, 2016, 07:58 PM
Waz
Have a look at ACROSSTITLE, and see if it does what you want.
Waz...
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!
September 13, 2016, 03:31 AM
Tony A
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
September 13, 2016, 11:56 AM
CoolGuy
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.
September 13, 2016, 01:38 PM
Danny-SRL
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
September 13, 2016, 02:07 PM
CoolGuy
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.
September 13, 2016, 02:23 PM
Francis Mariani
It looked like a frown, until ,I zoomed in.
It's probably ® 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
September 13, 2016, 03:02 PM
Francis Mariani
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
September 13, 2016, 03:20 PM
CoolGuy
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.
September 13, 2016, 03:46 PM
Danny-SRL
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
September 13, 2016, 03:55 PM
CoolGuy
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.
September 13, 2016, 04:38 PM
Waz
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.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!
September 13, 2016, 05:16 PM
Crymsyn
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
September 13, 2016, 06:34 PM
CoolGuy
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.
September 14, 2016, 02:04 PM
Greg G
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
September 14, 2016, 02:39 PM
CoolGuy
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.
September 15, 2016, 02:36 AM
Tony A
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
September 15, 2016, 03:07 AM
Dave
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
September 15, 2016, 11:08 AM
CoolGuy
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.
