Naming Output Files, Date-Time Stamping, and Jeff-Net Variables

  Print
Questions:

1) How can I customize the output file name in an emailed or FTP'd job?

2) How can I put a long date or use a report variable in the email subject?

3) How can I automatically add a date/time stamp to an output file?

4) I have global date/time stamping turned on for all output files... how can I turn it off for one batch?

5) Can I use report values anywhere else in a batch?

6) How can I customize the header or footer text of a report with report values?

Answers:

You can do all of this and more utilizing special Jeff-Net formula fields on your report and then using special variables while setting up jobs and batches.

---

Naming An Output File:

By default, output files use the name of the report and append the file type (like PDF or TXT). If you are doing a File export, you can of course set the exact path and file name manually (or throw in some Jeff-Net variables explained later in this document). For emailed and FTP'd file names, you have these following options:

  • Create a formula in the report called jnOutputFile and place that formula in your Report Header section. It does not have to be displayed; it can be suppressed. Within the formula, you literally name the file whatever you want using fields from a database and/or manually typed in strings. It might look something like: {table.customername} & "-report.pdf". Note, with the usage of jnOutputFile, Report Runner Batch will not add or append anything to any file names, but it will replace any Jeff-Net variables passed in the file name.
  • Use the batch-specific INI setting of UseThisOutputFileNameForEverythingExceptFileDestinations. This setting can be found under INI button, [Options] group under other settings. A menu is available by right-clicking your mouse. The setting might look something like this: UseThisOutputFileNameForEverythingExceptFileDestinations=invoice.pdf or UseThisOutputFileNameForEverythingExceptFileDestinations=jnParm1-report.pdf or UseThisOutputFileNameForEverythingExceptFileDestinations=Sales-Report-jn_MMDDYY.pdf
Date-Time Stamping An Output File:

  • You can enable date-time stamping of files globally under the main Settings in the Home menu, under the File options. These settings can be easily enabled or disabled for any batch using the batch-specific INI setting of DTStampFileOnOrOff or DTStampFTPOnOrOff under [Options] group under other settings.
  • You can manually add an unlimited number of date-time stamp variations using the Jeff-Net data-based variables like jn_MM, jn_DD, jn_YYYY and many others (described below)
Jeff-Net Variables:

There are a number of Jeff-Net variables that can be used literally anywhere around the application (file names, email subject, email headers and footers). Some variables are automatically set with values and some must be manually set using formulas in reports.

An example of automatically setting a variable would be jnParm1, jnParm2, jnParm3, etc. The variables will ALWAYS be set to the values of parameter 1, parameter 2, parameter 3, etc... of the current job running. Let's say parameter 1 is set to "MONTHLY" to indicate a frequency and parameter 2 is set to "5" to indicate a sales territory. If the job was creating a PDF and emailing it, you could set the email subject (under batch-specific INI settings) to jnParm1 Sales Report for Territory jnParm2. This would of course be changed to MONTHLY Sales Report for Territory 5.

An example of manually setting a variable would be using the jnUserX formulas in a report and setting them to field values in the report, then using the jnUser1 variables in the header and footer of the email. Let's say for the sales territories you wanted to set the value jnUser1 to the salesperson's name and jnUser2 to their total sales volume. You would create two formulas jnUser1 and jnUser2. jnUser1 would be set to {SalesTable.FirstName} and jnUser2 might be set to {SalesTable.TotalSales}. Then in the Header settings, you would type:

Dear jnUser1:

Attached is your sales report for this month. Your total sales this month for Territory jnParm2 was $jnUser2.

Make sense?

Complete List of Variables:

Here is the complete list of available date-time variables. We will not put a definition or example for each one as the variables are replaced with exactly what you think. For any that are unclear, we will explain further. Note, we have left pseudo-code to show what format values are displayed.

