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     COLUM-TOTAL on fields which are sorted using BY

Read-Only Read-Only Topic
Go
Search
Notify
Tools
COLUM-TOTAL on fields which are sorted using BY
 Login/Join
 
<hellonitiv>
posted
Is it possible to get COLUMN-TOTALs on fields which are sorted using BY clause. Below is the code snippet:
SUM TOTAL_CNT/D9 AS ''
BY VESSEL AS ' Vessel '
BY VOYAGE AS ' Voyage '
BY ALLOCATION_COUNT AS 'Allocation'
ACROSS TRADE_LANE_CD AS ' Trade '
ACROSS MVMT_TYPE AS ''
ON VESSEL SUB-TOTAL AS 'Total'
ON TABLE SUBTOTAL AS 'TOTAL TEU'
From this code snippet, i am getting totals of all the numbers which are stored in TOTAL_CNT column. But i want to display Totals on ALLOCATION_COUNT column as well.
I think the SUB-TOTAL does not sums up any of the sort fields.
Is there any alternate way to achieve this?
Please help.
 
Report This Post
Leah
Virtuoso
posted Hide Post
Add another subtotal statement for the allocation_count. As I understand the difference also between subtotal and sub-total is whether you get 'subtotals' after the by. If sub-total is at a lower by level you will get 'subtotals' for all higher levels'


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
susannah
Expert
posted Hide Post
create a second variable, a version of allocation count that you will SUM.
then sum newallocationcount total_cnt
by vessel
by voyage
by allocation_count noprint
then your subtotal will work fine.



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
<hellonitiv>
posted
quote:
achieve

Hi Susannah,
Thanks a lot for the suggestion.
But, it does not work the way i would have liked it to.
I think you ignored the ACROSS keywords used in the report.
Had the ACROSS not been used, your solution would have worked perfectly fine.
Since i am using ACROSS, if i try to put new_allocatin_count with total_cnt(using SUM), it (ACROSS) applies to both these columns(used with 'SUM' verb), which i dont want. i want ACROSS to be applied only to TOTAL_CNT.
I would like to achieve SUBTOTALS directly on the BY fields.
Please suggest some alternate way, if any.
Thanks in advance!!!
 
Report This Post
Alan B
Virtuoso
posted Hide Post
The only easy way I know would be to use a subfoot
  
SUM TOTAL_CNT/D9 AS ''
BY VESSEL AS ' Vessel '
BY VOYAGE AS ' Voyage '
BY ALLOCATION_COUNT AS 'Allocation'
ACROSS TRADE_LANE_CD AS ' Trade '
ACROSS MVMT_TYPE AS ''
ON VESSEL SUB-TOTAL AS 'Total'
ON VESSEL SUBFOOT
"Total Allocation <ST.ALLOCATION_COUNT"
ON TABLE SUBTOTAL AS 'TOTAL TEU'

and use the style sheet to position the field in the subfoot under the correct column.

This does give 2 lines for the subtotal though.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
How about this?

TABLE FILE CAR
SUM SEATS AS 'Allocation'
BY COUNTRY AS ' Vessel '
BY CAR AS ' Voyage '
BY SEATS NOPRINT
SUM SALES AS ''
BY COUNTRY AS ' Vessel '
BY CAR AS ' Voyage '
BY SEATS NOPRINT
ACROSS MPG IN-GROUPS-OF 5 AS ' Trade '

ON COUNTRY SUB-TOTAL AS 'Total'
ON TABLE SUBTOTAL AS 'TOTAL TEU'
END

Or, using your code:
SUM ALLOCATION_COUNT AS 'Allocation'
BY VESSEL AS ' Vessel '
BY VOYAGE AS ' Voyage '
BY ALLOCATION_COUNT NOPRINT
SUM TOTAL_CNT/D9 AS ''
BY VESSEL AS ' Vessel '
BY VOYAGE AS ' Voyage '
BY ALLOCATION_COUNT NOPRINT
ACROSS TRADE_LANE_CD AS ' Trade '
ACROSS MVMT_TYPE AS ''
ON VESSEL SUB-TOTAL AS 'Total'
ON TABLE SUBTOTAL AS 'TOTAL TEU'

GOOD LUCK!

This message has been edited. Last edited by: Danny-SRL,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
Actually Danny, in retrospect both these methods bring in an anomoly, which is data dependent and may cause an issue.

Because there are models within BMW, the SUM of the seats for W GERMANY would come out as 34, instead of the expected 14, which would be the sum of the visible values in the BY. If you remove the NOPRINT from the SEATS, you will see what I mean. In your example you can overcome this with using SUM FST.SEATS, and in my example you can't! Which I think makes your example a more universal approach.

It is a question of which is the correct value to be shown. It is no fun trying to explain to a user that the sum of 5+4+5=34!


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
<hellonitiv>
posted
Hi Alan,
Thanx for help. It seems to be working but doesn't exactly fit to my requirements.

Hi Danny,
Your solution is almost perfect in terms of report display. But somehow the Totals on the BY sort field i.e. "Allocation" are not correct.
The totals are almost 5-10 times greater than the expected ones.
Any clue as to why totals are not correct.
Please suggest.
Thanks in advance!!!
 
Report This Post
Alan B
Virtuoso
posted Hide Post
I think our entries are crossing.

Try using SUM FST.ALLOCATION_COUNT as in my previous entry.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
<hellonitiv>
posted
Hi Alan, Danny,
You guys are great.
My problem is solved with .FST function.
Thanx a ton!!!
 
Report This Post
<hellonitiv>
posted
Hi Alan,
I was using a style statement on ALLOCATION_COUNT column, which was working if i don't use FST. function.
But the style stmt does not work if FST is used. My style code is like this:
TYPE=DATA, COLUMN=ALLOCATION_COUNT, JUSTIFY=CENTER,COLOR=RED,WHEN=ALLOCATION_COUNT EQ 0, $
Is there any solution/workaround to overcome this!!
 
Report This Post
Danny-SRL
Virtuoso
posted Hide Post
I suppose it is because in your style when you refer to ALLOCATION_COUNT, it is the sort field which is used. You could use FST.ALLOCATION_COUNT in the style or the print number of ALLOCATION_COUNT, P3 if I am not mistaken.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Alan,
You are correct about the qualifier for SEATS.
I'll add another small point: if you are reporting from an SQL database, it is better to use MAX or MIN instead of FST, the latter not having an equivalent in SQL.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
<hellonitiv>
posted
Hi Danny,
FST.ALLOCATION_COUNT works for the style statment.
Thanks again!!!
Have a good day.
Big Grin
 
Report This Post
Alan B
Virtuoso
posted Hide Post
Very true Danny.

My SQL is 5 years rusty and not up to date at all. But I may have to get more practice in, I just learnt today that I will have to access an SQL datasource for reporting AND update.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report 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     COLUM-TOTAL on fields which are sorted using BY

Copyright © 1996-2020 Information Builders