Focal Point
GRANDTOTAL Formating

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7371058331

May 06, 2005, 12:47 PM
<Sean Williams>
GRANDTOTAL Formating
Should be simple enough to do but its giving me a headache
Formatting Grand total
for example, in the following

SUM
COMPUTE Actual/F5 = ( CHGPT101 / CHGPT201 ) * 100; NOPRINT
COMPUTE TgtCalcC/F5 = TgtCalcA / TgtCalcB; NOPRINT AS 'Target'
COMPUTE Var/F5 = Actual - TgtCalcC; AS 'Variance'
BY
MF_SORTVALUE AS 'Mode of Transport'
BY
EI_DISPVALUE AS 'Direction'
ACROSS
HIGHEST YEARSORT NOPRINT
ACROSS
PERIOD AS ' '


I have the ON TABLE SUMMARIZE

So I now want to format the GRANDTOTAL using CSS

TYPE=GRANDTOTAL,
COLUMN=Var,
CLASS=RPTTOTALRED,
WHEN=Var LT 0,
$


First question, is there any defined ways to identify which column, ( N1, N2 N3 etc ) or is it purely in order so in this case

Actual =N1
TgtCalc = N2
Var =N3

2ndly in the above example, when setting the Column to Var, only the first item would change to red when LT , the others remained the normal formatting..

3rdly , is there anyway to set the CSS style formating and define the fields inside the Report Painter ?
May 06, 2005, 01:42 PM
<JG>
to answer the first 2 these are the guide lines

Nn Identifies a column by its position in the report.
To determine this value, count vertical sort (BY) fields,
display fields, and ROW-TOTAL fields, from left to right,
including NOPRINT fields.

Pn Identifies a column by its position in the report.
To determine the value of n, count vertical sort (BY) fields,
display fields, and ROW-TOTAL fields from left to right.
Do not count NOPRINT fields.

Cn Identifies a display column by its position in the report.
To determine the value of n, count only display fields from left to right,
including NOPRINT fields. Do not count vertical sort (BY) fields or ROW-TOTAL fields.

To select all display fields use C*.

Bn Identifies a vertical sort (BY) column by its position in the report.
To determine the value of n, count only vertical sort (BY) fields,
including NOPRINTs, from left to right.

To select all BY fields use B*.

field Identifies a column by its field name.

When a field occurs more than once, use field(n) to select a particular occurrence
or field(*) to select all occurrences of the field.

ROWTOTAL Identifies a column of row totals generated using ROW-TOTAL.
When used with ACROSS and multiple display commands, ROWTOTAL generates multiple total columns.
Use ROWTOTAL(n) to select a particular total column.
Use ROWTOTAL(field) to select the row total column for a particular field.

Use ROWTOTAL(*) to select all row total columns in the report.
May 06, 2005, 09:48 PM
susannah
isn't it also true that CLASS won't work unless SET HTMLCSS=ON is set...
which wreaks havoc on everything else.
Or am i nuts?
May 09, 2005, 05:36 AM
<Sean Williams>
SET HTMLCSS=ON is on.
and also referencing the CSS file,
the CLASS statements aren't the issue, as it's working everwhere else, it's only in the row totals ( SUMMARIZE ) that the Formatting gives an issue. Try it, format column totals conditionally ...
Doesn't seem to want to work for me...
May 09, 2005, 06:30 AM
<Sean Williams>
OK after changing the code to
various totals still getting no closer

TYPE=GRANDTOTAL
CLASS=RPTTOTALRED
WHEN = Var LT 0,
$
format the whole totals row as red including the word TOTAL !

If I try to restrict it to
WHEN = TOTAL(Var) LT 0 it returns an Error
(FOC3202) BAD VALUE IN STYLESHEET FILE AT LINE

WHEN = ROWTOTAL(Var) LT 0
Doesn't format anything

WHEN = ROWTOTAL(*) LT 0
(FOC3219) A SINGLE COLUMN MUST BE SPECIFIED

+ various other incarnations.


