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     Need help for COLUMN-TOTAL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Need help for COLUMN-TOTAL
 Login/Join
 
Gold member
posted
Hello All!

Simple problem: I want to show Field1 Field2 Field3 Field4 Field5 in a Subtotal line but only Field1 Field4 Field5 in my COLUMN-TOTAL. I did

ON FieldX SUBTOTAL Field1 Field2 Field3 Field4 Field5 AS 'SubTot ';

...

ON TABLE COLUMN-TOTAL AS 'Grand Total' TOTCOST FOVERD SOVERD

But the Column-Total show all 5 fields!! How can I change that?

Thanks. Confused
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
<Pietro De Santis>
posted
Use

ON TABLE SUBTOTAL TOTCOST FOVERD SOVERD AS 'Grand Total'
 
Report This Post
Gold member
posted Hide Post
Oops, I didn't want to show the real column names in my example: My code is -->

ON FieldX SUBTOTAL Field1 Field2 Field3 Field4 Field5 AS 'SubTot ';

...

ON TABLE COLUMN-TOTAL AS 'Grand Total' Field1 Field4 Field5

Pietro,
ON TABLE SUBTOTAL Field1 Field4 Field5 AS 'Grand Total'
didn't work! I still get the 5 columns in the Grand Total.
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
<Pietro De Santis>
posted
This should work:

ON TABLE SUBTOTAL Field1 Field4 Field5 AS 'Grand Total '
 
Report This Post
Gold member
posted Hide Post
Well, I'm using 5.2.5 and specifying less fields in the ON TABLE COLUMN-TOTAL or ON TABLE SUBTOTAL than in the Field SUBTOTAL doesn't work at all.

No matter what, I always get 5 fields in my Table total when I have 5 fields specified in my Field Subtotal line.

Try this, it doesn't work:

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
MPG
BY
CAR
BY
COUNTRY
ON COUNTRY SUBTOTAL DEALER_COST RETAIL_COST SALES AS 'STotal '
ON TABLE COLUMN-TOTAL AS 'G Total ' DEALER_COST SALES
END
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Virtuoso
posted Hide Post
How about doing something with a SubFoot?

TABLE FILE CAR
SUM
DEALER_COST RETAIL_COST SALES
MPG
BY
CAR
BY
COUNTRY
ON COUNTRY SUB-TOTAL DEALER_COST RETAIL_COST SALES AS 'STotal '
ON TABLE SUBFOOT
"TOTAL <TOT.RETAIL_COST <TOT.SALES"
END
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
Thanks for the idea but...

I've already thought about doing this but it doesn't look good. My reports are either created as HTML, Excel or PDF and I want the Grand Total line to be exactly like other lines on the report with values aligned with the columns. Also I already have a page footing and a table subfoot defined in my report so this one would appear between these.

I really need to have less fields shown on the Table Total than in my Field Subtotal.

Is it something that can't be done in WF?

p.s. I tried to put the fields I don't wanna see the same color as my background but in Excel the values is still in the cell...
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Virtuoso
posted Hide Post
Well if you use the NOTOTAL..YOU won't get the first total line on the SUBTOTAL...and you can align by using TABFOOTING...Just another thought.

TABLE FILE CAR
SUM
DEALER_COST RETAIL_COST SALES
MPG
BY
CAR
BY
COUNTRY
ON COUNTRY SUB-TOTAL DEALER_COST RETAIL_COST SALES AS 'STotal '
ON TABLE NOTOTAL
ON TABLE SUBFOOT
"G-TOTAL <+0> <+0> <TOT.RETAIL_COST <TOT.SALES "
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY,$

TYPE=TABFOOTING, OBJECT=FIELD, ITEM=4, JUSTIFY=RIGHT, STYLE=BOLD, SIZE=9,$
TYPE=TABFOOTING, OBJECT=FIELD, ITEM=5, JUSTIFY=RIGHT, STYLE=BOLD, SIZE=9,$
ENDSTYLE
END
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
<Pietro De Santis>
posted
Would changing the colour of the undesired totals in the sub-total and the table sub-total to white help?
 
Report This Post
<Pietro De Santis>
posted
TABLE FILE CAR
SUM
SEATS
DEALER_COST
RETAIL_COST
SALES
LENGTH
WIDTH
HEIGHT
WEIGHT
BY COUNTRY
ON COUNTRY SUBTOTAL RETAIL_COST LENGTH HEIGHT
BY CAR
ON TABLE COLUMN-TOTAL DEALER_COST WIDTH WEIGHT
HEADING
" "
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='VERDANA', SIZE=8, $
TYPE=SUBTOTAL, COLUMN=DEALER_COST, COLOR=WHITE, $
TYPE=SUBTOTAL, COLUMN=WIDTH, COLOR=WHITE, $
TYPE=SUBTOTAL, COLUMN=WEIGHT, COLOR=WHITE, $

TYPE=GRANDTOTAL, COLUMN=RETAIL_COST, COLOR=WHITE, $
TYPE=GRANDTOTAL, COLUMN=LENGTH, COLOR=WHITE, $
TYPE=GRANDTOTAL, COLUMN=HEIGHT, COLOR=WHITE, $
ENDSTYLE
END
 
