I can display a date field on a report

   
TABLE FILE ATRGRAD
PRINT REFRESH_DATE

And that will show

REFRESH_DATE
2013/09/18 00:00:00

I then changed the format to how I would like the date to display

REFRESH_DATE/HYYMDN


REFRESH_DATE
20130918

When I tried to Concatenate it in order to use in a header:

-SET &REFRESH_DATE = REFRESH_DATE/HYYMDN;

That produced an error.

I then tried

-SET &REFRESH_DATE=EDIT(REFRESH_DATE,'99999999');
-SET &FileName='REPORT_OF_'||&REFRESH_DATE||'.pdf'


TABLE FILE ATRGRAD
PRINT REFRESH_DATE
HEADING
"FileName: &FileName "
END

And the result of that was:

FileName: REPORT_OF_REFRESH_.pdf


What would be the syntax in order to display it as:

FileName: REPORT_OF_20130905.pdf


The date field in the table contains the same date for all the rows as the date is to indicate the last time the table had a major change to its data. This date won't be displayed in the detail of the report, only the header which hopefully in the future I can get to actually save to the filename rather than show in the header.

This message has been edited. Last edited by: sxschech,

You can't reference a field in a set statement. It doesn't know what is in the field. You have to create a define and just move the set statements there.

Use this instead

 

-SET &FileName='REPORT_OF_'||&YYMD||'.pdf'
TABLE FILE CAR
PRINT SALES
HEADING
"FileName: &FileName "
END
 

Hi Mike,

I figured out that I could change the format of the date in the hold file, but when I tried to follow your suggestion to concatenate this into the filename variable using Define I must have missed something.

-INCLUDE ATRGRADSQL

DEFINE ATRGRAD
	FN/A60 = 'REPORT_OF_'||REFRESH_DATE||'.pdf';
END
TABLE FILE ATRGRAD
	PRINT REFRESH_DATE
HEADING
"FileName: <FN "
END

I get the following error message.

quote:

ERROR AT OR NEAR LINE 3 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC001) THE NAME OF THE FILE OR THE WORD 'FILE' IS MISSING
(FOC1517) UNRECOGNIZED COMMAND FN/A60 = 'REPORT_OF_'||REFRESH_DATE||'.PDF';
0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

If I use

"FileName: FN " rather than "FileName:
I don't get the error message, but it of course only displays the literal.

WebFocus 7.7.03
Win7, all output

You wrote 'DEFINE ATRGRAD'. You need 'DEFINE FILE ATRGRAD'.

Ok, Thanks for pointing that out. I was trying to recreate a simplified version of the original code, so missed that key word.

I fixed that line, but now the error says:

quote:

ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

I also tried redefining the field with edit

-INCLUDE ATRGRADSQL

DEFINE FILE ATRGRAD
	REFDATE/A8 = EDIT(REFRESH_DATE, '99999999');
	FN/A60 = 'REPORT_OF_'||REFDATE||'.pdf';
END
TABLE FILE ATRGRAD
	PRINT REFRESH_DATE
HEADING
"FileName: <FN "
END

And received essentially the same error:

quote:

ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
0 ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: REFDATE
0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

You will need to use one of the date/time functions to first convert the date/time format to an alpha format.

I got some assistance from tech support and based on the example they provided, was able to get it to work. I had a little trouble at first, getting error messages when I tried against my data, and then noticed that the code IBI provided did not put a semicolon at the end of the -SET. Once I put in the semicolons, the code ran for my data without error.

Here is IBI's code

TABLE FILE CAR
PRINT DEALER_COST
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS TEST1
END
 
-RUN
-READ TEST1 &TEST1.A7.

-SET &FileName =  'All' || &TEST1
TABLE FILE CAR
PRINT DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
HEADING
"FileName: &FileName"
ON TABLE PCHOLD FORMAT HTML
END

One of the earlier error messages showed that REFRESH_DATE was 17 characters, so that is where I got the .A17. from.

And here is what I did (added some if statements to better match the actual code) for getting the date to show within the file name format.

-INCLUDE ATRGRADSQL

TABLE FILE ATRGRAD
PRINT REFRESH_DATE/HYYMDN AS 'REFDATE'
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS REFDATES
END

-RUN
-READ REFDATES &REFDATES.A17.

-SET &RefDate   =  EDIT(&REFDATES, '99999999$$$$$$$$$'); 
-SET &GroupName = 'SF';
-SET &FileName  =  IF &GroupName = 'USF' THEN 'All' || &RefDate || &GroupName || '.pdf' ELSE 'All' || &RefDate || '.pdf';

TABLE FILE ATRGRAD
PRINT FIELD1
      FIELD2