'Date/Time Stamp Type Formats
'hh is 12 hour time format and HH is 24 hour time format
'mm is minutes and MM is month
jn_HH2 = Format$(dtWorkDate, "hh")
jn_MMDDYYYYHHMMSS = Format$(dtWorkDate, "MMddyyyyHHmmss")
jn_DDMMYYYYHHMMSS = Format$(dtWorkDate, "ddMMyyyyHHmmss")
jn_YYYYMMDDHHMMSS = Format$(dtWorkDate, "yyyyMMddHHmmss")
jn_YYYYDDMMHHMMSS = Format$(dtWorkDate, "yyyyddMMHHmmss")
jn_MMDDYYHHMMSS = Format$(dtWorkDate, "MMddyyHHmmss")
jn_DDMMYYHHMMSS = Format$(dtWorkDate, "MMddyyHHmmss")
jn_HHMMSS = Format$(dtWorkDate, "HHmmss")
jn_HHMM = Format$(dtWorkDate, "HHmm")
jn_HH = Format$(dtWorkDate, "HH")
jn_MM2 = Format$(dtWorkDate, "mm")
jn_SS = Format$(dtWorkDate, "ss")
jn_AMPM = Format$(dtWorkDate, "tt")

jn_YYYYMMDD = Format$(dtWorkDate, "yyyyMMdd")
jn_YYMMDD = Format$(dtWorkDate, "yyMMdd")

jn_MMDDYYYY = Format$(dtWorkDate, "MMddyyyy")
jn_DDMMYYYY = Format$(dtWorkDate, "ddMMyyyy")
jn_MMDDYY = Format$(dtWorkDate, "MMddyy")
jn_DDMMYY = Format$(dtWorkDate, "ddMMyy")
jn_YYYY = Format$(dtWorkDate, "yyyy")
jn_MM = Format$(dtWorkDate, "MM")
jn_DD = Format$(dtWorkDate, "dd")
jn_YY = Format$(dtWorkDate, "yy")

'Month, day, year, and month variables for yesterday and tomorrow
jn_yesterdayMM = Format$(Now.Date.AddDays(-1), "MM")
jn_tomorrowMM = Format$(Now.Date.AddDays(+1), "MM")
jn_yesterdayDD = Format$(Now.Date.AddDays(-1), "dd")
jn_tomorrowDD = Format$(Now.Date.AddDays(+1), "dd")
jn_yesterdayYYYY = Format$(Now.Date.AddDays(-1), "yyyy")
jn_tomorrowYYYY = Format$(Now.Date.AddDays(+1), "yyyy")
jn_yesterdayYY = Format$(Now.Date.AddDays(-1), "yy")
jn_tomorrowYY = Format$(Now.Date.AddDays(+1), "yy")
jn_yesterdaymonth = Format$(Now.Date.AddDays(-1), "MMMM")
jn_tomorrowmonth = Format$(Now.Date.AddDays(+1), "MMMM")

jn_todayMMDDYY = Format$(DateTime.Now, "MM/dd/yyyy")
jn_todayDDMMYY = Format$(DateTime.Now, "dd/MM/yyyy")
jn_dayofweek = DateTime.Now.DayOfWeek.ToString
jn_dayofweekyesterday = DateTime.Now.AddDays(-1).DayOfWeek.ToString
jn_dayofweektomorrow = DateTime.Now.AddDays(+1).DayOfWeek.ToString

jn_yesterdayMMDDYY = Format$(Now.Date.AddDays(-1), "MM/dd/yyyy")
jn_tomorrowMMDDYY = Format$(Now.Date.AddDays(+1), "MM/dd/yyyy")
jn_yesterdayDDMMYY = Format$(Now.Date.AddDays(-1), "dd/MM/yyyy")
jn_tomorrowDDMMYY = Format$(Now.Date.AddDays(+1), "dd/MM/yyyy")

'Years
jn_thisyear = DateTime.Now.Year.ToString)
jn_lastyear = (DateTime.Now.Year - 1).ToString)
jn_nextyear = (DateTime.Now.Year + 1).ToString)
jn_lastmonthsyear = DateTime.Now.AddMonths(-1).Year.ToString)
jn_nextmonthsyear = DateTime.Now.AddMonths(+1).Year.ToString)

