I have a report that compares numbers for up to 5 years. I have several fields that are being summed across the year and then across the gender. I also have an across-total column. Since I have so many years next to each other, the different years kind of blend together and it's hard to see the break between them. I was wondering if there is any way to put some styling on the across-total "column" like maybe making the values bold or changing the background color of the cells. I've looked everywhere and I can't find anything that does this. Thanks.

I started this in RA and edited as I couldn't find the option. Some days feel like a complete new bee: The report comes out with a grid in PDF.
TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
BY CAR.COMP.CAR
ACROSS CAR.ORIGIN.COUNTRY
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID = ON , ORIENTATION = LANDSCAPE, $
TYPE=DATA, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=TITLE, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=DATA, COLUMN=CAR.COMP.CAR,
FONT=COURIER,
SIZE=14,
$
TYPE=TITLE, COLUMN=CAR.COMP.CAR,
FONT=COURIER,
SIZE=14,
$
ENDSTYLE
ON TABLE SET HTMLCSS ON
ON TABLE ROW-TOTAL
ON TABLE COLUMN-TOTAL
END

I'm not sure if I was completely clear in my posting. Here is the part of my code where the sum and across is occuring:

SUM INQUIRED AS 'Inquired' OVER
GENERAL AS 'General File' OVER
INTV AS 'Interviewed' OVER
INPRO AS 'In Process' OVER
FILEC AS 'File Complete' OVER
REVIEW AS 'Review' OVER
DENIED AS 'Denied' OVER
CANCNOAC AS 'Can/Not Acc' OVER
CANCACC AS 'Can/Acc' OVER
CANCPD AS 'Can/Paid' OVER
WAIT_POOL AS 'Waiting Pool' OVER
CANCWAIT AS 'Can/Wait Pool' OVER
QUAL AS 'Qualified Status' OVER
ACCEPTED AS ' Accepted' OVER
PAID AS 'Paid' OVER
APPLIED AS 'Applied'
ACROSS HIGHEST TERM_CODE AS ''
ACROSS LOWEST SEX AS '' ACROSS-TOTAL AS 'Total'

It's this last ACROSS-TOTAL AS 'Total' that I'm trying to style. I've been able to get the ACROSS fields to style, but not the ACROSS-TOTAL which appears on the report looking like a column.

Okay, it takes counting, so this may or may not work for you.

TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
BY CAR.COMP.CAR
ACROSS CAR.ORIGIN.COUNTRY
ACROSS CAR.BODY.SEATS
ON TABLE SET STYLE *
TYPE=REPORT,
GRID=ON, ORIENTATION = LANDSCAPE,FONT=COURIER,SIZE=8, $
$
TYPE=DATA, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=TITLE, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=DATA, COLUMN=CAR.COMP.CAR,
SIZE=14,
$
TYPE=TITLE, COLUMN=CAR.COMP.CAR,
FONT=COURIER,
SIZE=14,
$
TYPE = DATA, COLUMN = 11 ,COLOR = GREEN,BACKCOLOR=YELLOW, $
TYPE = GRANDTOTAL, COLUMN = 11 ,COLOR = GREEN,BACKCOLOR=YELLOW, $
ENDSTYLE
ON TABLE PCHOLD FORMAT PDF
ON TABLE ROW-TOTAL
ON TABLE COLUMN-TOTAL
END

I'm still not having much success. I should probably add that my output is in HTML format.

This should be easy and obvious, but it's not.

I just spent quite a bit of time scanning the "Creating Reports With WebFOCUS Language Version 5 Release 3" documentation and found nothing on styling the Across Total component.

Perhaps the styling had been overlooked by the right hand while the left hand was developing ACROSS-TOTAL.

This looks very familiar to the styling problem with COMPUTEd fields after an ACROSS.

Ken

focusqueen, I agree with Francis. i spent time trying to make it work, using every stylesheet word i could find. it should work like the "type=data,column=rowtotal(n)" syntax we see in the sample stylesheets. this is def an NFR or maybe it does work in a higher release.
I need this feature , too. We all do. Has CSS given you any idea? how about NFR'ing it for all of us.

