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!

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...

Tom

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 ''

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...

Tom

This 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

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.

X_P1_MONTH/I2L=P1_MONTH;
A_P1_MONTH/A2=EDIT(X_P1_MONTH);

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

DEFINE FILE SAPOVERVIEW
P1_DATE/MDYY=HDATE(PriorMonth1Name, 'MDYY');
P2_DATE/MDYY=HDATE(PriorMonth2Name, 'MDYY');
P3_DATE/MDYY=HDATE(PriorMonth3Name, 'MDYY');
CUR_DATE/MDYY=HDATE(CurrentMonthName, 'MDYY');

P1_MONTH/M=P1_DATE;
P2_MONTH/M=P2_DATE;
P3_MONTH/M=P3_DATE;
CUR_MONTH/M=CUR_DATE;

A_P1_MONTH/A2=EDIT(P1_MONTH);
A_P2_MONTH/A2=EDIT(P2_MONTH);
A_P3_MONTH/A2=EDIT(P3_MONTH);
A_CUR_MONTH/A2=EDIT(CUR_MONTH);

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

Clay Williams
WebFOCUS 7.1.4

Clay,

Thanks, helps a lot. Now for tricks:

A_P1_MONTH/A2=EDIT(P1_MONTH);
A_P2_MONTH/A2=EDIT(P2_MONTH);
A_P3_MONTH/A2=EDIT(P3_MONTH);
A_CUR_MONTH/A2=EDIT(CUR_MONTH);

After your defines:

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

DEFINE FILE SAPOVERVIEW
P1_DATE/MDYY=HDATE(PriorMonth1Name, 'MDYY');
P2_DATE/MDYY=HDATE(PriorMonth2Name, 'MDYY');
P3_DATE/MDYY=HDATE(PriorMonth3Name, 'MDYY');
CUR_DATE/MDYY=HDATE(CurrentMonthName, 'MDYY');

P1_MONTH/M=P1_DATE;
P2_MONTH/M=P2_DATE;
P3_MONTH/M=P3_DATE;
CUR_MONTH/M=CUR_DATE;

A_P1_MONTH/A2=EDIT(P1_MONTH);
A_P2_MONTH/A2=EDIT(P2_MONTH);
A_P3_MONTH/A2=EDIT(P3_MONTH);
A_CUR_MONTH/A2=EDIT(CUR_MONTH);

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

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...

Welcome to the WebFOCUS World...

Clay,

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