[SOLVED] Dates in Page Header

March 03, 2016, 11:32 AM

I have two dates as DEFINE in my master file

DEFINE Day1/HMDYYS=HGETC(8,'HYYMDS');,
TITLE='Current Day', $
DEFINE Yesterday/HMDYYS=DTADD(Day1,DAY,-1);,
TITLE='Yesterday', $

I use these in my fex to run audit reports for the last 24 hours. The fex runs fine and pulls the right information. I have in the page header of the reports these defined masterfile fields, as below (just posting a snippet of the code)

HEADING
"Eastern CT State University"
"Daily Direct DB Report for All Users"
"From WHERE ( DBA_AUDIT_TRAIL.DBA_AUDIT_TRAIL......

When the fex is scheduled the page header dates come up as blanks when there are no results from the pull. When there are results the date fields are visible.

I tried creating the date fields in the .fex and using that but these do not appear either. How do you get the date fields to appear in the header even if there are no results. These reports are for auditing purposes and need to have the date fields in them.

Thanks

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

March 03, 2016, 11:40 AM

Trudy

I just looked at the post and noticed that the line with the date fields was missing so am reposting that code now. Don't know how that happened because I didn't edit it out, hmmmm... So am trying to repost that code again.

HEADING
"Eastern CT State University"
"Daily Direct DB Report for All Users"
"From WHERE ( DBA_AUDIT_TRAIL.DBA_AUDIT_TRAIL.DBTIMESTAMP GE Yesterday ) AND .....

March 03, 2016, 11:46 AM

Trudy

And again the code didn't copy, what is up with that? The code looks like this when I open it in text editor. When I copy and paste it into focal point I loose a portion of the 4th line.

HEADING
"Eastern CT State University"
"Daily Direct DB Report for All Users"
"From WHERE (DBA_AUDIT_TRAIL.DBA_AUDIT_TRAIL.DBTIMESTAMP ......

Anything after the WHERE is irrelevant.

March 03, 2016, 11:46 AM

Francis Mariani

Trudy, when you post code, please use the </> button (at the top-right of the post window) to generate code tags, then paste the code between these tags. Sometimes code will not display properly because it can be interpreted as HTML.

[code]

you code here

[/code]

March 03, 2016, 11:48 AM

Trudy

OK, sorry everyone for being such a newbie but this last time I typed in the code and it removed the characters.

This is found after the From on line 4
The WHERE starts on line 5

March 03, 2016, 11:51 AM

Trudy

All right, feel like a fool now. Here is the code.

 
HEADING
"Eastern CT State University"
"Daily Direct DB Report for All Users"
"From <Yesterday to  <Day1 "
WHERE ( DBA_AUDIT_TRAIL.DBA_

Thanks Francis, won't make that mistake again.

March 03, 2016, 11:56 AM

BabakNYC

Do you need the timestamp? If you don't need it, the standard & Date fields in the heading even when the report is empty seems to work. If you need the actual time, then you'll have to also place &TOD in the heading instead of using the Defined fields.

TABLE FILE CAR
BY COUNTRY
BY CAR
ON TABLE SUBHEAD
" &DATETMDYY &TOD"
WHERE COUNTRY EQ 'RUSSIA';
ON TABLE SET EMPTYREPORT ON
END

March 03, 2016, 11:59 AM

Francis Mariani

quote:
when there are no results from the pull

Is this an empty report? The problem is that you're using DEFINE fields, which are report columns - if there are no report rows, the dates will not display.

Play with the EMPTYREPORT setting. Try SET EMPTYREPORT=ANSI.

quote:
The syntax is:
SET EMPTYREPORT={ANSI|ON|OFF}
where:

ANSI - Produces a single-line report and displays the missing data character or a zero if a COUNT is requested. In each case, &RECORDS will be 0, and &LINES will be 1.

If the SQL Translator is invoked, ANSI automatically replaces OFF as the default setting for EMPTYREPORT.

ON - Produces an empty report (column headings with no content). This was the default behavior in prior releases.

OFF - Produces no report output. OFF is the default value except for SQL Translator requests. When the SQL Translator is invoked, ANSI replaces OFF as the default setting for the EMPTYREPORT parameter, so the results are the same as for the ANSI setting.

The command can also be issued from within a request using:

ON TABLE SET EMPTYREPORT ON

March 03, 2016, 03:33 PM

Trudy

Thanks all, I did try the SET EMPTYREPORT settings with no luck. The &DATETMDYY &TOD does work, but I want to do it for a date range, from yesterday to today. So I believe the easiest way for me to do this is to use DM and create an &variable for yesterday and use this along with the &DATEMDYY &TOD in my header. My last question for you all is where do I find documentation on the various 'functions' that are available for use in DM?

Thanks again