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     [CLOSED] Select Display Row-totals with over

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Select Display Row-totals with over
 Login/Join
 
PBax
Gold member
posted
Im trying to do something which I thought would be relevently simple but cant see the wood for the trees & havent been able to find a suitable answer so far,

Im basically trying to do a report with several over fields & a row-total (or across total) but I dont want to row-total all my fields or at least not display them.

Using the car file as an example.

TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES OVER
LENGTH
BY COUNTRY

ACROSS CAR
ON TABLE ROW-TOTAL
END

This would show row totals for all the fields within the SUM statement, but I would only want to display the row-total for DEALER_COST.

I have got round displaying the information by formatting the text to white for those totals I do not wish to see but they still print out. Therefore, i would like if possible to not display them at all.

Part of me hopes that I am just being a numpty as I can live with the shame Smiler

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


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
Well, I don't know what a numpty is but I surely wouldn't want to be one either! Smiler And your question is legitimate. Here is a quick take though not perfect:
SET ASNAMES=ON
DEFINE FILE CAR
TOTCAR/A16='ZTOTAL';
TOTRCOST/D7=0;
TOTSALES/I6=0;
TOTLEN/D5=0;
END
TABLE FILE CAR
SUM   DEALER_COST
      TOTRCOST AS RETAIL_COST
      TOTSALES AS SALES
      TOTLEN AS LENGTH
BY COUNTRY
BY TOTCAR AS CAR
ON TABLE HOLD AS TOTCAR FORMAT ALPHA
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES OVER
LENGTH 
BY COUNTRY
ACROSS CAR
-*ON TABLE ROW-TOTAL
MORE 
FILE TOTCAR
END

Now in the first TABLE, I made TOTCAR ZTOTAL so that it would sort at the end because I was being lazy and didn't want to code the Dialogue Manager -REPEAT loop necessary to do the ACROSS COLUMNS syntax to get them to sort in the correct order. But in my defense, that would take the fun out of it for you. And I think there are a number of posts related to that in the Forum. Search on ACROSS COLUMNS and see what you find. If you need a further explanation of what I did, let me know and I will supply.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
This had me stumped and Ginny's solution is pretty good!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
quote:
numpty

99 definitions (some of them cannot be posted as the formum filters will **** them)

http://www.urbandictionary.com/define.php?term=numpty
 
Report This Post
Hua
Guru
posted Hide Post
Thanks Jinny. My cup of morning coffee on across-total. Smiler
quote:
MORE
FILE TOTCAR



Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Dan Satchell
Virtuoso
posted Hide Post
This is not very elegant, but offers a different approach. Converting amounts you don't want totalled into alpha format prevents them from being ROW-TOTALed.

TABLE FILE CAR
  SUM DEALER_COST
      RETAIL_COST
      SALES
      LENGTH
   BY COUNTRY
   BY CAR
   ON TABLE HOLD
END
-*
DEFINE FILE HOLD
 RCOSTA/A10  = FTOA(RETAIL_COST,'(D7)','A10');
 SALESD/D6   = SALES ;
 SALESA/A10  = FTOA(SALESD,'(D6)','A10');
 LENGTHA/A10 = FTOA(LENGTH,'(D5)','A10');
END
-*
TABLE FILE HOLD
   SUM DEALER_COST
  OVER RCOSTA  AS 'RETAIL_COST'
  OVER SALESA  AS 'SALES'
  OVER LENGTHA AS 'LENGTH'
    BY COUNTRY AS ''
ACROSS CAR     AS ''
    ON TABLE ROW-TOTAL
    ON TABLE SET STYLE *
       TYPE=DATA, JUSTIFY=RIGHT, $
    ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
PBax
Gold member
posted Hide Post
Thanks for the replies folks! Smiler

As soon as I get some urgent year end stuff done I will take a look .

numpty in my book is just being a bit simple/stupid Smiler But i do love description no2 on the urban dictionary link (been so hectic in the office as of late its given me a nice 'out loud' laugh which had my colleagues asking what was so funny Smiler


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 2006Report This Post
PBax
Gold member
posted Hide Post
Thanks all. This forum is a REAL wealth of knowledge!! Smiler

As feeback, Ginnys suggestion gave me what I wanted but gave me zero where I didnt want to display the data & Dans gave me what I wanted but I wasnt to keen on subjecting myself to inserting all the FTOA defines (theres a lot of data on these reports.)

I had tried doing a simple version of the more file before asking the question but Ginny put me on the right tracks. With the addition of S after the formats in the 1st sub request I got what I wanted. waahaay!

Example of finalised code below if it helps anyone else:


SET ASNAMES=ON

DEFINE FILE CAR
TOTCAR/A16='ZTOTAL';
TOTRCOST/D7=;
TOTSALES/I6=0;
TOTLEN/D5=0;
END
TABLE FILE CAR
SUM DEALER_COST
TOTRCOST AS RETAIL_COST
TOTSALES AS SALES
TOTLEN AS LENGTH
BY COUNTRY
BY TOTCAR AS CAR
ON TABLE HOLD AS TOTCAR FORMAT ALPHA
END

TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST/D7S OVER
SALES/I6S OVER
LENGTH/D5S
BY COUNTRY
ACROSS CAR
MORE
FILE TOTCAR
END


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 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     [CLOSED] Select Display Row-totals with over

Copyright © 1996-2020 Information Builders