I have the following graph code but I am trying to figure out how to use a variable/function that will return current year instead of hardcoding '2009' in the report. And I don't want the user to be prompted.
Please can someone help?This message has been edited. Last edited by: umun,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
November 27, 2009, 10:28 AM
GamP
-SET &CYEAR=EDIT(&YYMD,'9999'); GRAPH FILE RETAIL SUM __OVER_30_DAYS_DELNQNT ACROSS MONTH1 BY ORGANISATION WHERE ( CONVT_TO_CRRNCY EQ 'EUR' ) AND ( YEAR1 EQ '&CYEAR' );
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 30, 2009, 09:35 AM
jgelona
Instead of 2009 use &DATEYY.
If you need the current month, use &DATEM
If you need the current day, use &DATED.
I find that &DATE followed by any valid date format is quite useful.
For example:
DO THIS GET THIS
-TYPE &DATE 10/13/09
-TYPE &DATEYYMD 2009/10/13
-TYPE &DATEMtRDYY October 13, 2009
-TYPE &DATEMtDYY Oct 13, 2009
-TYPE &DATEMTrDYY OCTOBER 13, 2009
-TYPE &DATEMTDYY OCT 13, 2009
-TYPE &DATEWMDYY TUE, 10/13/2009
-TYPE &DATEWRMDYY TUESDAY, 10/13/2009
-TYPE &DATEWRMTDYY TUESDAY, OCT 13 2009
-TYPE &DATEwrMtDYY Tuesday, Oct 13 2009
-TYPE &DATEwrMtrDYY Tuesday, October 13 2009
This message has been edited. Last edited by: jgelona,
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
December 02, 2009, 07:43 AM
umun
Hi GamP, You are too much! It worked. Thank you very much. The next stage of my project is to display the graph for current month and last 3 months.
Please, can you let me know how to get the current month (e.g if December is current month, the value should be '122009'. if July, value= '072009') and how to get the last 3 months.
Thank you.This message has been edited. Last edited by: umun,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
December 02, 2009, 08:18 AM
umun
Hi GamP, You are too much! It worked. Thank you very much. The next stage of my project is to display the graph for current month and last 3 months.
Please, can you let me know how to get the current month (e.g if December is current month, the value should be '122009'. if July, value= '072009') and how to get the last 3 months.
Thank you.[/QUOTE]
I am able to get current month now with -SET &CMONTH=EDIT(&DMYY,'$$999999');
I am still working on getting last three months. Any idea?
ThanksThis message has been edited. Last edited by: umun,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
December 02, 2009, 09:34 AM
GinnyJakes
Look up the AYM function/subroutine. That will allow you to subtract 3 months from an I6YYM format value.
TABLE FILE WHOLESALE PRINT TOTAL_STOCK_IN_CT/I11S AS 'STOCK_IN_CT' CURRENT_DT INITIAL_DT TEST_DT BY COUNTRY1 BY NEW_USED1 BY FIN_PLAN_MARKETING_DESC1 BY MONTH1 -*ACROSS MONTH1 ON COUNTRY1 SUBTOTAL AS '*TOTAL' WHERE ( MONTH1 GE '092009' ); ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON
The task is to get report for the last 3 months. The source data is from cube and the month value is in mmyyyy format (e.g for sept, value is 092009). With the WHERE CLAUSE above( MONTH1 GE '092009'), the code returns the correct value. But the plan is not to hardcode the initial month as it will be changing.
The DEFINE fields CURRENT_DT, INITIAL_DT and TEST_DT above have 2009/12/10, 092009 and Sep, 2009 values respectively for sept 2009 (an example). But when I used INITIAL_DT in the WHERE clause (MONTH1 GE 'INITIAL_DT' ), I get the following error:
********** 0 ERROR AT OR NEAR LINE 23 IN PROCEDURE homepage_wh (FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT: INITIAL_DT BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT **********
Please, can someone help me find out whats wrong and/or solution?
Thank you once again.
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
December 10, 2009, 10:53 AM
cmallain
I don't have access to run WF anymore but I think you need to remove the | in your date format... INITIAL_DT/M|YY the slashes should appear automatically due to smart date
------------------------------------------ last version used: v7.1; truly miss the wonderful things I did with WebFOCUS, HTML, & JavaScript.
December 10, 2009, 11:03 AM
GinnyJakes
You should always do your date tests against literal dates as opposed to defined fields. Here is an example of how to calculate 3 months paste, though depending on what you really want you might want to subtract 4 instead of 3:
Originally posted by GinnyJakes: You should always do your date tests against literal dates as opposed to defined fields. Here is an example of how to calculate 3 months paste, though depending on what you really want you might want to subtract 4 instead of 3:
Thank you Ginny. It worked perfectly. I appreciate your assistance.
The only issue is that when I use ACCROSS keyword on MONTH1, the data values does not display in a staight line. Please, do know the reason? the sample code is:
TABLE FILE WHOLESALE PRINT TOTAL_STOCK_IN_CT/I11S AS '' BY COUNTRY1 BY NEW_USED1 BY FIN_PLAN_MARKETING_DESC1 ACROSS MONTH1 ON COUNTRY1 SUBTOTAL AS '*TOTAL'
It displays like the following (in a slanting position):
It's not that odd ... PRINT means to show one line per individual record - and that's exactly what's happening. With SUM you specify to roll up all detail records to the first aggregate level (BY of ACROSS) and by doing that the across values will also be printed on the same line.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
December 11, 2009, 03:58 AM
umun
I have tried both SUM and PRINT but the values/measures still do not come in a straight line. One of my colleagues just told me that it's a known webFocus issue and that informationbuilders are working on it.
Is this true? We are using version 769.
Thank you.
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
December 11, 2009, 11:42 AM
Darin Lee
No. You've just got some additional sort level that is preventing the row from being aggregated at the level you want. It isn't a WebFOCUS "issue". It's just standard functionality based on how you've coded it. You'll have to use a SUM with fewer BY fields.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat