I need to write a series of focexecs that will have start dates and end dates as part of the selection critria.
This is using WebFOCUS 7.1.3.
I would like the default start date to be day 1 of the previous month of the current year and the default end date to be the last day of that month. I have found that -DEFAULT only works when you hard-code a value, such as 20070101. The variables do not translate properly when doing calculations -- the code shows rather than the value. No sytax errors, we have found this to be a consistent behavior.
The focexec is reporting off of a SQL table which has an I11 format for the date field.
So, my questions are: 1) How do I get DM to cooperate? 2) How could this be done with Java script? 4) Can the Calendar option in the HTML Painter do this?
I also have reports that need to have not only the date range for the current year, but also the same months and days in the previous year. It should be as simple as substracting 10000 from the start and end date of the current year to get the start and end dates of the previous year.
So, 4) How do I get DM to cooperate? 5) How could this be done with Java script? 6) Can the Calendar option in the HTML Painter do this?
Thanks,
John
WF 7.7.03, Windows 7, HTML, Excel, PDF
February 24, 2007, 03:04 PM
susannah
John, you'll want to use the AYM and AYMD functions; Look them up in the 4th manual in your set ("Using Functions"). DM works just fine with these functions, using, for ex, the systemdate as a seed basis.
Here's an example, using &YYMD . (i prefer it to &DATE.)
Now, in your fex, you can compare a smart date to these integer forms just fine. eg, if your database date is MYDATE/YYMD then you can say IF MYDATE GE &STARTDATE or IF MYDATE FROM &STARTDATE TO &ENDDATE and it works just dandy. This takes care of your questions 1 and 4 (you'll be able to figure out last year the same way)
-SET &THISDAYLY=&YYMD - 10000 ;
...etc
You say you're comparing to an I11 formatted field...I don't know what is in your I11... and why its 11 and not 8, but you can figure it out from here, i'm sure. Give it a try.
Questions 2 and 3 your best bet is a .js forum. Questions 3 and 6, beats me, sorry. I'll defer to a gui person.
-S.This message has been edited. Last edited by: susannah,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 24, 2007, 03:29 PM
Alan B
This type of date manipulation in javascript?
Not unless you are very brave with plenty of time. There are no built in functions to help, so it is a complete manual calculation.
Alan. WF 7.705/8.007
February 25, 2007, 04:37 PM
JohnB
Thanks, Susan.
-SET works fine. However, -DEFAULT is where the problem lies. It only seems to work when hardcoding a value for the start or end date for that I11 field. Otherwise it displays the expression. How can I get -DEFAULT to work?
I was told the reason for the I11 "date" field is it was from some old legacy application.
WF 7.7.03, Windows 7, HTML, Excel, PDF
February 25, 2007, 05:15 PM
Alan B
-DEFAULTS is just that, setting an initial value in the instance where a variable does not exist. No calculations or expressions allowed.
I don't really understand why you have to use -DEFAULTS, if you are using an expression then -SET would do the same thing.
Can you expand on what you are trying to achieve.
Alan. WF 7.705/8.007
February 25, 2007, 05:46 PM
susannah
ah...i see. yes you're right. you can't DEFAULT to an &VAR..won't work. you need to do this in 2 steps.. -DEFAULT for the user input dates then -SET for the variable you're actually going to use in your filters. -SET &STARTDATE= ... what we did above using &YYMD -DEFAULT &USERSTART= 00000000 ; ... this sets an empty value for a user-supplied date..and if it is empty or still 00000000 at run time, then use the calculated &STARTDATE else override with the User-supplied date. ...now compare the 2 -SET &STDATE = IF &USERSTART IS 00000000 THEN &STARTDATE ELSE &USERSTART ; and your filter is IF MYDATE GE &STDATE ... i do this sort of thing with a variable called &LAUNCH. it is set to &LAUNCH='DEFAULT' when the user hits my page for the first time; then from my paramter launch page i have a hidden variable LAUNCH=LIVE ; i then control all my filters on whether LAUNCH = DEFAULT or not. ... does that I11 date look like this: 00020070725 ?? ... but as Alan says, in your particular example, you're always calculting a startdate from the &YYMD..so you don't really need to default anything?This message has been edited. Last edited by: susannah,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 26, 2007, 09:27 AM
JohnB
I need to use -DEFAULT so it's value can be overriden by user input. The values that can be supplied for begin and end dates can be any range.
WF 7.7.03, Windows 7, HTML, Excel, PDF
February 26, 2007, 09:35 AM
JohnB
Re: does that I11 date look like this: 00020070725 ??
Yes.
WF 7.7.03, Windows 7, HTML, Excel, PDF
February 26, 2007, 09:52 AM
Alan B
As you cannot use -DEFAULTS to do what you want, then you will have to use -SET.
Susannah has given a solution that will work, giving the ability to have an &variable overridden by user input.
Call the fex passing the STARTDATE and ENDDATE variables or not passing the STARTDATE and ENDDATE variables. There will be a difference.
The key to defaulting a DM variable to a DM variable is the .EVAL.
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
February 26, 2007, 11:36 PM
susannah
i tried that code, Francis, in both 5 and 7, and i got nada. i got the character string &WSTARTDATE.EVAL as the content of &STARTDATE. how did you make it work?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 27, 2007, 12:53 AM
Francis Mariani
The code gives me the following as the result - in both self-serve and MRE:
WSTART 20070101 WEND 20070131
START 20070101 END 20070131
I scanned my code and found quite a few programs with this type of stuff. It would appear to conform with what the manual states.
When the command procedure is executed, the expression is replaced with the value of the specified variable before any other action is performed. Without the .EVAL operator, a variable cannot be used in place of some commands.
And I found this in the IBI supplied samples:
C:\ibi\apps\ibinccen\hrlist2.fex
-DEFAULTS &SORTBY = &DIMENSION.EVAL
(Just finished watching the Colbert Report)
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
February 27, 2007, 01:41 AM
susannah
was he good tonight? post oscars, must have been. razzing JohnStewart. ok, so why can't i make it work? i copied your code, pasted it, and ran it. 5 and 7. boy, would i like to use it, if i could. When i run, my autoprompt screen shows inside the boxes , the character string &WENDDATE.EVAL Do you see that?This message has been edited. Last edited by: susannah,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 27, 2007, 03:30 AM
Alan B
Using the parameter prompting of XMLPROMPT for IBIF_wfdescribe will prompt for &variables started with a -DEFAULTS. Setting this to XMLRUN will stop that behaviour.
For some reason, not aligned to autoprompt, and I cannot remember why, I stopped using -DEFAULTS quite a while back, and just used -SET. So for this type of scenario I would use:
Where &STARTDATE could be supplied by the user or not. This would be compatible with having auto-prompt set for XMLPROMPT. On that note also, I cannot find a single instance of me having used .EVAL. Different folks, different strokes.
Alan. WF 7.705/8.007
February 27, 2007, 10:30 AM
Francis Mariani
Susannah,
I just tried the code in MRE with Prompt for Parameters set to on and it does not work. I hadn't tested this as the original question did not mention MRE.
Sorry about that.
I do know some programmers that have too many .EVAL cards in their back pockets, but I have had occasion to use .EVAL.
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
February 27, 2007, 10:32 AM
susannah
ah , that makes sense. how do you say thank you in portuguese? If i change XMLPROMPT to XMLRUN, i wonder what else it will screw up?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 27, 2007, 01:15 PM
FrankDutch
I have put these values like &&LASTWORKDAY &&THISYEARSTART &&THISMONT &&PREVIOUSMONTH
etc with the use of formulas like Susannah mentioned
in my site profile, so I can use them in every report.
Frank
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
July 07, 2008, 04:07 PM
susannah
-DEFAULT &STARTDATE = &YYMD.EVAL ; works fine for me now its in unix don't know if that makes a difference. but it won't work in a launch form the launch form displays the string '&YYMD.EVAL' which ain't too swell.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID