Steven Carter Posted January 31, 2022 Posted January 31, 2022 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 Posted January 31, 2022 Posted January 31, 2022 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 Posted February 1, 2022 Author Posted February 1, 2022 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 Posted February 1, 2022 Posted February 1, 2022 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 Posted February 2, 2022 Posted February 2, 2022 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 Posted February 7, 2022 Posted February 7, 2022 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 Posted February 8, 2022 Posted February 8, 2022 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 Posted February 16, 2022 Posted February 16, 2022 Steven, welcome to the myibi community! Were the recent suggestions posted here helpful
David Briars Posted March 18, 2022 Posted March 18, 2022 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
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now