I have a report that looks like this: image.png811522 16.6...

[Steven Carter]

I have a report that looks like this:

 

image.png811522 16.6 KB

 

The code looks like this:

 

SUM

NEW_ADMITS AS New Admits OVER

READMITS AS Readmits OVER

CONTINUING OVER AS Continuing

TOTAL_ENROLLMENTS AS Total Enrollments

BY COLLEGE SUBTOTAL AS College Total:

BY PROGRAM AS Program

ACROSS ACAD_YEARS AS

ACROSS SEMESTERS AS

 

I have figured out how to style the column titles (i.e., New Admits, Readmits, etc.) that appear in the data rows because of my use of OVER. But I cannot figure out what stylesheet declaration to use in order to change the BACKCOLOR in the SUBTOTAL rows.

This does NOT work:

 

TYPE=SUBTOTAL,

COLUMN=TITLE,

BACKCOLOR=GOLD,

 

Have any other suggestions

[David Briars]

steve.carter:

 

But I cannot figure out what stylesheet declaration to use in order to change the BACKCOLOR in the SUBTOTAL rows

 

 

Can you try

TYPE=SUBTOTAL,

BY=COLLEGE,

BACKCOLOR=GOLD,$

[Steven Carter]

Thanks, David. I had not tried that combination. Unfortunately, it did not work. That combination of

 

TYPE=SUBTOTAL,

BY=COLLEGE,

 

 

Only affected the parts that were already gold. It did not change styling in the titles.

[Martin Yergeau]

Seems that the OVER will always discard styling

Long way, but can manually be done as below with programming

TABLE FILE CAR

SUM DCOST AS 'DCOST'

RCOST AS 'RCOST'

BY TOTAL COMPUTE GTROW /I1 = 1;

BY TOTAL COMPUTE STROW /I1 = 1;

BY COUNTRY

BY CAR

BY BODYTYPE

ON TABLE HOLD AS DETDATA

END

-RUN

 

TABLE FILE DETDATA

SUM DCOST AS 'VALUE'

BY GTROW

BY STROW

BY TOTAL COMPUTE ROWID /I1 = 1;

BY COUNTRY

BY CAR

BY BODYTYPE

ON TABLE HOLD AS DCSTDATA

END

-RUN

 

TABLE FILE DETDATA

SUM RCOST AS 'VALUE'

BY GTROW

BY STROW

BY TOTAL COMPUTE ROWID /I1 = 2;

BY COUNTRY

BY CAR

BY BODYTYPE

ON TABLE HOLD AS RCSTDATA

END

-RUN

 

TABLE FILE DCSTDATA

SUM VALUE

BY GTROW

BY STROW

BY ROWID

BY COUNTRY

BY CAR

BY BODYTYPE

ON TABLE HOLD AS DETDATA

MORE

FILE RCSTDATA

END

-RUN

 

TABLE FILE DETDATA

SUM VALUE

BY GTROW

BY TOTAL COMPUTE STROW /I1 = 2;

BY ROWID

BY COUNTRY

BY TOTAL COMPUTE CAR /A16 = 'Total';

BY BODYTYPE

ON TABLE HOLD AS STOT

END

-RUN

 

TABLE FILE DETDATA

SUM VALUE

BY TOTAL COMPUTE GTROW /I1 = 3;

BY TOTAL COMPUTE STROW /I1 = 3;

BY ROWID

BY TOTAL COMPUTE COUNTRY /A10 = 'Total';

BY TOTAL COMPUTE CAR /A16 = '';

BY BODYTYPE

ON TABLE HOLD AS GTOT

END

-RUN

 

TABLE FILE DETDATA

SUM VALUE

BY GTROW

BY STROW

BY ROWID

BY COUNTRY

BY CAR

BY BODYTYPE

ON TABLE HOLD AS RPTDATA

MORE

FILE STOT

MORE

FILE GTOT

END

-RUN

 

DEFINE FILE RPTDATA

ROWTXT /A20V = DECODE ROWID (1 'Dealer Cost' 2 'Retail Cost');

END

TABLE FILE RPTDATA

SUM VALUE AS ''

BY GTROW NOPRINT

BY COUNTRY AS 'Country'

BY STROW NOPRINT

BY CAR AS 'Car'

BY ROWID NOPRINT

