Is there a way that I can display a date contained in a field for a field Name. The names of the fields change based on whither it is the Current Month or months previous. The names of those months are located in different fields but I need to display the values as a name of a different field. I'm desperate!
Clay Williams WebFOCUS 7.1.4
November 06, 2006, 12:00 PM
Tom Flynn
claymeone,
Update your signature.
-SET &COL_TYP_1 = IF &MONTH EQ &CUR_MO THEN 'FIELD_1' ELSE 'FIELD_2' ; -SET &COL_TYP_2 = IF &MONTH EQ &CUR_MO THEN 'FIELD_3' ELSE 'FIELD_4' ;
etc.
TABLE FILE FILENAME SUM/PRINT
&COL_TYP_1 &COL_TYP_2
ETC.
I think that is what you are going for, not real sure...
Thank you so much for you quick response. However, I should have been more clear. You example is good but I am trying to get the date to display after the AS see below. Is that possible?
TABLE FILE SAPOVERVIEW SUM PriorMonth3 AS '&COL_TYP_1' MONTH3RENT AS '' PriorMonth2 AS MONTH2RENT AS '' PriorMonth1 AS '' MONTH1RENT AS '' CurrentMonth AS '' CURMONTHRENT AS '' BY SAPCategory NOPRINT AS '' BY Originator AS ''
Clay Williams WebFOCUS 7.1.4
November 06, 2006, 12:44 PM
Tom Flynn
claymeone,
Then you are looking for column names, not field names.
OK, let's try this approach:
-SET &COL_TYP_1 = IF &MONTH EQ &CUR_MO THEN EDIT(PriorMonth3,'$$$9999/99/99') ELSE EDIT(PriorMonth2,'$$$9999/99/99'); -SET &COL_TYP_2 = IF &MONTH EQ &CUR_MO THEN EDIT(PriorMonth1,'$$$9999/99/99') ELSE EDIT(PriorMonth12,'$$$9999/99/99');
SET ASNAMES = ON etc...
TomThis message has been edited. Last edited by: Tom Flynn,
Tom I'M new at this so forgive me. Here is more of what I am trying to do. PriorMonth1 is a 1 or 0 then I sum that number to get the total. PriorMonth1Name is the date associated with the value 1 or 0. I am trying to get the PriorMonth1Name date to display as the field name for the PriorMonth. This method is not working.
ENGINE SQLMSS SET DEFAULT_CONNECTION MDFR-FSSQL1 SQL SQLMSS EX sp_USARptSAPOverview ; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS SAPOVERVIEW END DEFINE FILE SAPOVERVIEW P1_DATE/MDYY=HDATE(PriorMonth1Name, 'MDYY'); P1_MONTH/M=P1_DATE; A_P1_MONTH/A2=EDIT(P1_MONTH); END
TABLE FILE SAPOVERVIEW SUM PriorMonth1 AS '&A_P1_MONTH' - Which is really the date in the field PriorMonth3Name
Clay Williams WebFOCUS 7.1.4
November 06, 2006, 01:59 PM
Tom Flynn
quote:
DEFINE FILE SAPOVERVIEW P1_DATE/MDYY=HDATE(PriorMonth1Name, 'MDYY'); P1_MONTH/M=P1_DATE; A_P1_MONTH/A2=EDIT(P1_MONTH); END
TABLE FILE SAPOVERVIEW SUM PriorMonth1 AS '&A_P1_MONTH' -
Which is really the date in the field PriorMonth3Name-
That's OK, we were all new once. Update your signature with platform and version or susannah will get 'ya (lol)
First, we can't turn a smart date, YYMD, MDYY, Q, M into a an alpha field immediately.
Second, PriorMonth1 AS '&A_P1_MONTH' is invalid as A_P1_MONTH is-not an AMPER variable, per your code. Third, I don't see where PriorMonth3Name is referenced, at all. Fourth, I don't think we are getting the entire picture.
So, please post your actual focexec so we can view the code to assist you...
I updated my profile signature I hope it works. Here is the fex. You right about the amper variable not working. I am just trying to get the date from the PriorMonth(x)Name to display as the field names for PriorMonth(x) and -set doesn't seem to work with the fields.
ENGINE SQLMSS SET DEFAULT_CONNECTION MDFR-FSSQL1
SQL SQLMSS EX sp_USARptSAPOverview ; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS SAPOVERVIEW END
MONTH3RENT/D12.2M= IF PriorMonth3 EQ 1 THEN MonthlyRent ELSE 0; MONTH2RENT/D12.2M= IF PriorMonth2 EQ 1 THEN MonthlyRent ELSE 0; MONTH1RENT/D12.2M= IF PriorMonth1 EQ 1 THEN MonthlyRent ELSE 0; CURMONTHRENT/D12.2M= IF CurrentMonth EQ 1 THEN MonthlyRent ELSE 0; END TABLE FILE SAPOVERVIEW SUM PriorMonth3 AS '&A_P3_MONTH' MONTH3RENT AS '' PriorMonth2 AS '&A_P2_MONTH' MONTH2RENT AS '' PriorMonth1 AS '&A_P1_MONTH' MONTH1RENT AS '' CurrentMonth AS '' CURMONTHRENT AS '&A_CUR_MONTH' BY SAPCategory NOPRINT AS '' BY Originator AS '' ON SAPCategory SUBHEAD "ON SAPCategory SUBTOTAL AS '*TOTAL' HEADING "UNISON SITE MANAGEMENT" "SAP Overview Report" "Report run by: &IBIMR_user<+0> Report Run on: &DATEtrMDYY" "Report Criteria Start Date: Current Month Plus three Previous Months" " " FOOTING ""Currently SMS does not capture historical rent changes." WHERE SAPCategory EQ 'Active Legal Sites' OR 'SAPs Not Submitted' OR 'SAPs Accepted' OR 'SAPs Under Review'; ON TABLE SET NODATA '0' ON TABLE SET EMPTYCELLS OFF ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.250000, RIGHTMARGIN=0.250000, SQUEEZE=OFF, ORIENTATION=LANDSCAPE, $ TYPE=REPORT, GRID=OFF, FONT='TIMES NEW ROMAN', SIZE=8, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $ TYPE=TITLE, SIZE=9, BACKCOLOR=RGB(167 173 199), STYLE=BOLD, $ TYPE=HEADING, FONT='ARIAL', SIZE=10, COLOR='NAVY', STYLE=BOLD+ITALIC, JUSTIFY=CENTER, $ TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=1, JUSTIFY=LEFT, WIDTH=2.181, $ TYPE=HEADING, LINE=2, OBJECT=TEXT, ITEM=1, JUSTIFY=LEFT, WIDTH=1.431, $ TYPE=HEADING, LINE=3, OBJECT=TEXT, ITEM=1, STYLE=BOLD+ITALIC+UNDERLINE, WIDTH=1.556, $ TYPE=HEADING, LINE=3, OBJECT=TEXT, ITEM=2, JUSTIFY=LEFT, WIDTH=2.000, $ TYPE=HEADING, LINE=4, OBJECT=TEXT, ITEM=1, SIZE=8, JUSTIFY=LEFT, WIDTH=3.000, $ TYPE=HEADING, LINE=4, OBJECT=TEXT, ITEM=2, SIZE=8, JUSTIFY=RIGHT, WIDTH=7.500, $ TYPE=HEADING, LINE=5, SIZE=8, JUSTIFY=LEFT, $ TYPE=HEADING, LINE=5, OBJECT=TEXT, ITEM=1, WIDTH=3.431, $ TYPE=SUBHEAD, SIZE=9, STYLE=BOLD+UNDERLINE, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(167 173 199), STYLE=BOLD, $ TYPE=REPORT, IMAGE=app/unison.gif, POSITION=(0.263889 0.305556), SIZE=(1.875000 0.305556), $ TYPE=REPORT, COLUMN=N1, SQUEEZE=1.805556, $ TYPE=REPORT, COLUMN=N2, SQUEEZE=1.555556, $ ENDSTYLE END -RUN
TABLE FILE SAPOVERVIEW SUM A_P1_MONTH A_P2_MONTH A_P1_3_MONTH A_CUR_MONTH ON TABLE HOLD AS MO_DATA FORMAT ALPHA END -RUN -READ MO_DATA &A_P1_MONTH.A2., &A_P2_MONTH.A2., &A_P3_MONTH.A2., &A_CUR_MONTH.A2.
Now, the rest of the code should work...
Tom
P.S. Your WF version is there, not your platform, although we know your RDBMS is SAP...
Wow! Brillant!!! It worked!!!! THANK YOU SO MUCH!!!!!! I didn't know you could do such things. This will help me on so many other things. Not only controlling the naming of fields but just using multiple TABLE FILE statements to work the data. I've used hold files but not like this. Thanks again!!!
SET ASNAMES = ON ENGINE SQLMSS SET DEFAULT_CONNECTION MDFR-FSSQL1
SQL SQLMSS EX sp_USARptSAPOverview ; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS SAPOVERVIEW END
MONTH3RENT/D12.2M= IF PriorMonth3 EQ 1 THEN MonthlyRent ELSE 0; MONTH2RENT/D12.2M= IF PriorMonth2 EQ 1 THEN MonthlyRent ELSE 0; MONTH1RENT/D12.2M= IF PriorMonth1 EQ 1 THEN MonthlyRent ELSE 0; CURMONTHRENT/D12.2M= IF CurrentMonth EQ 1 THEN MonthlyRent ELSE 0; END TABLE FILE SAPOVERVIEW SUM A_P1_MONTH A_P2_MONTH A_P3_MONTH A_CUR_MONTH ON TABLE HOLD AS MO_DATA FORMAT ALPHA END -RUN -READ MO_DATA &A_P1_MONTH.A2., &A_P2_MONTH.A2., &A_P3_MONTH.A2., &A_CUR_MONTH.A2.
TABLE FILE SAPOVERVIEW SUM PriorMonth3 AS '&A_P3_MONTH' MONTH3RENT AS '' PriorMonth2 AS '&A_P2_MONTH' MONTH2RENT AS '' PriorMonth1 AS '&A_P1_MONTH' MONTH1RENT AS '' CurrentMonth AS '' CURMONTHRENT AS '&A_CUR_MONTH' BY SAPCategory NOPRINT AS '' BY Originator AS '' ON SAPCategory SUBHEAD "ON SAPCategory SUBTOTAL AS '*TOTAL' HEADING "UNISON SITE MANAGEMENT" "SAP Overview Report" "Report run by: &IBIMR_user<+0> Report Run on: &DATEtrMDYY" "Report Criteria Start Date: Current Month Plus three Previous Months" " " FOOTING ""Currently SMS does not capture historical rent changes." WHERE SAPCategory EQ 'Active Legal Sites' OR 'SAPs Not Submitted' OR 'SAPs Accepted' OR 'SAPs Under Review'; ON TABLE SET NODATA '0' ON TABLE SET EMPTYCELLS OFF ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.250000, RIGHTMARGIN=0.250000, SQUEEZE=OFF, ORIENTATION=LANDSCAPE, $ TYPE=REPORT, GRID=OFF, FONT='TIMES NEW ROMAN', SIZE=8, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $ TYPE=TITLE, SIZE=9, BACKCOLOR=RGB(167 173 199), STYLE=BOLD, $ TYPE=HEADING, FONT='ARIAL', SIZE=10, COLOR='NAVY', STYLE=BOLD+ITALIC, JUSTIFY=CENTER, $ TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=1, JUSTIFY=LEFT, WIDTH=2.181, $ TYPE=HEADING, LINE=2, OBJECT=TEXT, ITEM=1, JUSTIFY=LEFT, WIDTH=1.431, $ TYPE=HEADING, LINE=3, OBJECT=TEXT, ITEM=1, STYLE=BOLD+ITALIC+UNDERLINE, WIDTH=1.556, $ TYPE=HEADING, LINE=3, OBJECT=TEXT, ITEM=2, JUSTIFY=LEFT, WIDTH=2.000, $ TYPE=HEADING, LINE=4, OBJECT=TEXT, ITEM=1, SIZE=8, JUSTIFY=LEFT, WIDTH=3.000, $ TYPE=HEADING, LINE=4, OBJECT=TEXT, ITEM=2, SIZE=8, JUSTIFY=RIGHT, WIDTH=7.500, $ TYPE=HEADING, LINE=5, SIZE=8, JUSTIFY=LEFT, $ TYPE=HEADING, LINE=5, OBJECT=TEXT, ITEM=1, WIDTH=3.431, $ TYPE=SUBHEAD, SIZE=9, STYLE=BOLD+UNDERLINE, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(167 173 199), STYLE=BOLD, $ TYPE=REPORT, IMAGE=app/unison.gif, POSITION=(0.263889 0.305556), SIZE=(1.875000 0.305556), $ TYPE=REPORT, COLUMN=N1, SQUEEZE=1.805556, $ TYPE=REPORT, COLUMN=N2, SQUEEZE=1.555556, $ ENDSTYLE END -RUN
Clay Williams WebFOCUS 7.1.4
November 06, 2006, 03:41 PM
Tom Flynn
Clay,
You are welcome, BUT, I am far from brilliant. You will find Dialogue Manager a BIG piece in assisting you in developing procedures. May want to read up, or, use the IBI website for documentation and CBT's...
If this is a column that you will use many times then it might be better / more efficient for you to include it as an extra column in your stored proc. Just use something like COALESCE(CurrentMonth,PriorMonth1) AS MonthToUse.
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