Report This Post
Gold member
posted Hide Post
Thanks Pietro, I've already thought about puting the unwanted subtotal fields in the background color but in Excel, the values are still there...

Thanks Prarie22 but like I said, doing something else than a table total looks like crap, even if you use tabfooting. I'va already tried it and don't like it.

Thanks to everybody but my question was really: Can I have less fields in my grtand total than in my subtotal?

Looks like WF can't do it, too bad, that's stupid. Mad
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Platinum Member
posted Hide Post
There's always a way. Sometimes it's painful. This is one of those times. I don't think the code below is as streamlined as it could be, and the resulting report might not be exactly 100% what you seek. But if it's not, it's very close, and you can get there from here. I would provide a report image, but those don't display so well here. Guess you'll just have to run it.

SET ASNAMES=ON

-STEP1
DEFINE FILE CAR
TABLECTR/I1 WITH CAR = 2;
COUNTRY2/A20 = 'GRAND TOTAL';
FAKE_COUNTRY/A20 = 'ZZZZZZZZZZZZZZZZZZZZ';
CAR2/A16 = ' ';
DEALER_COST2/D9 = 0;
MPG2/I7 = 0;
END

TABLE FILE CAR
SUM
RETAIL_COST AS RETAIL_TBL
SALES AS SALES_TBL
DEALER_COST2 AS DEALER_TBL
MPG2 AS MPG_TBL
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY2 AS COUNTRY
BY CAR2 AS CAR
ON TABLE HOLD AS HOLDTBL
END
-RUN

-STEP2
DEFINE FILE CAR
TABLECTR/I1 WITH CAR = 1;
COUNTRY2/A20 WITH COUNTRY = '*SUBTOTAL ' | COUNTRY;
FAKE_COUNTRY/A20 = COUNTRY | 'YYYYYYYYYY';
CAR2/A16 = ' ';
END

TABLE FILE CAR
SUM
RETAIL_COST AS RETAIL_CTRY
SALES AS SALES_CTRY
DEALER_COST AS DEALER_CTRY
MPG AS MPG_CTRY
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY2 AS COUNTRY
BY CAR2 AS CAR
ON TABLE HOLD AS HOLDCTRY
END
-RUN

-STEP3
DEFINE FILE CAR
TABLECTR/I7 WITH CAR = 1;
COUNTRY2/A20 = COUNTRY;
FAKE_COUNTRY/A20 = COUNTRY | ' ';
END

TABLE FILE CAR
SUM
DEALER_COST AS DEALER_CAR
RETAIL_COST AS RETAIL_CAR
SALES AS SALES_CAR
MPG AS MPG_CAR
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY2 AS COUNTRY
BY CAR
ON TABLE HOLD AS HOLDCAR
END
-RUN

-STEP4
MATCH FILE HOLDTBL
PRINT
DEALER_TBL
RETAIL_TBL
SALES_TBL
MPG_TBL
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY
BY CAR
RUN
FILE HOLDCTRY
PRINT
DEALER_CTRY
RETAIL_CTRY
SALES_CTRY
MPG_CTRY
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY
BY CAR
RUN
FILE HOLDCAR
PRINT
DEALER_CAR
RETAIL_CAR
SALES_CAR
MPG_CAR
BY TABLECTR
BY FAKE_COUNTRY
BY COUNTRY
BY CAR
AFTER MATCH HOLD AS RPTFILE OLD-OR-NEW
END
-RUN

-STEP5
DEFINE FILE RPTFILE
DEALER_COST/I7S = DEALER_TBL + DEALER_CTRY + DEALER_CAR;
RETAIL_COST/I7S = RETAIL_TBL + RETAIL_CTRY + RETAIL_CAR;
SALES/I6S = SALES_TBL + SALES_CTRY + SALES_CAR;
MPG/I6S = MPG_TBL + MPG_CTRY + MPG_CAR;
END

TABLE FILE RPTFILE
PRINT
DEALER_COST/I9CS
RETAIL_COST/I9CS
SALES/I9CS
MPG/I6CS
BY TABLECTR NOPRINT
BY FAKE_COUNTRY NOPRINT
BY COUNTRY
BY CAR
END
-RUN
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Gold member
posted Hide Post
Thanks for the help, as you said that's really painful, kind of PITA just for that Mad . I'm gonna keep the grand totals I don't want same color as background for now... That's a really good solution though. Wink
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Gold member
posted Hide Post
Hi all,

I'm reading this forum every now and then, but here comes the first post Wink .

Would/could the following solution help?

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
MPG
COMPUTE XBY/I1 = 1 ; NOPRINT
BY TOTAL XBY NOPRINT
BY
COUNTRY
BY
CAR
ON XBY SUBFOOT
"G Total <+0> <ST.DEALER_COST <ST.SALES"
ON COUNTRY SUBTOTAL DEALER_COST RETAIL_COST SALES AS 'STotal '
ON TABLE NOTOTAL
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
ENDSTYLE
END
 
Posts: 58 | Location: Sydney, Australia | Registered: April 22, 2005Report 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     Need help for COLUMN-TOTAL

Copyright © 1996-2020 Information Builders