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***Extract Month and Year from System Date As Integers

Read-Only Read-Only Topic
Go
Search
Notify
Tools
***CLOSED***Extract Month and Year from System Date As Integers
 Login/Join
 
Master
posted
I need to extract the month as I2 field and the year as an I4 field. I need this based on the current date. Ex: if today is 07/29/2014, I need to extract Month = 7 and Year = 2014. Month and Year should be intergers. I have had luck in the past with:

COMPUTE DATE_DAY/D = DATE_MDYY ;
COMPUTE DAY_I2/I2 = DATE_DAY ;
COMPUTE DAY_A2/A2 = EDIT(DATE_DAY);

However I am not sure how to apply this to the system date (or what the appropriate & varaiable for the system date is) or if these functions are even applicable.

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


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
This will get the date from when the report started.

It uses the Date amper variables.

Take a look at the documentation, there are many more date variables.

TABLE FILE CAR
PRINT COMPUTE
Day/I2 = &DATED ;
Month/I2 = &DATEM ;
Year/I4 = &DATEYY ;

BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END


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
Master
posted Hide Post
quote:
&DATEM

Awesome thanks!

Now the next problem. I am updating several fexes. They all start with the same DEFAULT and said default is referenced in a WHERE condition later on. There are 10 or so of these grap/report fexes all rendered via an HTML launch page. We want to get away from the hard coded FYCURRENT shown below:

APP HOLD foccache
-DEFAULT &FYCURRENT = 2014;

I am tasked with eliminating the hard coded value. It needs to be replaced with a variable...something along these lines:

IF &DATEM GE 8 AND &DATEM LE 12 THEN &DATED + 1 ELSE &DATED;


I am unsure how to do this. Do I create a define and set the default to that...I had no success. Can I set the default directly? Again no success.

How can I do this?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Member
posted Hide Post
hello,

As System Date is a kind of constant, you can simply use Dialog MAnager in order to get your values.

example :
  -SET &YEAR = &DATEYY ; will provide 2014 as YEAR value.

-SET &MONTH = &DATEM ; will provide 07 for july but
-SET &MONTH = &DATEM + 0 ; will provide 7 for july.

The same is also valid for the day :
-SET &DAY = &DATED + 0 ; in order to get 1 or 30 for the day.



Using Dialog manager is faster than doing a TABLE with RECORDLIMIT in order to read values in a file.


Rgds,

Patrick


8207, Windows 2016 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Suresnes | Registered: August 26, 2010Report This Post
Member
posted Hide Post
hello,

in order to remove hardcoded value, you can just type :

APP HOLD foccache
-DEFAULT &FYCURRENT = &DATEYY.EVAL ;

IF you do a 
-TYPE &FYCURRENT you'll get 2014

 


Do you still need APP HOLD FOCCACHE ?

Rgds,
Patrick


8207, Windows 2016 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Suresnes | Registered: August 26, 2010Report This Post
Member
posted Hide Post
You can still use Dialog manager in order to do this :

 

-SET &MYVARIABLE = 
IF &DATEM GE 8 AND &DATEM LE 12 THEN &DATED + 1 ELSE &DATED;


 


and use &MYVARIABLE later.


8207, Windows 2016 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Suresnes | Registered: August 26, 2010Report This Post
Member
posted Hide Post
Hi RobertF,
You can do like following to add Month, Day and Year in Date, I used this following Function :

Syntax :
HADD(datetime, 'component', increment, length, output)

This function works for Date and Time adding.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 5 | Location: Pune, India | Registered: June 19, 2014Report This Post
Master
posted Hide Post
quote:
-DEFAULT &FYCURRENT = &DATEYY.EVAL ;

All,
I need to have DEFAULTS set for various reasons. Technically there may be others ways to do this, but I have numerous fexes already set up in this manner. I really need to derive the default value.

I just tried this:
-DEFAULT &FYCURRENT = &DATEYY.EVAL ;

It did not work. Upon execution of a fex, the parameter boxes pop up with my default values for various parameters. The default for FYCURRENT is NOT 2014 but rather the text: &DATEYY.EVAL . That does not yield an error until: RUN is hit on the paramter screen. Then the code that generates the first hold file yields this error:

0 NUMBER OF RECORDS IN TABLE= 2037 LINES= 2037
0 ERROR AT OR NEAR LINE 36 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: &DATEYY.EVAL
0 ERROR AT OR NEAR LINE 63 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: &DATEYY.EVAL
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Here is the basic code structure. Note the DEFAULTS are in place in order to be able to get into the advanced graphing tool…plus it made testing easier when originally developed. Further we are still undecided as to whether we want the user selecting a year or not. Note that when run in production, the HTML launch page prompts for values that override the defaults. Fo now, we simply no longer want the user to select a Fiscal Yea, we want to derive the value to be in effect based on the month we are in.

Code:

-DEFAULT &FYCURRENT = &DATEYY.EVAL ;
-DEFAULT &IDXSPECIALTY = 'FAMILY MEDICINE';
-DEFAULT &IDXROLLUP = 'OTHER';
-DEFAULT &BILLPROVNAME = JOHNSON MD,RICHARD';
-DEFAULT &RPTBANBR = 3022;

