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     Subtotal with detail

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Subtotal with detail
 Login/Join
 
reFOCUSing
Guru
posted
I will start off by showing you what I want to do:

SORT__COST__HOURS___AMOUNT
1_____3_____2_______6
1_____4_____5_______20
1_____5_____6_______30
Sub total 1_________56

2___________3_________
2___________7_________
Sub total 2_________67

Here is what raw data looks like:
SORT__COST__HOURS___AMOUNT
1_____3_____2_______6
1_____4_____5_______20
1_____5_____6_______30
2_____6_____3_______18
2_____7_____7_______49

So my problem is how can I produce a subtotal without showing the information I'm summing?

I put underscores in to show the spacing they are not actually in the data.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Leah
Virtuoso
posted Hide Post
Have you considered a subfoot on the sort key,


SET STYLEMODE = FIXED
TABLE FILE CAR
PRINT COUNTRY SEATS DEALER_COST RETAIL_COST
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
"<+25>SUBTOTAL END

Gives:
PAGE 1


COUNTRY SEATS DEALER_COST RETAIL_COST
------- ----- ----------- -----------
ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL 45,319
FRANCE 5 4,631 5,610
SUBTOTAL 5,610
ITALY 4 4,915 5,925
ITALY 2 5,660 6,820
ITALY 2 5,660 6,820
ITALY 2 25,000 31,500
SUBTOTAL 51,065
JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL 6,478
W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL 64,732
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
reFOCUSing
Guru
posted Hide Post
This does not work for me since I don't want to show the individual numbers that I am creating a subtotal for.

For SORT 1 I want to show all the values in the data but for SORT 2 I only want show the column HOURS with a subtotal on AMOUNT.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Leah
Virtuoso
posted Hide Post
So you're looking for something like this if I interpret your last response correctly:

PAGE 1


SUBTOTAL FOR ENGLAND 45,319
SUBTOTAL FOR FRANCE 5,610
SUBTOTAL FOR ITALY 51,065
SUBTOTAL FOR JAPAN 6,478
SUBTOTAL FOR W GERMANY 64,732

SET STYLEMODE = FIXED
TABLE FILE CAR
SUM
COMPUTE LITA/A15 = 'SUBTOTAL FOR '; AS ''
COUNTRY AS ''
RETAIL_COST AS ''
BY COUNTRY NOPRINT
END

Of course if sort field is not alpha, you my have to do something to keep the value of it.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
reFOCUSing
Guru
posted Hide Post
kind of. I will make the example relate to the car table.

This is want I'm trying to do.
I want to see a subtotal for retail_cost under each grouping of country. I want to see detail information from the columns seats, dealer_cost and retail_cost for each country except italy. For italy I want to see only the column seats and I still want to have a subtotal on retail_cost.

Based on the data you posted the first time this is the output I'm trying to get.
COUNTRY SEATS DEALER_COST RETAIL_COST
------- ----- ----------- -----------
ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL 45,319

FRANCE 5 4,631 5,610
SUBTOTAL 5,610

ITALY 4
ITALY 2
ITALY 2
ITALY 2
SUBTOTAL 51,065

JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL 6,478

W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL 64,732
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
<WFUser>
posted
The only problem seems to be how to print field for all sorts except for one. You will probably end up with 2 reports. You can put them under each other with HTML or a compound PDF doc. For the sorts where you are printing everything, that is no problem. For the other (Italy) why can't you just do this.

TABLE FILE CAR
SUM SEATS
BY COUNTRY
ON COUNTRY SUBFOOT
"TOTAL RCOST < RCOST>"
BY MODEL NOPRINT
END
 
Report This Post
Leah
Virtuoso
posted Hide Post
Okay, how's this:

SET STYLEMODE = FIXED
TABLE FILE CAR
PRINT
COUNTRY AS ''
SEATS NOPRINT
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE SEAT/D7S = C2; AS ''
COMPUTE DEALERC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C3; AS ''
COMPUTE RETAILC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C4; AS ''
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
" SUBTOTAL FOR END

PAGE 1


ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL FOR ENGLAND 45,319
FRANCE 5 4,631 5,610
SUBTOTAL FOR FRANCE 5,610
ITALY 4
ITALY 2
ITALY 2
ITALY 2
SUBTOTAL FOR ITALY 51,065
JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL FOR JAPAN 6,478
W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL FOR W GERMANY 64,732
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
reFOCUSing
Guru
posted Hide Post
If anyone else reads this Leah's code should read as follows:
TABLE FILE CAR
PRINT
COUNTRY AS ''
SEATS NOPRINT
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE SEAT/D7S = C2; AS ''
COMPUTE DEALERC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C3; AS ''
COMPUTE RETAILC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C4; AS ''
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
"SUBTOTAL FOR <ST.RETAIL_COST"
END

The < messed up the last bit of it.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report 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     Subtotal with detail

Copyright © 1996-2020 Information Builders