BY ROWTXT AS ''

ACROSS BODYTYPE AS 'Body'

RECOMPUTE AS 'Total'

ON TABLE NOTOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,

$

DEFMACRO=STLINE,

MACTYPE=RULE,

WHEN=STROW EQ 2,

$

DEFMACRO=GTLINE,

MACTYPE=RULE,

WHEN=GTROW EQ 3,

$

TYPE=DATA,

BACKCOLOR=GOLD,

MACRO=STLINE,

$

TYPE=DATA,

BACKCOLOR=RGB(0 0 255),

COLOR=WHITE,

MACRO=GTLINE,

$

END

-RUN

[David Briars]

Oh, I didnt see the use of OVER to stack data, when I replied the first time.

I dont think the chapter Identifying a Report Component in a WebFOCUS StyleSheet in the Creating Reports WebFOCUS manual documents a way to identify OVERs within a SUBTOTAL.

Unless someone knows of a way, and we are just not seeing it

I believe OVERs are from very early on when the reporting language was used for plain text formats only.

It looks like your fundamental requirement is to stack measures; and then display the stacked measures sorted down and across the page (matrix report).

An excellent way to stack measures is the create extracts (HOLD files) and then MORE them together, as Martin shows.

Another way to stack measures is to JOIN to a dummy file. JOINing to a dummy file will allow you to create n number of output records from a single input record.

You will sometimes see this technique called the MacGyver Technique. MacGyver being a fictional detective with many tools at his disposal, and then the MacGyver Technique being a tool to handle many complex reporting requirements.

By way of example, using the sample file GGSALES:

 

image.png530658 20.2 KB

 

-*

-* Report Environment Settings.

-*

SET PAGE = OFF

SET NODATA = 'N/A'

-RUN

-*

-* Create temporary Fixed Sequential (FSEQ) master file.

-*

EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM

FILENAME=FSEQ,SUFFIX=FIX

SEGNAME=CHAR1,SEGTYPE=S0

FIELDNAME=BLANK,BLANK,A1,A1,$

SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE

FIELDNAME=CHAR,CHARS,A1,A1,$

FIELDNAME=COUNTER,ORDER,I2,I4,$

-RUN

-* Allocate temporary Fixed Sequential (FSEQ) data file.

FILEDEF FSEQ DISK FSEQ.FTM

-RUN

-* Load data to temporary Fixed Sequential (FSEQ) data file.

-WRITE FSEQ ONEBLANKTHENDUMMYDATA

-RUN

-*

-* Create 'Sales' report.

-*

-* JOIN to Fixed Sequential (FSEQ) file.

JOIN BLANK WITH SEQ_NO IN GGSALES TO BLANK IN FSEQ AS J1

-* Temporary fields:

DEFINE FILE GGSALES

BLANK/A1 WITH SEQ_NO = ' ';

MEASUREDESC/A16 = DECODE COUNTER (1 'Actual Units'

2 'Budgeted Units'

3 'Total Units'

4 'Actual Units'

5 'Budgeted Units'

6 'Total Units');

MEASURE/I08C = IF COUNTER EQ 1 OR 4 THEN UNITS ELSE IF COUNTER EQ 2 OR 5 THEN BUDUNITS ELSE UNITS + BUDUNITS ;

NEW_PRODUCT/A24 = IF COUNTER GE 4 THEN 'Total' ELSE PRODUCT;

GOLDBACK_YN/A1 = IF COUNTER GE 4 THEN 'Y' ELSE 'N';

END

-*

TABLE FILE GGSALES

"UNITS Report"

"Actual vs. Budget Across Region By Category and Product"

SUM MEASURE

GOLDBACK_YN NOPRINT

BY CATEGORY

BY NEW_PRODUCT AS 'Product'

BY COUNTER NOPRINT

BY MEASUREDESC AS ''

ACROSS REGION AS ''

IF COUNTER LE 6

ON TABLE SET STYLE *

INCLUDE = ENWarm, $

TYPE=DATA, BACKCOLOR= RGB(249 229 172), WHEN = GOLDBACK_YN EQ 'Y' ,$

ENDSTYLE

END

 

Ill generally use the file concatenation or MacGyver techniques over the OVER technique in production systems.

[Dirk Kuerbig]