I agree with Leah. You can only do this by counting out the columns and use TYPE = DATA , COLUMN = Pnn , ... syntax to apply the formating one by one to EACH AND EVERY ACROSS-TOTAL value. In order to do this easily, I suggest running your report WITHOUT the 'OVER' keywords, figure out the Pnn value for each ACROSS-TOTAL value and then put your OVER keywords back in and add the styling commands using the Pnn values. There seems to be no other way to do this even in WebFOCUS 7.1.

FYI, this will work for both HTML and PDF output.

Greetings,

An enhancement will be made in the documentation to provide an example for styling an ACROSS-TOTAL component using TYPE=ACROSSVALUE along with COLUMN=Nn.

ACROSS-TOTAL itself cannot be used for styling, as noted in the Reference: Usage Notes for ACROSS-TOTAL topic, which states: "ACROSS-TOTAL populates the ACROSSTITLE and ACROSSVALUE components in a StyleSheet" (from page 7-7 of WebFOCUS Creating Reports 7.1 (DN4500695.0505)).

Hope this helps. Sorry for any confusion this may have caused.

Regards,
Jenn

I'm still confused.

How do you style the ACROSS-TOTAL column(s) - there can be more than one?

What is the syntax? Can you provide the example that will be added to the documentation?

Thank you.

The following example illustrates how to identify a row total (ACROSS-TOTAL) for horizontal sort (ACROSS) data using the ACROSSVALUE component and a numeric column reference (Nn). The relevant StyleSheet declaration is the TYPE=ACROSSVALUE line.



TABLE FILE CENTORD

SUM QUANTITY

BY PRODNAME

ACROSS PLANT AS 'Manufacturing Plant'

ACROSS-TOTAL AS 'Plant Totals'

WHERE PRODTYPE EQ 'Digital'
ON TABLE SET PAGE-NUM OFF

ON TABLE SET STYLE *

TYPE=REPORT, GRID=OFF, $

TYPE=ACROSSVALUE, COLUMN=N8, STYLE=ITALIC, COLOR='RED', $ ENDSTYLE END



This produces output with the ACROSS-TOTAL value, Plant Totals, styled in red italics. The following screen capture shows this output:


Note: For additional styling, you can also include the following line above the TYPE=ACROSSVALUE line above to get all ACROSS values (all manufacturing plants) displayed in blue, while the 'Plant Totals' is still displayed in red italics. TYPE=ACROSSVALUE, COLOR='BLUE', $



Hope this helps.

Regards,
Jenn

This message has been edited. Last edited by: <Mabel>,

Unfortunately, this is a work-around solution. Using Nn is styling a particular ACROSS-VALUE column, and not necessarily the ACROSS-TOTAL column or columns. Quite often, you do not know how many ACROSS values there are, therefore you cannot use Nn.

Actually, I've had to incorporate logic on occasion that does this type of thing.

Let's say you want to do this:

TABLE FILE CAR
SUM SALES ACROSS CAR
ACROSS-TOTAL AS 'TOTAL'
END

And you want to style the across total column. You can count the number of cars and put in a save file, read the file and adjust the parameter such as:


SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

TABLE FILE CAR
SUM CNT.DST.CAR
ON TABLE SAVE AS TEST
END
-RUN
-SET &CARS = 0 ;
-READ TEST &CARS.I5.
-TYPE CARS TOTAL IS &CARS
-SET &TOTCARS = &CARS + 1 ;

-* &CARS is the total number of cars (ACROSS)
-* Because no sorting is done, adjust cars by 1
-* column (&CARS + 1)

TABLE FILE CAR
SUM SALES ACROSS CAR
ACROSS-TOTAL AS 'TOTAL'
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, COLUMN=N&TOTCARS, STYLE=ITALIC, COLOR='RED',$
ENDSTYLE
END

Ken

Ken,

It's a great work-around solution until IBI comes up with the correct solution.

Thanks.

Is this what "focusqueen" was trying to do? This only styles the column TITLE value and not the DATA values in that column. I thought the goal was to style the DATA values and the TITLE value of the ACROSS-TOTAL columns.

These solutions highlight the column title for the across-total column. What about the values in the column? I tried just counting higher and it didn't work.

Add this:

TYPE=DATA, COLUMN=N&TOTCARS, COLOR=BLUE, $


Ken

k, your w/a is clever syntax. applause.

i have it on good authority from a very nice chap at css that this feature is in fact NFR'd.

