Following the parts 1 and 2 of my hints and tips collection for Electronic reporting, let me shed some light on working with dates and date formatting.

Cut-off date

If a classic Excel transaction report must be implemented in the Electronic Reporting module, then a cut-off date is a common requirement: show all transactions up to and including a certain date, or show all transactions if the user hasn’t selected any date. Classically, a parameter in the dialog is presented to the user, this requires a User parameter of the DATE type in the format or – better – in the date model mapping (see also Electronic reporting in depth).

This parameter must be used in a Calculated field of the Record List type. Below is a snippet from one of my very first reports dated back to 2016; it extracts ledger transactions from the current company for the German GDPdU dump file:
WHERE(GLaccountEntry,
AND(
NOT(GLaccountEntry.AccountingCurrencyAmount=0),
GLaccountEntry.'>Relations'.GeneralJournalEntry.Ledger = Ledger.'current()',
GLaccountEntry.'>Relations'.GeneralJournalEntry.'>Relations'.FiscalCalendarPeriod.Type = FiscalPeriodType.Operating,
GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate >= FromDate,
OR(GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate <= ToDate, ToDate=NULLDATE())))

In hindsight, this was a terrible implementation. The WHERE operator works with in-memory lists. In essence, it loads all transactions from all companies and all periods and years into an internal XML container, and THEN starts filtering it by company and date. Not surprisingly, the performance degrades rapidly and the report execution time grows exponentially.

The FILTER is a better function as it compiles to a direct SQL statement and returns a reduced dataset; however, it has limited capabilities and does not support cross joins as above.

A mature solution would be to perform the filtering in 2 steps: one Calculated list fetches a maximally pre-filtered list from the SQL database, and subsequent Calculated field variables apply additional dynamic filters to this subset.
The below expression returns a list of all cost project transactions up to a certain date:
FILTER(ProjTransPosting,
AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost,
OR(ProjTransPosting.LedgerTransDate <= $ToDate, $ToDate=NULLDATE())))
where $ToDate is the user parameter.

To simplify and reuse the classic pattern WHERE LedgerTransDate <= $ToDate OR $ToDate=NULLDATE(), an interim variable (Calculated field) $ToDateOrInfinity may be declared (I could not find any other way to pass a date literal into an ER expression, that’s why the crude DATEVALUE(“31-12-2154″,”dd-MM-yyyy”)):
IF('$ToDate'>NULLDATE(), '$ToDate', DATEVALUE("31-12-2154","dd-MM-yyyy"))
and the above query reduces to just
FILTER(ProjTransPosting, AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost,
ProjTransPosting.LedgerTransDate <= $ToDateOrInfinity))

This led to an issue on the UI, however. User parameters from the [default] Mapping designer propagate to the format, are merged with the user parameters defined in the Format designer and displayed together in the common dialog window. According to Maxim B. from the Microsoft R&D Center in Moscow, a user parameter is shown at the run time if 2 conditions have been met:

• the visibility is not turned off;
• in the mapping, the user parameter is bound to the model either directly or indirectly.

Seemingly, the above indirect usage obscures the parameter from the ER format and it disappears from the dialog. The solution was to bind $ToDate directly to an unused field in the model.

Date formatting: DATETIME to DATE

I spent quite some time trying to find an embedded function to convert a DATETIME type and bind to a DATE cell in Excel. Obviously, you can declare the format node a STRING and apply the DATETIMEFORMAT() function, but this circumvents the locale settings.

The solution was dumb, but it worked:
DATEVALUE(DATETIMEFORMAT(@.'Created date', "dd-MM-yyyy"), "dd-MM-yyyy")

First, Last date of the month

The below formulas work in model mappings. Assume the parmPeriodDate a user parameter. The first formula is easy and trivial:
DATEVALUE("01-"&DATEFORMAT(parmPeriodDate, "MM-yyyy"), "dd-MM-yyyy")

The second formula is crazy. It needs a declaration of the system class DateTimeUtil nearby in the model mapping data source section.
DATEVALUE(DATETIMEFORMAT(ADDDAYS(DATETODATETIME(DATEVALUE("01-"&
DATETIMEFORMAT(DateTimeUtil.addMonths(DATETODATETIME(parmPeriodDate),1),"MM-yyyy"), "dd-MM-yyyy")),-1),"dd-MM-yyyy"),"dd-MM-yyyy")
It takes the parameter (e.g. 15.02.2023), adds a month (15.03.2023), makes the 1st of that month (01.03.2023), and subtracts 1 day (28.02.2023).

Date formatting: Show an empty date in Excel as blank

An empty date in the format data source is printed in Excel as 02.01.1900. This is not good and very distracting. Again, an obvious solution is DATEFORMAT() in a string type cell, but it just doesn’t feel right.
An elegant approach is a generic Transformation in the Format designer: Formula transformation NoNullDate =
IF(parameter<=NULLDATE()+1, "", DATEFORMAT(parameter, "dd-MM-yyyy"))
This can be re-used and quickly applied to every node: