Calculated dates

Pentaho Report Designer

Version
10.0.x
Audience
anonymous
Part Number
MK-95PDIA008-05

A date is typically displayed as a static number or a range, but this formula enables you to display specific dates like "the first Monday of the month" or "every second Wednesday."

Perform the following steps to create a formula to display a calculated date in a report:

  1. Open an existing report or create a new report and establish a data source and query, then drag your data-driven fields onto the canvas.
  2. Left-click the text field you want to print the calculated date in.
    If you do not have a text field dedicated to this task, create one now.
  3. Click the Structure tab and click value in the common section under the Attributes tab.
  4. Click the round green + (Add Expression) icon in the Formula column. The Expression dialogue will appear.
  5. Click the ellipsis (...) to open the Formula Editor dialog box.
  6. Select Date/Time from the Category drop-down box.
  7. Double-click the DATEVALUE item in the list on the left.
  8. Enter in your DATEVALUE formula, then click OK.
    For more information on DATEVALUE's parameters, see the OASIS reference page for Alternatively, you can consult the examples below and modify them for your purposes.
  9. Click Close to exit the Expression dialog box.
  10. Click Preview and verify that your date values are properly calculated and formatted.
    You may have to adjust your query if it does not produce a testable result set.
The date values you specified should now appear correctly in your report.
Some common calculated date formulas:

1st day of current month

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());1))

Sunday of current week

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now();2)))

Saturday of current week

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now())+7))

Current day, date, and time

=NOW()

Current date

=TODAY()

Yesterday's date

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW()-1)))