This would be great. Do you know when this is supposed to be available? Due to how my report is being generated, I don't believe I can "fudge" it as described above. It would be wonderful to just be able to style an across-total column.

it sure will be, but no , i don't know when actually. but in the meantime, k.lane's solution is really a pretty slick w/a. (Doc services answers miss the point.) If you need help implementing it, post your code, and one of us will certainly help you out.

Ok. This program is in three pieces. The first is the following. I've had to take out the htmlform section that actually displays the report because I couldn't submit the form with it in there.

-* Program: STUpStat.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* Launch Page: STUpStat.htm
-*
-* Input: Term (multiple ones)
-* Year, Month and Day of activity date for each term selected
-*
-* Included: STUpStat_Style.fex (style sheet for output)
-* STUpStat_Middle.fex (Sum and Where statement for report.
-* Repeated for all 4 student types.)
-*
-* This report creates a statistical report for Upland comparing each of the
-* selected terms for the activity date selected for each term.
-**************************************************************************

-* Combining the year, month and date fields together for each activity date.
-* There will be at least two dates. Checks to see if there are more than that
-* before trying to create the combined date fields.

-SET &DATE1 = &AYear|| &AMonth || &ADay;
-SET &DATE2 = &BYear || &BMonth || &BDay;
-IF &TERMNUM EQ '2' THEN GOTO L$DONEDATES;
-SET &DATE3 = &CYear || &CMonth || &CDay;
-IF &TERMNUM EQ '3' THEN GOTO L$DONEDATES;
-SET &DATE4 = &DYear || &DMonth || &DDay;
-IF &TERMNUM EQ '4' THEN GOTO L$DONEDATES;
-SET &DATE5 = &EYear || &EMonth || &EDay;
-L$DONEDATES

-* Setting page up so there won't be any page breaks or line numbers.

SET PAGE=NOPAGE
SET LINES=99999

-* Combining all of the tables needed from the admissions datamart.

JOIN CLEAR
JOIN PROSPECT_KEY IN FACT_ADMISSIONS TO PROSPECT_KEY IN DIM_PROSPECT AS J1
JOIN TERM_KEY IN FACT_ADMISSIONS TO TERM_KEY IN DIM_TERM AS J2
JOIN CAMPUS_KEY IN FACT_ADMISSIONS TO CAMPUS_KEY IN DIM_CAMPUS AS J3
JOIN ADMISSIONS_DECISION_CODE_KEY IN FACT_ADMISSIONS TO
ADMISSIONS_DECISION_CODE_KEY IN DIM_ADMISSIONS_DECISION_CODE AS J4
END
JOIN PROSPECT_PROFILE_KEY IN FACT_ADMISSIONS TO
PROSPECT_PROFILE_KEY IN DIM_PROSPECT_PROFILE AS J5
END
JOIN APPLICATION_DATE_KEY IN FACT_ADMISSIONS TO
APPLICATION_DATE_KEY IN DIM_APPLICATION_DATE AS J6
END
JOIN PROSPECT_ADDRESS_KEY IN FACT_ADMISSIONS TO
PROSPECT_ADDRESS_KEY IN DIM_PROSPECT_ADDRESS AS J7
END
JOIN BEGIN_DATE_KEY IN FACT_ADMISSIONS TO
BEGIN_DATE_KEY IN DIM_BEGIN_DATE AS J8
END
JOIN END_DATE_KEY IN FACT_ADMISSIONS TO
END_DATE_KEY IN DIM_END_DATE AS J9
END

-* Defining calculations for the statistical report. These will be summed
-* below for each student type.

