Report Designer - Using dates in expressions on Sage Accounts reports

Show -st, -nd, -rd or -th in a date

If using other date variables, replace AUDIT_HEADER.DATE with the relevant variable.

• Using AUDIT_HEADER.DATE:
  
  CString(DayFromDate(AUDIT_HEADER.DATE)) +
  ((DayFromDate(AUDIT_HEADER.DATE) % 10) = 1 AND DayFromDate(AUDIT_HEADER.DATE) <> 11 ? "st":
  (DayFromDate(AUDIT_HEADER.DATE) = 2 OR DayFromDate(AUDIT_HEADER.DATE) = 22) ? "nd":
  (DayFromDate(AUDIT_HEADER.DATE) = 3 OR DayFromDate(AUDIT_HEADER.DATE) = 23) ? "rd" :"th") +
  FormatString(" {0:MMMM yyyy}", AUDIT_HEADER.DATE)
  

• Using INVOICE.INVOICE_DATE:
  
  CString(DayFromDate(INVOICE.INVOICE_DATE)) +
  ((DayFromDate(INVOICE.INVOICE_DATE) % 10) = 1 AND DayFromDate(INVOICE.INVOICE_DATE) <> 11 ? "st":
  (DayFromDate(INVOICE.INVOICE_DATE) = 2 OR DayFromDate(INVOICE.INVOICE_DATE) = 22) ? "nd":
  (DayFromDate(INVOICE.INVOICE_DATE) = 3 OR DayFromDate(INVOICE.INVOICE_DATE) = 23) ? "rd" :"th") +
  FormatString(" {0:MMMM yyyy}", INVOICE.INVOICE_DATE)

Age of a transaction

To show the age of a transaction on the date the report is run to:

AUDIT_HEADER.DATE_TO - AUDIT_HEADER.DATE

Last day of the month based on a date

For example, to show the end of the month that a transaction is dated in.

CreateDateTime(YearFromDate(AUDIT_SPLIT.DATE),
MonthFromDate(AUDIT_SPLIT.DATE),
DaysInMonth(YearFromDate(AUDIT_SPLIT.DATE),
MonthFromDate(AUDIT_SPLIT.DATE)))

For example, if the transaction date is 12/11/12, the expression shows 30/11/12.

Last day of next month based on a date

For example, an invoice due date may be the end of the month following the invoice date. To show this:

MonthFromDate(AUDIT_HEADER.DATE) < 12 ?
CreateDateTime( YearFromDate(AUDIT_HEADER.DATE),
MonthFromDate(AUDIT_HEADER.DATE)+1,
DaysInMonth (YearFromDate(AUDIT_HEADER.DATE), MonthFromDate(AUDIT_HEADER.DATE) +1) ) :
CreateDateTime(YearFromDate(AUDIT_HEADER.DATE)+1, 1, 31)

For example, if the invoice date is 12/11/12, the expression shows 31/12/12.

First day of next month based on a date

INVOICE.INVOICE_DATE +
((DaysInMonth(YearFromDate(INVOICE.INVOICE_DATE),
MonthFromDate(INVOICE.INVOICE_DATE)))) -
(StringToInteger(DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd"))) +1

Specific date next month based on a date

For example, an invoice due date may be the 20th of the month following the invoice date. The following expression shows this due date:

CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),
MonthFromDate(INVOICE.INVOICE_DATE),
DaysInMonth(YearFromDate(INVOICE.INVOICE_DATE),
MonthFromDate(INVOICE.INVOICE_DATE))) + 20

For example, if the invoice date is 12/11/12, the expression shows 20/12/12.

Show a date in full

The following expression shows the report date in the format Tuesday 1st November 2012:

(DateTimeToFormattedString(REPORT.DATE,"dddd") + " " +
CString(DayFromDate(REPORT.DATE))) + (
(DayFromDate(REPORT.DATE) % 10 = 1 and DayFromDate(REPORT.DATE) <> 11) ? "st" :
(DayFromDate(REPORT.DATE) % 10 = 2 and DayFromDate(REPORT.DATE) <> 12) ? "nd" :
(DayFromDate(REPORT.DATE) % 10 = 3 and DayFromDate(REPORT.DATE) <> 13) ? "rd" :
" th") + FormatString(" {0:MMMM yyyy}", REPORT.DATE)

First day of the financial year

CreateDateTime(COMPANY.FINANCIAL_YEAR, COMPANY.START_MONTH,1)

Last day of the financial year

CreateDateTime(COMPANY.FINANCIAL_YEAR +1, COMPANY.START_MONTH,1) -1

Sum a period for column based reporting

The following expression sums the net amounts of transactions posted in period 1 of the financial year:

Sumif(AUDIT_HEADER.NET_AMOUNT,
MonthFromDate(AUDIT_HEADER.DATE) = MonthFromDate(DateFromPeriod(1)) and YearFromDate(AUDIT_HEADER.DATE) = YearFromDate(DateFromPeriod(1)))

The following expression sums the net amounts of transactions posted in period 2 of the financial year:

Sumif(AUDIT_HEADER.NET_AMOUNT,
MonthFromDate(AUDIT_HEADER.DATE) = MonthFromDate(DateFromPeriod(2)) and YearFromDate(AUDIT_HEADER.DATE) = YearFromDate(DateFromPeriod(2)))

For more examples of column based reporting, please refer to article .

Remove time stamps

When using expressions to show dates, depending on the expression you may see a time stamp after the date when you preview the report, for example, 10/02/12 00:00:00. If this occurs, you can use the DateTimeToFormattedString function to remove the time stamp.

For example, if the following expression shows the date in the format dd/MM/yyyy 00:00:00.

"Invoice due for payment on or before " + (INVOICE.INVOICE_DATE+Days(30))

To remove the 00:00:00 you must include DateTimeToFormattedString within the expression as follows:

"Invoice due for payment on or before " +
( DateTimeToFormattedString( (INVOICE.INVOICE_DATE+Days(30) ), "dd/MM/yyyy") )