HEADING
"FileName: &FileName"
ON TABLE PCHOLD FORMAT HTML
END

This seems way too complicated - obviously I'm missing something....

DEFINE FILE XYZ
REFDATE/YYMD=20130603;
END

TABLE FILE XYZ
PRINT SOMEFIELD NOPRINT
WHERE SOMEDATE EQ REFDATE
HEADING
"Filename:  <SOMEDATE .pdf "
ON TABLE PCHOLD FORMAT PDF
END

Hi George,

I tried your example, couldn't get it to work (based on my trying to understand your code). Also, the RefDate is coming from the table itself and your example is showing as hard coded, which you then are making equal to the date in the table. Not sure why I would need to make the date equal to the date in the table, seems unnecessary. Also the filename string is a lot more complicated than simply concatenating the date with .pdf, I have several IF statements and ampervariables from an html composer file that come together to derive the filename.

I only used a hard date and the where statement because I tested that against my own database which has thousands of dates.

I presume yours does too and you are using some other criterion to select your records such that they all have the same REFDATE.

Here I do it again using an order number (ONUM) as my criterion since each order can have only one order date (ODATE).

This gives: Filename: 2007/09/27 .pdf in the heading with a space between the date and .pdf.

TABLE FILE XYZ
PRINT ODATE NOPRINT
WHERE ONUM EQ '207361'
HEADING
"Filename:  <ODATE .pdf "
ON TABLE PCHOLD FORMAT PDF
END

You would need to do a define to convert the date to your preferred format and add a spot marker to take out the space between the date and .pdf

The following gives: Filename: September 27, 2007.pdf

Of course you then have to take steps to make the filename match what you have in the header.
Personally I wouldn't use the above date format for this because I'm against commas in filenames as a matter of principle.
I would go for 2007-09-27.pdf

DEFINE FILE XYZ
NEWDATE/MtrDYY=ODATE;
END

TABLE FILE XYZ
PRINT NEWDATE NOPRINT
WHERE ONUM EQ '207361'
HEADING
"Filename:  <NEWDATE<+0>.pdf "
ON TABLE PCHOLD FORMAT PDF
END

Hi George,

Thanks for continuing to look at this.
The date that is being used in the table is the same for all records. It indicates the date that the table was refreshed/updated. I use this date so when the report is run, we know when the data were last refreshed/updated. There shouldn't be a need to have a where clause to determine the date in this particular set up. The person running the report shouldn't need to know the date in order to run the report, but would like it displayed on the report for the above reason. If I could think of another way to store the date for use on the report without taking up space in the table I could consider that. I'm pulling the date out in the format 20130918 Year Month Day. No spaces, dashes slashes, etc. It then needs to be concatenated with the rest of the name making up the filename which is derived through if statements evaluating the ampervariables passed to it from the html composer page. When the help desk IBI consultant tried to do something similar to what you showed with spot markers, he couldn't quite get it to work so that is why he suggested using the method with the -READ.

Not sure how I could sandwich together the filename with the date using your method since date is not the last item before .pdf

Here is how the filename is built.

-SET &REFRESH_DATE = EDIT(REFRESH_DATE,'99999999');
-SET &FileName 	 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
-SET &FileName 	 = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_'  || &STUPOP2 || &REFDATE  || '-'  || &FileName || &FOREWORDNAME  ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || &REFDATE || '-'  || &FileName || &FOREWORDNAME;
-SET &FileName   = IF &FILTERS EQ 'Yes' THEN &FileName || '_StartEnd'   || &STARTITEM || '.pdf' ELSE
-				   IF &FILTERS EQ 'Any' THEN &FileName || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE
-				   &FileName || '.pdf';

I think the way to make it work is to do most of what you have inside of a DEFINE instead of with SET statements. That way the REFRESH DATE is available within the report.

So something like:

DEFINE FILE XYZ
REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;

FILENAME/A60 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');

FILENAME = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_'  || &STUPOP2 || REFRESH_DATE_ALPHA || '-'  || FILENAME|| &FOREWORDNAME  ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || REFRESH_DATE_ALPHA || '-'  || FILENAME || &FOREWORDNAME;

FILENAME   = IF &FILTERS EQ 'Yes' THEN FILENAME || '_StartEnd'   || &STARTITEM || '.pdf' ELSE IF &FILTERS EQ 'Any' THEN FILENAME || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE FILENAME || '.pdf';

[other statements]

END

Hi George,

Don't know what I'm doing wrong, haven't been able to get your code to work. I get error message:

quote:

0 ERROR AT OR NEAR LINE 17 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
REFRESH_DATE_ALPHA
0 ERROR AT OR NEAR LINE 18 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: REFRESH_DATE_ALPHA
0 ERROR AT OR NEAR LINE 26 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: FileName