DEFINE FILE FACT_ADMISSIONS
INQUIRED/I5 = 1;
INPRO/I4 = (DECISION_CODE EQ 'IP' OR 'HD');
GENERAL/I4 = (DECISION_CODE EQ 'GN');
FILEC/I4 = (DECISION_CODE EQ 'FC' OR 'FI');
REVIEW/I4 = (DECISION_CODE EQ 'RV');
QUAL/I4 = (DECISION_CODE EQ 'QP' OR 'QU');
APPLIED/I4 = (APPLICATION_SHORT_DATE NE ' ');
ACCEPTED/I4 = (DECISION_CODE EQ 'AC' OR 'AN' OR 'AP' OR 'PN' OR
'PP' OR 'PD' OR 'NG');
DENIED/I4 = (DECISION_CODE EQ 'DN');
PAID/I4 = (DECISION_CODE EQ 'PD' OR 'PN' OR 'PP');
CANCACC/I4 = (DECISION_CODE EQ 'CA' OR 'CP');
CANCPD/I4 = (DECISION_CODE EQ 'CP');
CANCNOAC/I4 = (DECISION_CODE EQ 'CN' OR 'CW' OR 'C1' OR 'C2' OR 'C3');
CANCWAIT/I4 = (DECISION_CODE EQ 'CW' OR 'C1' OR 'C2' OR 'C3');
WAIT_POOL/I4 = (DECISION_CODE EQ 'WP' OR 'W2');
-* CAMP_LDT/A10 = HCNVRT(UP_CAMP_VISIT_DATE,'(HYYMD)',10,'A10');
-* CAMP_LDT2/A8 = EDIT(CAMP_LDT,'9999$99$99');
INTV_LDT/A10 = HCNVRT(UP_INTERVIEW_DATE,'(HYYMD)',10,'A10');
INTV_LDT2/A8 = EDIT(INTV_LDT,'9999$99$99');
-* UVIS/I4 = (CAMP_LDT2 GT '0');
INTV/I4 = (INTV_LDT2 GT '0') AND (DECISION_CODE EQ '00');
SEX/A6 = DECODE GENDER (M Male F Female ELSE 'Needed');
END

-* Creating Report for Freshman Prospective Students.

TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Freshman Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'F'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS FRESHRPT FORMAT HTMLTABLE
END
-RUN

-* Creating Report for Transfer Prospective Students.

TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Transfer Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'T'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS TRANSRPT FORMAT HTMLTABLE
END
-RUN

-* Creating Report for Guest Prospective Students.

TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Guest Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'G'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS GUESTRPT FORMAT HTMLTABLE
END
-RUN

-* Creating Report for ReAdmit Prospective Students.

TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"ReAdmit Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'E'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS READMRPT FORMAT HTMLTABLE
END
-RUN

-* Displays all four sections of the report in HTML format. Each
-* section is displayed one at a time starting with the freshman
-* page. There are buttons at the top that allow you to select
-* the other 3 student types.

***This is where the htmlform went. If you need to see this, let me know where I can send you the full code.


The program above calls STUpStat_Middle.fex. This is the code for this part.

-* Program: STUpStat_Middle.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* This code is included four times in the STUpStat.fex program.
-* It is used to find the correct records for the statistical
-* for the four student types that appear on the report.
-**************************************************************************

SUM INQUIRED AS 'Inquired' OVER
GENERAL AS 'General File' OVER
INTV AS 'Interviewed' OVER
INPRO AS 'In Process' OVER
FILEC AS 'File Complete' OVER
REVIEW AS 'Review' OVER
DENIED AS 'Denied' OVER
CANCNOAC AS 'Can/Not Acc' OVER
CANCACC AS 'Can/Acc' OVER
CANCPD AS 'Can/Paid' OVER
WAIT_POOL AS 'Waiting Pool' OVER
CANCWAIT AS 'Can/Wait Pool' OVER
QUAL AS 'Qualified Status' OVER
ACCEPTED AS ' Accepted' OVER
PAID AS 'Paid' OVER
APPLIED AS 'Applied'
ACROSS HIGHEST TERM_CODE AS ''
ACROSS LOWEST SEX AS '' ACROSS-TOTAL AS 'Total'
WHERE (((TERM_CODE EQ '&TERM1') AND (BEGIN_NUMERIC_DATE LE '&DATE1')
AND (END_NUMERIC_DATE GT '&DATE1'))
OR
((TERM_CODE EQ '&TERM2') AND (BEGIN_NUMERIC_DATE LE '&DATE2')
AND (END_NUMERIC_DATE GT '&DATE2'))
-IF &TERMNUM EQ '2' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM3') AND (BEGIN_NUMERIC_DATE LE '&DATE3')
AND (END_NUMERIC_DATE GT '&DATE3'))
-IF &TERMNUM EQ '3' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM4') AND (BEGIN_NUMERIC_DATE LE '&DATE4')
AND (END_NUMERIC_DATE GT '&DATE4'))
-IF &TERMNUM EQ '4' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM5') AND (BEGIN_NUMERIC_DATE LE '&DATE5')
AND (END_NUMERIC_DATE GT '&DATE5'))
-L$DONEWHERE1
)
WHERE CAMPUS_CODE EQ 'U'
WHERE GENDER EQ 'M' OR 'F'


