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] What's wrong with this fex?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] What's wrong with this fex?
 Login/Join
 
Francis Mariani
Expert
posted
Running the following fex in PDF and HTML output gives me three years, 1997, 1996 and 1990. Running it in EXL2K output gives me 35431, 35065 and 32874. Is there something wrong with the DEFINE? ORDER_DATE is defined as MDY in the GGORDER master.

DEFINE FILE GGORDER
YEAR1/YY = ORDER_DATE;
YEAR/A4YY = YEAR1;
QTY/D6 = QUANTITY;
END

TABLE FILE GGORDER
SUM
QTY AS ''
ACROSS HIGHEST YEAR AS ''
BY PRODUCT_DESCRIPTION

HEADING
"GG ORDERS BY PRODUCT"
ON TABLE PCHOLD FORMAT EXL2K

ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=ACROSSVALUE, JUSTIFY=RIGHT, COLOR=NAVY, STYLE=BOLD, $
ENDSTYLE
END


Thanks,

This message has been edited. Last edited by: Francis Mariani,


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
Doug
Expert
posted Hide Post
Wouldn't that (what's wrong with this fex) be the format of the destination column in Excel? Setting that format of that column to a date will convert those numbers?

That would change tose to : 1/1/1997, 1/1/1996, and 1/1/1990.

I would include the slashes in the dates so that Excel automatically sees the dates as dates.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Waz
Expert
posted Hide Post
Perhaps leaving it as format YY will stop the number.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
j.gross
Virtuoso
posted Hide Post
Solution: make the reported YEAR field a plain (non-date) A4.

(you need the define with A4YY in order to cast the YY to a character variable;
but you can then redefine the A4YY as an A4, without changing the fieldname, so no change in the TABLE code)

DEFINE FILE GGORDER
YEAR1/YY = ORDER_DATE;
YEAR/A4YY = YEAR1; 
YEAR/A4 = YEAR;    <==
QTY/D6 = QUANTITY;
END
. . .


And of course that will work in the other output formats as well.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
GamP
Virtuoso
posted Hide Post
DEFINE FILE GGORDER
YEAR/YY = ORDER_DATE;
QTY/D6 = QUANTITY;
END

Indeed Waz is right. Just leave it as YY.
I have no idea why an A4YY field behaves like this in excel, but if you really want to kknow, I'd suggest to open a case with IB asking for an explanation.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Thanks everyone. I didn't really require a solution, I wanted to know what was wrong with the code. It's strange that the A4YY field is treated as numeric for EXL2K only.

This message has been edited. Last edited by: Francis Mariani,


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
Tony A
Expert
posted Hide Post
quote:
It's strange that the A4YY field is treated as numeric for EXL2K only.

Not really Francis, as Excel will treat anything that looks like a numeric as numeric. But that's Good ol' Microshaft interpreting what they think you want because, of course, no Microshaft user ever knows what they want to do, do they!

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
Francis Mariani
Expert
posted Hide Post
I simplified the DEFINE and it works:

DEFINE FILE GGORDER
YEAR/YY = ORDER_DATE;
QTY/D6 = QUANTITY;
END

TABLE FILE GGORDER
SUM
QTY AS ''
ACROSS HIGHEST YEAR AS ''
BY PRODUCT_DESCRIPTION

HEADING
"GG ORDERS BY PRODUCT"
ON TABLE PCHOLD FORMAT PDF

ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=ACROSSVALUE, JUSTIFY=RIGHT, COLOR=NAVY, STYLE=BOLD, $
ENDSTYLE
END

So, now YEAR is a DATE field passed to Excel which doesn't treat it as a number, go figure!

Thanks,


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
  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] What's wrong with this fex?

Copyright © 1996-2020 Information Builders