'Months
jn_thismonth = DateTime.Now.ToString("MMMM")
jn_lastmonth = DateTime.Now.AddMonths(-1).ToString("MMMM")
jn_nextmonth = DateTime.Now.AddMonths(+1).ToString("MMMM")

'US Date Format mm/dd/yyyy
jn_lastweekSundayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset - 7), "MM/dd/yyyy")
jn_lastweekMondayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset - 7), "MM/dd/yyyy")
jn_lastweekTuesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset - 7), "MM/dd/yyyy")
jn_lastweekWednesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset - 7), "MM/dd/yyyy")
jn_lastweekThursdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset - 7), "MM/dd/yyyy")
jn_lastweekFridayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset - 7), "MM/dd/yyyy")
jn_lastweekSaturdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset - 7), "MM/dd/yyyy")

jn_thisweekSundayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset), "MM/dd/yyyy")
jn_thisweekMondayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset), "MM/dd/yyyy")
jn_thisweekTuesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset), "MM/dd/yyyy")
jn_thisweekWednesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset), "MM/dd/yyyy")
jn_thisweekThursdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset), "MM/dd/yyyy")
jn_thisweekFridayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset), "MM/dd/yyyy")
jn_thisweekSaturdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset), "MM/dd/yyyy")

jn_thisweekNextSundayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextMondayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextTuesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextWednesdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextThursdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextFridayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset + 7), "MM/dd/yyyy")
jn_thisweekNextSaturdayMMDDYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset + 7), "MM/dd/yyyy")

'Overseas Date Format dd/MM/yyyy
jn_lastweekSundayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset - 7), "dd/MM/yyyy")
jn_lastweekMondayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset - 7), "dd/MM/yyyy")
jn_lastweekTuesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset - 7), "dd/MM/yyyy")
jn_lastweekWednesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset - 7), "dd/MM/yyyy")
jn_lastweekThursdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset - 7), "dd/MM/yyyy")
jn_lastweekFridayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset - 7), "dd/MM/yyyy")
jn_lastweekSaturdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset - 7), "dd/MM/yyyy")

jn_thisweekSundayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset), "dd/MM/yyyy")
jn_thisweekMondayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset), "dd/MM/yyyy")
jn_thisweekTuesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset), "dd/MM/yyyy")
jn_thisweekWednesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset), "dd/MM/yyyy")
jn_thisweekThursdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset), "dd/MM/yyyy")
jn_thisweekFridayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset), "dd/MM/yyyy")
jn_thisweekSaturdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset), "dd/MM/yyyy")

jn_thisweekNextSundayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSundayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextMondayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intMondayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextTuesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intTuesdayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextWednesdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intWednesdayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextThursdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intThursdayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextFridayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intFridayOffset + 7), "dd/MM/yyyy")
jn_thisweekNextSaturdayDDMMYY = Format$(Now.Date.AddDays(((Now.DayOfWeek) * -1) + intSaturdayOffset + 7), "dd/MM/yyyy")

'Last and Next Date Variables
jn_lastMMYY = DateTime.Now.AddMonths(-1).ToString("MMyy")
jn_nextMMYY = DateTime.Now.AddMonths(+1).ToString("MMyy")
jn_lastYYMM = DateTime.Now.AddMonths(-1).ToString("yyMM")
jn_nextYYMM = DateTime.Now.AddMonths(+1).ToString("yyMM")
jn_lastYY = DateTime.Now.AddMonths(-1).ToString("yy")
jn_nextYY = DateTime.Now.AddMonths(+1).ToString("yy")
jn_lastMM = DateTime.Now.AddMonths(-1).ToString("MM")
jn_nextMM = DateTime.Now.AddMonths(+1).ToString("MM")

'Next and Previous Business Days
'MM/DD
jn_NextBusinessDayMMDDYY = Format$(Now.Date.AddDays(1), "MM/dd/yyyy")
jn_PreviousBusinessDayMMDDYY = Format$(Now.Date.AddDays(-3), "MM/dd/yyyy")