TABLE FILE TBLMSTR_UNITSWRVUS
.
.
.
WHERE J1.TBLMSTR_CALENDARPERIOD.FISCALYEAR EQ &FYCURRENT OR J1.TBLMSTR_CALENDARPERIOD.FISCALYEAR EQ &FYCURRENT - 1;

WHERE TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.IDXSPECIALTY EQ '&IDXSPECIALTY';

WHERE TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.IDXROLLUP EQ '&IDXROLLUP';
.
.
.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
quote:
Fo now, we simply no longer want the user to select a Fiscal Yea, we want to derive the value to be in effect based on the month we are in.



Clarification...RE:
Fo now, we simply no longer want the user to select a Fiscal Year, we want to derive the value to be in effect based on the month we are in.



Once I get the FYCURRENT default to derive the year from the current date's year, I plan to maninulate the date on the current month we are in....I'll worry about that later.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Member
posted Hide Post
this is strange, what about testing by removing the WHERE based on the date and adding a heading in order to display the value ?
just add an -EXIT after your TABLE.

Try to add ' ' in the default

Add a .EVAL to each &FYCURRENT in your WHERE

I don't have anymore 7.6 release available.

Rgds,


8207, Windows 2016 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Suresnes | Registered: August 26, 2010Report This Post
Master
posted Hide Post
quote:
&DATEYY.EVAL


Thanks but no luck..it does not appear to like the .EVAL. Is there some sort of option I must turn on for it work?

It scares me to think a DEFAULT can not be assigned a variable!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
Have you looked at the Fiscal Year function? FIYR


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Master
posted Hide Post
I think the issue is more one of how to set the default to a variable without future code utilizing the deafult erroring out.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
Fiscal year is August through July
  
-SET &FYCURRENT  = IF &DATEM GE '08' THEN &DATEYY + 1 ELSE &DATEYY;
-SET &FYPREVIOUS = &FYCURRENT - 1;

WHERE J1.TBLMSTR_CALENDARPERIOD.FISCALYEAR IN (&FYCURRENT, &FYPREVIOUS);
 

And, if FISCALYEAR is really an ALPHA then:
 
WHERE J1.TBLMSTR_CALENDARPERIOD.FISCALYEAR IN ('&FYCURRENT.EVAL', '&FYPREVIOUS.EVAL');


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
Thank you...that may be working. So if the SET statement comes AFTER the DEFAULT statement, it overrides the DEFAULT value in the WHERE condition?

I ask because I still need the DEFAULT statement...as soon as I remnove it I can no longer open teh Advanced graphing assistant.

So lets say I default FYCURRENT to 2025 (we would have no data for this year.) I then SET FYCURRENT as you described.

I run the fex.

My paramter screen pops up with: 2025 defaulted in the FYCURRENT box. I leave it be. I hit: RUN.

It seems the WHERE condition is now evaluating FYCURRENT as 2014...at least I think it is.

I probably should find a way to hide (or white out) the FYCURRENT parameter on the HTML launch page since it could lead to confusion....

I will further test this...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
RobertF,
Remove it all together, "IF", it doesn't need to be selected.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
To remove the prompting of the parameters, just uncheck the property : "Prompt for Parameters" from the Managed Reporting fex or html.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Ehhhh! All this apparently works for report selection and I have learned quite a bit but...it falls apart once I get to the html launch page.

The reason the FYCURRENT parameter is there is apparently to limit subsequent parameter values the user may choose...ie we have CHAINING in effect and require FYCURRENT (year) for that.

So, bottom line, the user need not see the parameter: FYCURRENT but it needs to be there to control the chained values they see for subsequent prompts....and we want to derive it as opposed to them selecting it.


Again, the trick really is how to get the default value for FYCURRENT derived and fed to the parameter box.

How can this be so complicated? All I really want to do is say something like this:

DEFAULT FYCURRENT =IF &DATEM GE 8 AND &DATEM LE 12 THEN &DATED + 1 ELSE &DATED;

(I know that won't work)


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Gold member
posted Hide Post
I did a little testing, and it appears you can only -DEFAULT to a static value. Since you want the default year to vary on your HTML page, what about using Javascript in the onInitialUpdate function of your page? In more recent versions of WebFOCUS, there is a function called IbComposer_setCurrentSelection to do this, but there are other ways to do this with Javascript, depending on the type of control you are using for the year.


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Master
posted Hide Post
Dave's tip:

Don't use the javascript function to do this.

There is an easier way.

Make a .fex that outputs whatever you want as a default value.

Then fill the html-input with this .fex ( like you'd fill a dropdownbox with available valus ).

G'luck.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Master
posted Hide Post
I can try that but if the FY is in effect in one table (fex) and populates a parameter but the other parameters are in another table populating the remaining parameters...I am unsure the chaining will work...will try though


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
HA!
Well I created a fex that yeild the year I want. I pointed my parameter to it on the html screen. I left my other parametrs point towards the other data source. I crossed my fingers that somehow it would all magically work and chain.

Well, the FYCURRENT poulates with the right year now!

Sadly, the other 3 parameters do not show up at all!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
Wait...I take that back. I now see all parameters but chaning is gone....going to try to go int, break the bindings and rechain....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
I THINK ITS WORKING!

I need to test. When I pointed the FYCURRENT parameter to the fex, many of the parameter setting were lost. I put them back and I think its working.

I need to test though.

THANKS ALL!!!!!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report 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***Extract Month and Year from System Date As Integers

Copyright © 1996-2020 Information Builders