Here is a very simplified version of my code based on what I thought was shown in your example:

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select '12345' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'yyyymmdd') as refresh_date from DUAL UNION
select '23456' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'yyyymmdd') as refresh_date from DUAL
END
TABLE FILE SQLOUT
PRINT 
	ID/A5 AS 'CWID'
	COHORTTERM/A6 AS 'COHORTTERM'
	INCOMINGCOLLEGE/A2 AS 'INCOMINGCOLLEGE'
	REFRESH_DATE/HYYMDN
ON TABLE HOLD AS ATR
END
DEFINE FILE ATR
REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;
FileName/A60=IF INCOMINGCOLLEGE = 'NS' THEN COHORTTERM || '_' || REFRESH_DATE_ALPHA || '.pdf' ELSE INCOMINGCOLLEGE || '_' || REFRESH_DATE_ALPHA || '_' || 'XYZ.pdf';
END
TABLE FILE ATR
PRINT 
     CWID
     COHORTTERM
	 INCOMINGCOLLEGE
HEADING
"Filename: <FileName"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
  

Read up on dates...

  
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
ENGINE SQLORA SET DEFAULT_CONNECTION PROD
SQL SQLORA PREPARE SQLOUT FOR
select '12345' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'YYYY/MM/DD') as refresh_date from DUAL UNION
select '23456' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'YYYY/MM/DD') as refresh_date from DUAL
;
END
DEFINE FILE SQLOUT
  X_DATE/YYMD = HDATE(REFRESH_DATE, 'YYMD');
  Y_DATE/I8YYMD = X_DATE;
  Z_DATE/A8YYMD = EDIT(Y_DATE);
END
TABLE FILE SQLOUT
PRINT 
	ID/A5 AS 'CWID'
	COHORTTERM/A6 AS 'COHORTTERM'
	INCOMINGCOLLEGE/A2 AS 'INCOMINGCOLLEGE'
	REFRESH_DATE
	X_DATE
	Y_DATE
	Z_DATE
ON TABLE HOLD AS ATR
END
DEFINE FILE ATR
-*REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;
FileName/A60 = IF INCOMINGCOLLEGE EQ 'NS' THEN COHORTTERM || '_' || Z_DATE || '.pdf' ELSE INCOMINGCOLLEGE || '_' || Z_DATE || '_' || 'XYZ.pdf';
END
TABLE FILE ATR
PRINT 
     CWID
     COHORTTERM
	 INCOMINGCOLLEGE
HEADING
"Filename: <FileName"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
-EXIT

Note that I said “something like” the solution I suggested. I didn't want to imply that it was the exact code.

According to 1001 ways to work with dates in WebFocus:

“The process of converting a smartdate field to a legacydate field simply requires an equality statement..... For legacydate conversions the output length specification must match the date output. Four-digit years would require A8MDYY … The order of the month, day and year components of the output field should usually match the input field” - Page 53 in the Second edition of the book.

This was he basis of my suggestion.

Tom is suggesting that REFRESH_DATE is a DateTime value. I assumed it was purely a date – and didn't actually try out the A8YYMD.

I understand HDATE will produce a numeric value – which would then need to be converted to alpha as he demonstrates.

Hi Tom,

When I try your code as is, it works, when I try it with my actual fex it doesn't yet work. I must be missing something in the syntax when I converted the FileName from the -SET to the DEFINE section.

quote:

ERROR AT OR NEAR LINE 18 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC260) AN OPERATION IS MISSING AN ARGUMENT
0 ERROR AT OR NEAR LINE 16 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
0 ERROR AT OR NEAR LINE 29 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: FileName
BYPASSING TO END OF COMMAND

This is the code from the -SET

-SET &FileName 	 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
-SET &FileName 	 = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_'  || &STUPOP2 || &REFDATE  || '-'  || &FileName || &FOREWORDNAME  ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || &REFDATE || '-'  || &FileName || &FOREWORDNAME;
-SET &FileName   = IF &FILTERS EQ 'Yes' THEN &FileName || '_StartEnd'   || &STARTITEM || '.pdf' ELSE
-				   IF &FILTERS EQ 'Any' THEN &FileName || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE
-				   &FileName || '.pdf';
  

This is how it looks in the DEFINE. What I did was remove the amper from FileName, but I imagine there must be something more to it.

 DEFINE FILE ATRGRAD

FileName 	 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
FileName 	 = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_'  || &STUPOP2 || Z_DATE  || '-'  || FileName || &FOREWORDNAME  ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || Z_DATE || '-'  || FileName || &FOREWORDNAME;
FileName   = IF &FILTERS EQ 'Yes' THEN FileName || '_StartEnd'   || &STARTITEM || '.pdf' ELSE
				   IF &FILTERS EQ 'Any' THEN FileName || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE
				   FileName || '.pdf';

