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     Excel Formula - Sum Total

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Excel Formula - Sum Total
 Login/Join
 
Sayed
Guru
posted
Hello,

I am trying to achieve the following report in excel formula. Any help would be appreciated.
  
         A		B			C		 	D	
1	ENGLAND		225			10			=B1/C1
2	JAPAN		100			15			=B2/C2
3	USA		400			25			=B3/C3
4
5	Total		=SUM(B1:B3)	         =SUM(C1:C3)	         =SUM(D1:D3)
6
7	Last Month	550			35			=B7/C7
9
10	Diff		=B5-B7		         =C5-C7		         =D5-D7


Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
j.gross
Virtuoso
posted Hide Post
You could generate the report as shown in FRL, but attemppting to hold an FRL report as Excel With Formulas results in...

(FOC3273) EXL2K: FORMULAS WILL NOT BE GENERATED WITH OVER OR FOR.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Sayed
Guru
posted Hide Post
Jack,

Thanks for the suggestion but I need to be able to hold the formulas. The user needs to be able to update cell values and the formulas will dynamically chage values of other cells.

Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
j.gross
Virtuoso
posted Hide Post
I suggest you use an Excel template for your report -- the Detail data filled in by WF in one tab, and your presentation (with precoded formulas referencing the detail data) in another.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Sayed
Guru
posted Hide Post
I thought about that but cannot use it for this case because the # of rows can change in my report.

Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Sayed,

I think this could be an answer for you:

  
-* File sayed1.fex
DEFINE FILE CAR
XCOUNTRY/A10 = IF SEATS EQ 2 OR 5 THEN COUNTRY ELSE 'L. Month';
XSORT/I1S=IF SEATS EQ 2 OR 5 THEN 0 ELSE 1;
RC/D7=IF SEATS EQ 2 OR 5 THEN RCOST ELSE -RCOST;
DC/D7=IF SEATS EQ 2 OR 5 THEN DCOST ELSE -DCOST;
END
TABLE FILE CAR
SUM RC DC  COMPUTE RATIO/D7.2=IF XSORT EQ 0 THEN DC/RC ELSE -DC/RC;
BY XSORT AS '' 
BY XCOUNTRY AS Country
ON XSORT SUBTOTAL AS 'Total' 
WHEN XSORT EQ 0;

ON TABLE PCHOLD FORMAT EXL2K FORMULA

ON TABLE SET STYLE *
TYPE=REPORT, COLUMN= XSORT, COLOR=WHITE, $
ENDSTYLE
END


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
Sayed
Guru
posted Hide Post
Danny,

Thanks for the input. Unfortunately, the excel formula the accounting department is trying to use is far more complex and manipulates too many cells. Therefore we decided to go with excel macros. We are getting the excel dump from WebFOCUS and then enable macro to divide the report into tabs with template and formula.

Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 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     Excel Formula - Sum Total

Copyright © 1996-2020 Information Builders