One option, if Subtotals give you issues with your styles, you can always revert to SUBFOOT syntax, and display the ST. (section total) prefix.

Please see an example of a WebFOCUS report using the CAR sample database below.

For comparisons, I have kept the SUBTOTAL and SUBFOOT results in the output. Please remove the subtotals once you are happy with what you see

The results show up like this.

 

image.png19201080 116 KB

 

 

ENGINE INT CACHE SET ON

SET PAGE-NUM=NOLEAD

SET SQUEEZE=ON

-DEFAULTH &WF_HTMLENCODE=ON;

SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON

-DEFAULTH &WF_EMPTYREPORT=ON;

SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=2;

SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY=Summary;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

TABLE FILE ibisamp/car

SUM CAR.BODY.SEATS

OVER CAR.BODY.DEALER_COST

OVER CAR.BODY.RETAIL_COST

OVER COMPUTE SALES_D20/D20=CAR.BODY.SALES; AS SALES

COMPUTE MYLABEL_SUBTOTAL_COUNTRY/A200='Subtotal: | CAR.ORIGIN.COUNTRY; NOPRINT

-* You can comment out the next line, once you have confirmed that the resulting data in the sub footing is identical to what the aub totals show.

BY CAR.ORIGIN.COUNTRY RECOMPUTE SUM. AS Subtotal:

ON CAR.ORIGIN.COUNTRY SUBFOOT

<MYLABEL_SUBTOTAL_COUNTRY<+0>Seats:<ST.SEATS

" <+0> <+0>Dealer Cost:<ST.DEALER_COST"

" <+0> <+0>Retail Cost:<ST.RETAIL_COST"

" <+0> <+0>Sales:<ST.SALES_D20"

BY CAR.COMP.CAR

ON TABLE PCHOLD FORMAT HTML

ON TABLE NOTOTAL

ON TABLE SET CACHELINES 100

ON TABLE SET GRWIDTH 1

ON TABLE SET STYLE *

INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$

TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, $

TYPE=SUBFOOT,HEADALIGN=BODY,$

TYPE=TITLE, COLUMN=N3, BACKCOLOR=RGB(255 255 128), $

TYPE=TITLE, COLUMN=N4, BACKCOLOR=RGB(255 255 128), $

TYPE=TITLE, COLUMN=N5, BACKCOLOR=RGB(255 255 128), $

TYPE=TITLE, COLUMN=N6, BACKCOLOR=RGB(255 255 128), $

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, JUSTIFY=RIGHT, SIZE=10, BACKCOLOR=RGB(32 32 32),COLOR=RGB(240 240 240),$

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, LINE=1, ITEM=1, OBJECT=FIELD, STYLE=BOLD, COLSPAN=2, JUSTIFY=LEFT,$

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, LINE=1, OBJECT=TEXT, STYLE=BOLD, JUSTIFY=LEFT, $

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, LINE=2, OBJECT=TEXT, STYLE=BOLD, JUSTIFY=LEFT, $

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, LINE=3, OBJECT=TEXT, STYLE=BOLD, JUSTIFY=LEFT, $

TYPE=SUBFOOT, BY=CAR.ORIGIN.COUNTRY, LINE=4, OBJECT=TEXT, STYLE=BOLD, JUSTIFY=LEFT, $

ENDSTYLE

END

-RUN

 

Please note: it is not really necessary to calculate / compute the subtotal label, if you are happy with unnecessary white space.

Please let me know if you need further explanations.

[David Briars]

Yep, replacing SUBTOTAL with the powerful SUBFOOT command is always something to look at.

If I understood the requirement correctly, the desired output report is a matrix report - BYs and ACROSS.

I think when the requirement is for a matrix report, in makes designing a SUBFOOT challenging.

[Sarah Buccellato]

Steven, welcome to the myibi community!

Were the recent suggestions posted here helpful

[David Briars]

DavidBriars:

 

You will sometimes see this technique called the MacGyver Technique. MacGyver being a fictional detective with many tools at his disposal, and then the MacGyver Technique being a tool to handle many complex reporting requirements.

 

 

FYI - The next FOCUS Friday VUG will have the MacGyver Technique as one of its topics:

https://www.tibco.com/events/focus-fridays-vug-focus-graphics-macgyver-technique-customizing-app-studio-maintain