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     [SOLVED] exl2k and templates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] exl2k and templates
 Login/Join
 
Platinum Member
posted
I have been reading a ton of posts about exl2k and templates for the last 2 days, but I haven't found any in regards to what I'm doing wrong. I thought I wasn't picking up my template, but I just realized I am picking it up. I looked at sheet 2 (which is not used but I did define it in the mht) and my columns 5 - 8 are marked as currency. In my sheet 1, it's like it's overriding the mht. My columns 5 - 8 are all custom (with the heading being general). I took off all formatting of my fields 5 - 8, but it's still showing up as custom. Any thoughts on what I'm doing wrong? This is the code.

SET NODATA = ' '
DEFINE FILE RATES_COMBINED2
MKTVL_PLUS_ACCR/P17.2=GROSS_END_MKT_VAL + GROSS_END_ACCRUED_INCOME;
GROSS_ROR2/D18.4=GROSS_ROR;
NET_ROR2/D18.4=NET_ROR;
VERIFIED_DATE/MDYY=VERIFICATIONDATE;
END

TABLE FILE RATES_COMBINED2
PRINT
ACCOUNT_SELECTED AS 'Account Number'
NBTBSGAC.SHRT_NAME AS 'Account Name'
GROSS_START_DATE AS 'From'
GROSS_END_DATE AS 'To'
VERIFIED_DATE AS 'Verified As of Date'
MKTVL_PLUS_ACCR AS 'Ending Market Value'
GROSS_ROR2 AS 'Total Return,Gross of Fees'
NET_ROR2 AS 'Total Return,Net of Fees'
BY ACCOUNT_SELECTED NOPRINT
BY GROSS_START_DATE NOPRINT
BY GROSS_END_DATE NOPRINT
ON TABLE SET PAGE-NUM OFF

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'perf_hist_template' SHEETNUMBER 1

END

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


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Expert
posted Hide Post
quote:
In my sheet 1, it's like it's overriding the mht.

Which is exactly what it does.

When you specify a particular sheet as a target, WebFOCUS replaces the target sheet with the Excel output (ish) including styling etc. etc.

If you want to have formats then you must apply them via VBA.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Pam,

WebFOCUS uses Excel templates by populating one or more sheets. Cells in other sheets refer to the cells in these WebFOCUS-populated sheets - these are the sheets that can be reformatted in Excel.

I just wanted to confirm that your code is populating sheet 1 of perf_hist_template. Does sheet 2 refer to cells in sheet 1? Reformatted columns 5 - 8 are in sheet 2?


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
Platinum Member
posted Hide Post
Francis - sheet 2 does not refer to cells in sheet 1. I just created my mht and formatted sheet 1 and sheet 2 by putting a space in column 1 (on both sheets), formatted columns 5 - 8 as currency (on both sheets) and then saved it.

What I am trying to do is ... my business wants to see 2 digit decimals (123.45) in the column, but if they click on the column they want to see 4 digit decimals (123.4567) in the formula box at the top of excel. I was told I could do this using templates. So, I'm sending 4 digits after decimal to excel spreadsheet, but I have the mht defined as 2 digit after the decimal format currency.

Is this something I can do?


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Platinum Member
posted Hide Post
Tony - what is VBA?


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Expert
posted Hide Post
The formatting should only be done in sheet 2 - sheet 1 is where WebFOCUS puts the data - any formatting there will be overridden by the WebFOCUS report. Sheet 1 should be ignored by the end user. There are discussions on how to hide the WebFOCUS-populated sheets (sheet 1 in your case).

I usually put the WebFOCUS-populated sheets AFTER the one that the user should be looking at, and I clearly name these as "data" or something to identify them.

VBA - Visual Basic for Applications. Excel macros use VBA. It is another way to format a WebFOCUS-generated spreadsheet after it gets created.


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
Virtuoso
posted Hide Post
A possible alternative solution is to use output format EXL2K FORMULA. Columns formatted by WebFOCUS as Dxx.2 will appear in the spreadsheet with two decimals, but show the full set of decimals when displayed in the formula box.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
Dan, I've never used FORMULA but that worked perfectly! Thank you so much for the suggestion. Much easier solution.


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
Pam, I'm glad it worked. And thanks to Francis' response to your post, I learned some more about using Excel templates, which I've never used before.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 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     [SOLVED] exl2k and templates

Copyright © 1996-2020 Information Builders