jn_Next2BusinessDayMMDDYY = Format$(Now.Date.AddDays(2), "MM/dd/yyyy")
jn_Next3BusinessDayMMDDYY = Format$(Now.Date.AddDays(3), "MM/dd/yyyy")
jn_Next4BusinessDayMMDDYY = Format$(Now.Date.AddDays(4), "MM/dd/yyyy")
jn_Next5BusinessDayMMDDYY = Format$(Now.Date.AddDays(7), "MM/dd/yyyy")
jn_Next6BusinessDayMMDDYY = Format$(Now.Date.AddDays(8), "MM/dd/yyyy")
jn_Next7BusinessDayMMDDYY = Format$(Now.Date.AddDays(9), "MM/dd/yyyy")

jn_Previous2BusinessDayMMDDYY = Format$(Now.Date.AddDays(-4), "MM/dd/yyyy")
jn_Previous3BusinessDayMMDDYY = Format$(Now.Date.AddDays(-5), "MM/dd/yyyy")
jn_Previous4BusinessDayMMDDYY = Format$(Now.Date.AddDays(-6), "MM/dd/yyyy")
jn_Previous5BusinessDayMMDDYY = Format$(Now.Date.AddDays(-7), "MM/dd/yyyy")
jn_Previous6BusinessDayMMDDYY = Format$(Now.Date.AddDays(-10), "MM/dd/yyyy")
jn_Previous7BusinessDayMMDDYY = Format$(Now.Date.AddDays(-11), "MM/dd/yyyy")

'Last and Next Business Days Overseas
'DD/MM
jn_NextBusinessDayDDMMYY = Format$(Now.Date.AddDays(1), "dd/MM/yyyy")
jn_PreviousBusinessDayDDMMYY = Format$(Now.Date.AddDays(-3), "dd/MM/yyyy")

jn_Next2BusinessDayDDMMYY = Format$(Now.Date.AddDays(2), "dd/MM/yyyy")
jn_Next3BusinessDayDDMMYY = Format$(Now.Date.AddDays(3), "dd/MM/yyyy")
jn_Next4BusinessDayDDMMYY = Format$(Now.Date.AddDays(4), "dd/MM/yyyy")
jn_Next5BusinessDayDDMMYY = Format$(Now.Date.AddDays(7), "dd/MM/yyyy")
jn_Next6BusinessDayDDMMYY = Format$(Now.Date.AddDays(8), "dd/MM/yyyy")
jn_Next7BusinessDayDDMMYY = Format$(Now.Date.AddDays(9), "dd/MM/yyyy")

jn_Previous2BusinessDayDDMMYY = Format$(Now.Date.AddDays(-4), "dd/MM/yyyy")
jn_Previous3BusinessDayDDMMYY = Format$(Now.Date.AddDays(-5), "dd/MM/yyyy")
jn_Previous4BusinessDayDDMMYY = Format$(Now.Date.AddDays(-6), "dd/MM/yyyy")
jn_Previous5BusinessDayDDMMYY = Format$(Now.Date.AddDays(-7), "dd/MM/yyyy")
jn_Previous6BusinessDayDDMMYY = Format$(Now.Date.AddDays(-10), "dd/MM/yyyy")
jn_Previous7BusinessDayDDMMYY = Format$(Now.Date.AddDays(-11), "dd/MM/yyyy")

'User Variables From Formulas On Reports
jnUser1-jnUser20

'Parameter Variables Based On Job Settings
jnParm1-jnParm30

'Bursting Variable Values
jnBurstValue - Always set to current burst value
jnBurstEmail - This is set to current burst email value from formula jnBurstEmail in Group Header 1

---

Note, if you format a jnVariable on the report by clicking it and setting a menu item (or using the right-click menu), this formatting does NOT carry over to Report Runner Batch when the value is fetched. The value is all that Crystal Reports returns to us. If you have specific formatting that's needed (like a dollar sign ($) in front of a number), you need to add the dollar sign manually wiithin the jnVariable and return the value as a string.





Related Articles

Login

 
Forgot password?
Register now

Language