The first program also calls STUpStat_Style.fex. This is the code for that one.

-* Program: STUpStat_Style.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* This code sets the style for the Upland Statistical report. It is
-* called 4 times in the STUpStat.fex program for each student type.
-**************************************************************************

ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
PAGECOLOR=RGB(204 204 255),
$
TYPE=REPORT,
GRID=ON,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
WRAP='OFF',
$
TYPE=HEADING,
SIZE=14,
COLOR='MAROON',
STYLE=BOLD+ITALIC,
JUSTIFY=CENTER,
$
TYPE=TITLE,
BACKCOLOR=RGB(240 240 255),
WRAP='OFF',
$
TYPE=ACROSSVALUE,
BACKCOLOR=RGB(240 240 255),
$
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
ACROSS=2,
COLOR='MAROON',
JUSTIFY=RIGHT,
$

ENDSTYLE

Thanks for your help.

so..it is that you don't know how many TERM-CODES you have per SEX? is that it?

Actually it's how many sex per term-codes. But I'm not guarenteed how many terms (could be from 2 up to six) and what terms they may be.

here you go.
on the car file, given that we'll be doing "ACROSS COUNTRY ACROSS MODEL" and say we don't know how many of either that we have.
SET ASNAMES=ON
SET HOLDLIST = PRINTONLY
-* count how many of variable 2 are in each variable 1
TABLE FILE CAR
SUM CNT.DST.MODEL AS NUM BY COUNTRY NOPRINT ON TABLE HOLD
END
-RUN
-* count how many variable 1 we have
-SET &HOWMANY = &LINES;
DEFINE FILE HOLD
-* get the column number for each variable 2 acrosstotal
-* remembering to accumulate them and account for the total column itself
NUM2/I5 = 1 + NUM + LAST NUM2 ;
ANUM/A5 = EDIT(NUM2);
-* takes those blasted leading zeros out of the edit conversion
BNUM/A6 ='C' | TRIM('L', ANUM, 5,'0',1,'A5');
END
TABLE FILE HOLD PRINT BNUM ON TABLE HOLD AS HOWMANY
END
-RUN

-SET &MYCOLNUM = ' ' ;
-SET &KOUNTER = 0 ;
-REPEAT end.loop &HOWMANY TIMES
-SET &KOUNTER=&KOUNTER + 1 ;
-SET &CN&KOUNTER= 0;
-READ HOWMANY &MYCOLNUM.A6
-SET &CN.&KOUNTER = &MYCOLNUM ;
-*TYPE &CN.&KOUNTER
-end.loop
-SET &KOUNTER = 0 ;
TABLE FILE CAR
SUM SEATS
ACROSS COUNTRY ACROSS MODEL ACROSS-TOTAL
ON TABLE SET STYLE *
-*(here's a slight variation on the k.lane idea)
-REPEAT end.loop.2 &HOWMANY TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
-SET &COL = &CN.&KOUNTER ;
TYPE=DATA,COLUMN= &COL ,COLOR=RED,$
-end.loop.2
ENDSTYLE
END
-* interestingly, TYPE=TITLE won't work.
if you want to color both the title and the data, TYPE=REPORT, COLUMN= &COL , works.
Maybe someone else can tell us how to get TYPE=TITLE to work, as well.

Thank you. I'll try this as soon as I can.

Ok, mine is a little bit more complicated than this. Above you showed me a program off the car table that only has one field that is being summed...seats. So, when you calculate the columns that need to be made red, you move across by the number of models in each country. In this case NUM2/I5 = 1 + NUM + LAST NUM2 ; works. But, my problem is I have 16 fields that are being calculated for each sex in each term. There is an OVER after each of them so they appear in columns. So I can't just move across by the number of columns in each term. I hope this is making sense. Let imagine that term1 has 2 genders and term2 has 1 and term3 has 3 (that includes M, F and Needed). That means I need columns 33 through 48 in red, 65 through 80 in red and then 129 through 144 in red. I'm not sure how to make up an equation that gets these values for the column numbers.

i have confidence that you'll figure it out.
you have the theory, you just have to apply it.
the over doesn't have any impact.