Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
***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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport 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


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport 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.


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport 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, 2014Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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,


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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, 2010Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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, 2009Reply With QuoteReport 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: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport 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 8.
Windows, All Outputs
 
Posts: 541 | Registered: June 28, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.