There just doesn't seem to be anything logical,

all I want to do is format the items in the Totals that are below zero to Red.

It doesn't even work if you conditional format the Totals in Resource painter.

I can get this to work no problem if individual columns are on a report but not when the columns are generated repeatedly as in the original example

eg
if
Country Place Value
Country Place Value
Country Place Value

Totals -1

then not a problem formating the Value

Its only when it becomes something like

01-2005 02-2005 03-2005
Var Var Var
Var Var Var
Var Var Var

Totals -1 1 -1
That this problem seems to be occuring
May 10, 2005, 02:48 AM
susannah
TYPE=GRANDTOTAL,COLUMN=Cn,COLOR=RED,WHEN=Cn LT 0,$
that's just gotta work. i use it like that.
you may not like the Cn part, but its a workaround, till somebody comes up with something cooler.

10.. Big Grin very funny.
May 10, 2005, 07:55 AM
<Sean Williams>
TYPE=GRANDTOTAL,
COLUMN=C*,
COLOR=RED,
WHEN=N3 LT 0,$ (

But the problem seems to be occuring in the WHEN statement,
for example, if I have column with a negative figure somewhere in it, then it's still formatted as red even though the total for the column is positive....

is there anyway to do something like
WHEN=COLUMN-TOTAL(N3) LT 0 ( I know COLUMN-TOTAL) is used for something else... but I want it here Razzer
September 30, 2005, 05:29 PM
<John G. O'Neill>
I know that the reply to this topic is a few months old but it may be of benefit to those who read this and come across Sean's problem (as I did).

I found that if the datatype was defined as packed decimal, e.g. P19, the entire colum came back red. However, once I changed it to decimal, e.g. D19, it worked fine.

Maybe someone has an explanation but I'm just happy that it works.
March 18, 2008, 09:26 AM
arvind
hi ,
i have 2 compute columns which are to be displayed at the end of a CROSSTAB report.. adn i am not able to format the title and font of the compute column and synchronize the font with the rest of the report structure..
this sounds simple..but i have been tryin this for the past 1 week..
please advice
below is the code which shows the structure of the report ..
thank you

TABLE FILE T_ORA_APPL_PLN_V
SUM
SALESX NOPRINT
COMPUTE PCNTS/D12.2 = ( SALESX / TOTAL_NO_APPS ) * 100; NOPRINT
BY LIFECYCLE_STAGE
SUM
SALESD AS '# OF APPLICATIONS'
COMPUTE PCNTA/D12.2 = ( SALESX / TOTAL_NO_APPS ) * 100;
AS '% OF APPLICATIONS'
BY LIFECYCLE_STAGE
ACROSS PRIMARY_BPO AS ''
COMPUTE
NOTA/D12.2 = SALESX; AS 'Total'
COMPUTE
PCNT/D12.2 = PCNTS; AS ' Total'
ON TABLE SUBHEAD
"APPLICATION PORTFOLIO PLANNING REPORT ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'APPLICATIONS THAT SUPPORT MULTIPLE BPO''S' SALESX SALESD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='A1',
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 18, 2008, 09:38 AM
Prarie
Have you tried ACROSSTITLE?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
March 19, 2008, 12:47 AM
arvind
hi prarie,
i have tried acrosstitle , acrossvalue keywords..It doesnt work ..the problem i am facing is the tow compute columns are detached from the rest of the report and i am not able to format them...please advice..i have pasted the screenshot of my output in [URL=http://www.tinypic.com]

[IMG]http://i30.tinypic.com/2ykzv2s.jpg



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


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 19, 2008, 03:28 AM
Tony A
Arvind,

Use the "TYPE=REPORT, COLUMN=" syntax of the stylesheet.

Example -
TABLE FILE CAR
SUM COMPUTE SALESX/D9 = SEATS; NOPRINT
    COMPUTE PCNTS/D12.2 = BHP / SALESX; NOPRINT
 BY COUNTRY
SUM COMPUTE SALESA/D9 = SEATS;
    COMPUTE PCNTS/D12.2 = BHP / SALESA;
 BY COUNTRY
ACROSS BODYTYPE AS ''
    COMPUTE NOTA/D9 = SALESX;
    COMPUTE PCNT/D12.2 = PCNTS;
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=8, $
TYPE=REPORT, COLUMN=NOTA, BACKCOLOR=RGB(000 000 255), COLOR=WHITE, SIZE=9, STYLE=BOLD+ITALIC, $
TYPE=REPORT, COLUMN=PCNT, BACKCOLOR=RGB(000 000 255), COLOR=WHITE, SIZE=9, STYLE=BOLD+ITALIC, $
ENDSTYLE
END

T

This message has been edited. Last edited by: Tony A,


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 
March 19, 2008, 03:53 AM
arvind
hi tony,
It dint work ...i used the column = (compute column name ) clause.. If u will please see the output shot ...the two compute columns are totally unaffected by the formatting changes in the rest of the report...My requirement is that their format should be similar to the report format..any suggestions??

thank you sir


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 19, 2008, 04:31 AM
Tony A
Arvind,

The trouble with tinypic is that it is an offline storage site and is often blocked by some companies firewalls and content filters (e.g. where I am!).

Did you try the example I gave you? That does work and is close to what you are attempting. The example, too, uses the COLUMN=[computed field name] and functions just fine.

Extending the example above to provide your column-total syntax, this code -
TABLE FILE CAR
SUM COMPUTE SALESX/D9 = SEATS; NOPRINT
    COMPUTE PCNTS/D12.2 = (SALESX / BHP) * 100; NOPRINT
 BY COUNTRY
SUM SEATS AS ''
    COMPUTE PCNTS/D12.2 = (SALESX / BHP) * 100; AS ''
 BY COUNTRY
ACROSS BODYTYPE AS ''
    COMPUTE NOTA/D9 = SALESX; AS 'Total'
    COMPUTE PCNT/D12.2 = PCNTS; AS 'Total'
-*ON TABLE SUBTOTAL SALESX SEATS AS 'Grand Total'
ON TABLE COLUMN-TOTAL AS 'Totals' SALESX SEATS
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, BACKCOLOR=RGB(240 245 255), COLOR=BLUE, SIZE=8, $
TYPE=ACROSS, BACKCOLOR=RGB(200 200 255), COLOR=WHITE, SIZE=9,
     STYLE=+BOLD+ITALIC, BORDER=LIGHT, BORDER-COLOR=RGB(160 160 255), $
TYPE=TITLE, BACKCOLOR=RGB(200 200 255), COLOR=WHITE, SIZE=9,
     STYLE=+BOLD+ITALIC, BORDER=LIGHT, BORDER-COLOR=RGB(160 160 255), $
TYPE=REPORT, COLUMN=NOTA, BACKCOLOR=RGB(200 200 255), COLOR=WHITE,
     SIZE=9, STYLE=+BOLD+ITALIC, BORDER=LIGHT, BORDER-COLOR=RGB(160 160 255), $
TYPE=REPORT, COLUMN=PCNT, BACKCOLOR=RGB(200 200 255), COLOR=WHITE,
     SIZE=9, STYLE=+BOLD+ITALIC, BORDER=LIGHT, BORDER-COLOR=RGB(160 160 255), $
-*TYPE=GRANDTOTAL, BACKCOLOR=RGB(160 160 255), COLOR=WHITE, SIZE=10,
     STYLE=+BOLD+ITALIC, BORDER=LIGHT, BORDER-COLOR=RGB(160 160 255), $
ENDSTYLE
END

Produces this output -

Try uncommenting the GRANDTOTAL styling and see the difference, also switch between SUBTOTAL and COLUMN-TOTAL to see differences.

T

This message has been edited. Last edited by: Tony A,


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 
March 19, 2008, 05:45 AM
arvind
hi tony,
thanks for the code..i tried it but the whole compute column's backcolor is changed..my requirement is i want only the coumn title backcolor to change..
When i use TYPE = TITLE , COLUN = NOTA ,
BACKCOLOR = RGB(153 153 153),
$
IT is not getting reflected only in the title..
Is there any other way to format the compute columns??please advice..

Can you suggest a place where i can upload the screenshot..or can u please give me your email Address ??

Thank you sir


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 19, 2008, 07:18 AM
Tony A
If you remove the two style statements for columns NOTA and PCNT in my example, all the titles are formatted the same. Is that what you want?

If you are wanting to individually change just those two column titles then I think you are out of luck.

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 
March 19, 2008, 07:22 AM
arvind
hi tony,
even if i remove the two style statements for compute columns..then also those two columns are not getting formatted according to the report ... below is the code ...please check

[CODE] DEFINE FILE T_ORA_APPL_PLN_V
SALESD/D15 = NO_OF_APPLICATIONS;
SALESX/D15= NO_OF_APPLICATIONS;
NOTA/D12.2 = SALESX;
PCNT/D12.2 = PCT.SALESX;
END
TABLE FILE T_ORA_APPL_PLN_V
SUM
SALESX NOPRINT
COMPUTE PCNTS/D12.2 = ( SALESX / TOTAL_NO_APPS ) * 100; NOPRINT
BY LIFECYCLE_STAGE
SUM
SALESD AS '# OF APPLICATIONS'
COMPUTE PCNTA/D12.2 = ( SALESX / TOTAL_NO_APPS ) * 100;
AS '% OF APPLICATIONS'
BY LIFECYCLE_STAGE
ACROSS PRIMARY_BPO AS ''
COMPUTE
NOTA/D12.2 = SALESX; AS 'Total'
COMPUTE
PCNT/D12.2 = PCNTS; AS ' Total'
ON TABLE SUBHEAD
"APPLICATION PORTFOLIO PLANNING REPORT ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'APPLICATIONS THAT SUPPORT MULTIPLE BPO''S' SALESX SALESD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='A1',
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=TITLE,
COLUMN=NOTA,
SIZE=10,
WRAP=2.5,
JUSTIFY=RIGHT,
$
TYPE=TITLE,
COLUMN=PCNT,
SIZE=10,
WRAP=2.5,
BACKCOLOR=RGB(153 153 153),
JUSTIFY=RIGHT,
$


tHE TWO COMPUTE COLUMNS ARE NOTA AND pcnt
THANK YOU SIR

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


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 19, 2008, 07:31 AM
Tony A
quote:
If you are wanting to individually change just those two column titles then I think you are out of luck.
As you attempting individual styling for these two columns, then the statement I made (repeated above in case you didn't notice it the first time) is relevant.

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 
March 19, 2008, 08:03 AM
arvind
HMM ok ..thanks a lot sir., Frowner. .


WebFOCUS 714
Windows XP
Output: HTML,PDF,Excel
March 19, 2008, 08:51 AM
linus
Change your column headings to be part of the report heading and format the report heading and in your report logic use the " AS '' " logic to keep you column headings from appearing.
Example of heading:
table file whatever
heading
"Application Planning Portfolio Report"<+0> <+0>Asset Protection<+0>Distribution<+0>Marketing<+0>Total<+0>Total"
"Life Cycle<+0># of Applications<+0>% of Applications<+0># of Applications<+0>% of Applications"

Then style it within the stylesheet logic as "type=heading, line=n, object=text, item=n,...."


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
March 19, 2008, 08:53 AM
linus
My lines didn't split properly in my previous post. the "<+0>" starts on a new line.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
March 19, 2008, 09:23 AM
Tony A
One method you could try and use if the two columns were to be styled identically, is to use -

TYPE=ACROSSVALUE, BACKCOLOR=RGB(200 255 200), COLOR=BLACK, $
TYPE=TITLE, BACKCOLOR=RGB(200 200 255), COLOR=WHITE, $

If you do not use ACROSSVALUE in your styling, all the across values default to using the TITLE styling.

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