END
 

Why is this marked as Solved then?
In your DEFINE, you have no formats, that means it's a bullion result, 0 or 1.
By doing ? FOCERROR number, you'll get an explanation of the error:

  
? 260
gives:
(FOC260) AN OPERATION IS MISSING AN ARGUMENT
 An arithmetic or logical operation is missing an argument, possibly
 two operations have been written in succession without a semi-colon
 (;) separating them.
? 266
gives
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
 Missing or extra IF, THEN, or ELSE clauses, or possibly a missing
 semi-colon (;).
? 406
gives
(FOC406) THE FIELDNAME IS NOT RECOGNIZED:
 The name of the data field is not on the list of fields for this
 file.  Check the spelling or the list of fieldnames and aliases
 for the file.

This is only partial code, I would ensure there is a TOKEN 8, and, it's 60 bytes, which I kinda doubt...
Debug yourself, 1 piece at a time...

  
-SET &FileName 	 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
-TYPE &FileName
-EXIT

Then, do the next, etc...

You need to specify a field format in a DEFINE:

FILENAME/A200 = ...

Tom,

The thread was marked as solved because IBI developed a solution that worked. However, after I marked it as solved, George Patton was suggesting an alternate approach and I thought since he offered that option, I should try it out. Unfortunately, I have spent way too much time on it, and still that approach doesn't seem to work for me and since I have a workable solution, I'm going to go with that rather than try to debug this.

Francis,
I tried putting your suggestion of FileName/A200, now there is no error message, but the output is blank.

I am glad that IBI development gave you a solution - I agree that you should rather stick with it.

The forum is here to "help" - but to be helped the requestor needs to heed the advice.

I was admittedly getting a little frustrated reading this post, because everyone has been trying their utmost to help, and also because a week ago I had suggested:

quote:

You will need to use one of the date/time functions to first convert the date/time format to an alpha format.

And Tom pretty much said the same thing two days ago:

quote:

Read up on dates...

Like George said - sometimes we will share "something" as an idea, not necessarily a complete solution.

These "something"s can be used for further learning/reading - which very often helps other people that are just starting out with WebFOCUS.

So, use the example that works, but to develop your knowledge in WebFOCUS, perhaps the only task you should set yourself is simply to figure out how to convert your date/time field to an alpha format. Admittedly this will probably mean the you'll have to read a manual ;-)

In other words, get the code below to create a field called ALPHA_REFDATE to display your date in alpha format.
If you can get that to work - post the code - and then I am sure we can help you!

DEFINE FILE ATRGRAD
ALPHA_REFDATE/A???? = ?????REFRESH_DATE????? ;
END

TABLE FILE ATRGRAD
PRINT REFRESH_DATE ALPHA_REFDATE
WHERE RECORDLIMIT EQ 1
END

quote:

 DEFINE FILE ATRGRAD
...
FileName 	 = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_'  || &STUPOP2 || Z_DATE  || '-'  || FileName || &FOREWORDNAME  ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || Z_DATE || '-'  || FileName || &FOREWORDNAME;
...
END
 

In addition to what other people here mentioned:

You have FOC_NONE on the second line of those defines, which means that entire line will be ignored by FOCUS. The same happens to lines where any of those &var's contain FOC_NONE.

That's probably not what you intended.

Twanette and others are precisely correct. You need to simplify and demonstrate that you are retrieving the correct value first before trying to concatenate it into a complex string.

We are all just trying to point out in the end that your REFRESH_DATE exists in the datbase you are pulling from and it therefore is eminently possible - and to the point BETTER coding style - to use a couple of DEFINES to get it into the format required.

Ultimately this is much simpler than doing a separate TABLE FILE to put the value into a separate file and then READing that file value into an Amper Variable.

PS - I did, if you look back to my DEFINE, show FILENAME/A60 = .....

You should try out Twanette's most recent suggestion - but it's a suggestion! - you will have to substitute some things for all those question marks !

Again, I would like to thank and let everyone know I appreciate their help and patience in working through this. Based on the fact that Tom's suggestion worked, the issue was at that point not with the date field, but something else. It seems the simpler one tries to make something for the user, the more complicated it gets behind the scenes. When I have time, I'm going to look into something Wep5622 pointed out in that post regarding the FOC_NONE as that might be why the output is blank.

I didn't see it mentioned but FPRINT can also be used to easily convert numerics to alphas including Date-Time and Legacy dates. Do a search on FPRINT to find out more.