Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Sovled]Subtotals on criteria but still display all data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Sovled]Subtotals on criteria but still display all data
 Login/Join
 
Platinum Member
posted
Is it possible to have subtotals for a and b only. Example:

Product,Revenue,Units
xxxx nnnn nnn
vvvv nnnn nnn
cccc nnnn nnn

Subtotals:
Flag A nnnn nnn
Flag B nnnn nnn

Grand totals nnn nnn

Flag is a field not displayed on the main output but only in the subtoal. Thanks for any assistance! Smiler

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


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
if you try to explain your question based on the CAR database then it might help

in the men time you should take a look at the mcguyver solution, that might help you




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Joe,

If the number of subtotals change at every sort break and you don't know ahead of run-time how many there are, check out my article MacGyver Technique for Dynamic Number of Grouped Subtotals/Subfoots.

This techniques works in any version, but works best in version 7.6.11 or higher because of the new set command, DROPBLNKLINES. It removes the blank line WebFocus adds right above the subtotal.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
<FreSte>
posted
Hi,

Below you'll find another technique of how these kind of reports can be made.
With this, one can create very flexible reports.
(in this example, things could ofcourse also be accomplished with DEFINE's, but
just for this example I used TABLE-requests).

-Fred-


-* ------------------------------------------------------------------
-* Go to database only once to get records
-* ------------------------------------------------------------------
TABLE FILE CAR
  SUM
    DEALER_COST
  BY COUNTRY
  BY CAR
  BY BODYTYPE
  BY SEATS
  ON TABLE HOLD AS HLDSOURCE FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Append records for the following TABLE-requests to same HOLD-file
-* Make sure all TABLE-requests produce the same kind of HOLD file,
-* ------------------------------------------------------------------
FILEDEF hldtotal DISK hldtotal.ftm (APPEND
-RUN

-* ------------------------------------------------------------------
-* Get all records
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE SRT/I1 = 1;
    DEALER_COST
  BY COUNTRY
  BY CAR
  BY BODYTYPE
  BY SEATS
  ON TABLE HOLD AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only Italy and Japan
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'Japan / Italy';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 2;
    DEALER_COST
  WHERE COUNTRY IN('JAPAN','ITALY');
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only 4-SEATS cars
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'all 4 seats';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 3;
    DEALER_COST
  WHERE SEATS EQ 4;
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only SEDAN's
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'all sedans';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 4;
    DEALER_COST
  WHERE BODYTYPE EQ 'SEDAN';
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* The end result
-* ------------------------------------------------------------------
TABLE FILE HLDTOTAL
  SUM
    DEALER_COST
  BY SRT NOPRINT
  BY COUNTRY
  ON COUNTRY SUBTOTAL AS 'Total'
  WHEN SRT EQ 1
  ON COUNTRY SUBFOOT
  " "
  WHEN SRT EQ 1
  BY CAR
  BY BODYTYPE
  ON TABLE SET HTMLCSS ON
  ON TABLE NOTOTAL
  ON TABLE SET STYLE *
  TYPE=REPORT, BORDER=1, BORDER-COLOR=RGB(230 230 230), FONT=VERDANA , SIZE=10,$
  TYPE=SUBTOTAL, BACKCOLOR=RGB(245 245 245) ,$
  TYPE=DATA, BACKCOLOR=RGB(225 225 225), WHEN=SRT EQ 2 ,$
  TYPE=DATA, BACKCOLOR=RGB(200 200 200), WHEN=SRT EQ 3 ,$
  TYPE=DATA, BACKCOLOR=RGB(175 175 175), WHEN=SRT EQ 4 ,$
  ENDSTYLE
END

This message has been edited. Last edited by: <FreSte>,
 
Report This Post
Platinum Member
posted Hide Post
Hi Fred, I agree your approach will work when the number of subtotals are known and you only have one layer of subtotals/totals you want displayed at the end of the report. A complex series of DEFINES could also work in place of separate TABLES. And you could also create REPEAT/LOOPS for appending data which is what I used in the past successfully and again in my early attempts for my current report. But once I saw the MacGyver Webinar, it became clear to me that the number of subtotals or number of differents views on the data could be dynamically handled through only one pass of the data. Which is really important if you are working with large tables.

David



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Platinum Member
posted Hide Post
Fred's example worked well. Thanks! The only problem I see with it is when there is a drill down field. It appears that the subtotals inheret the same formats and as what the main body of data has which I assumed would happen. I guess there's not a way to format around this? Thanks!


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
<FreSte>
posted
Joe,

I think there is; just add a condition in the stylesheet for the drill-down column like:

...
TYPE=DATA, COLUMN=CAR, FOCEXEC=WHATEVER(), WHEN=SRT EQ 1,$
....


-Fred-
 
Report This Post
Platinum Member
posted Hide Post
Thanks! I thin that will work.


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Sovled]Subtotals on criteria but still display all data

Copyright © 1996-2020 Information Builders