AX1444

Displaying date and date-time values in web reports

When using Date or DateTime columns in web reports, you can display the values in a variety of different ways:

  • You can choose to report on the full date or date-time value, or you can choose to report on just a part of the value by selecting a date part. When you use a date part you are extracting a specific portion of the date, such as Year, Month, Quarter, and so on.

  • You can choose various formatting options for the date or date-time value. For example, you can display the full date as 10/10/2022 or as October 10, 2022. Many of the date part options also support various formats. For example, you can display the Month date part as 10 or Oct or October.

Using date formatting versus date parts

There is a significant difference between defining a display format for a date or date-time value, versus using a date part. When you choose a date part, then for the purposes of this report you are effectively changing the column data type and contents to match the selected date part. For example, if you specify the date part as Month, the column is now treated as if it contains values from 1 to 12 representing each month. All of the other information about the date or date-time value is ignored. This means that you can use the column as a row dimension, and the values will be grouped by month instead of by the underlying date or date-time value.

Date formatting, on the other hand, is primarily for display only. The display format does not fundamentally change the way the values are treated. For example, you can choose to format a full date value as simply October 2022, but in this case the column values in the report are still the full date. If you use this column as a row dimension, you will likely see multiple instances of October 2022 as rows, because the underlying column values are different dates in October 2022 such as 10/102022, 10/11/2022, and so on. You are really grouping the report by the full date values and not by the display format. If instead you want to group the report by month and year (or "yearmo"), then you can select the YearMo date part. Now the column values are effectively transformed to integer yearmo values for purposes of the report, enabling the report data to be grouped by unique yearmo combinations.

Other benefits of using date parts include:

  • Column values are sorted by the date part and its chosen display format. For example, if the date part is Month, the column will be sorted as expected whether you choose to display the month as 1-12 or as January-December. Full date or date-time values are always sorted by the full date or date-time regardless of display format.

  • If end-user filtering is enabled for the column, the filtering options match the configured date part. For example, if the date part is Quarter, the user can filter by selecting from the list of Q1-Q4 values.

  • Date parts can also be used when defining filters for the report data, such as general filters or column filters. This makes it easier to construct filters based on a portion of the date or date-time value. For more information, see Using date part filtering.

  • Date parts support the concept of a fiscal year calendar. For example, you can choose to return the calendar month or the fiscal month, if they are different for your organization.

Configuring date parts and display formats for Date and DateTime columns

Use the Column Configuration properties to specify the date part and display format for any Date or DateTime columns in your report.

To configure the date part and display format for a Date or DateTime column:

  1. On the Build tab of the Report Builder, in the Report Canvas, click a column name in either the Row Dimensions box or the Column Definitions box to select that column.

  2. Complete the following Column Configuration properties in the Configuration Panel, on the General tab:

    Item Description

    Date part to retrieve

    Specifies the date or date-time part to retrieve for this column. For example, you can return the full date value, or just the year or month, or the fiscal year or month.

    By default, this property is set to Full Date or Full DateTime, which means the column will return the full date or date-time value. You can then use the Date format property to specify how this value should display in the report.

    You can optionally chose a different date part in order to extract and retrieve a specific aspect of the date or date-time value. When a date part is selected, the column data is effectively transformed into the date part values for purposes of this report. For more information on the available date part options, see Date part and format options.
    Date format

    Specifies the display format for the date or date-time values. The available formatting options depend on the specified Date part to retrieve. Some date parts, such as Year, do not have additional formatting options, in which case this property does not display.

    The label of this property varies depending on the selected date part. For example, if you select Month as the date part, then the label for this setting is Month format.

    For more information on the available formats for each date part, see Date part and format options.
    Custom Date Format

    Specifies the custom format to use for the full date or date-time value, if the Date format property is set to Custom. For more details on the available options to define default custom formats, see Custom formats.

    NOTE: The labels for these properties vary depending on whether the column is a Date or DateTime column. For example, if the column is DateTime, then the properties are labeled DateTime part to retrieve, DateTime format, and Custom DateTime Format.

Example date part and format properties in the Column Configuration panel

Date part and format options

You can configure Date and DateTime columns to display in various ways using the date part and format options:

  • Date part: Specify the part of the date or date-time value that you want to display—such as the full date or date-time, the year or fiscal year of the date, the month or fiscal month of the date, or the hour or minute from the time.
  • Format: Specify the format to display the selected date part. For example, if you select full date, you can display it as 10/15/2022 or October 2022 or Thursday, October 15, 2022. If you select Month, you can display it as 10 (the month number), Oct, or October.

The following tables detail the date part and format options. Where multiple formats are available, the default format is shown in bold. If only one format is available for a particular date part, then the Date format property does not display.

Standard date and time options

Date Part Description Format

Full Date

Use the full date stored in the column. Only applies to Date columns.

This option is the default date part for Date columns.
  • Default
  • Custom
  • ShortMonth Year (Oct 2022)
  • Month Year (October 2022)
  • Month/Date/Year as Date (10/15/2022)
  • Day, Month Date, Year (Thursday, October 15,2022)
  • YearMonth as Number (202210)
  • YearMonthDay as Number (20221015)

Date data types have a configured default that is set at the grid level. If a column is set to use Default and the grid-level defaults are changed, the column will update to use the new default format. For more information, see Configuring grid properties in a web report.

Full DateTime

Use the full date-time stored in the column. Only applies to DateTime columns.

This option is the default date part for DateTime columns.

Same as Full Date, plus the following additional options:

  • Month/Date/Year Hour:Minute as DateTime (10/15/2022 13:25)
  • Month/Date/Year Hour:Minute:Second DayPeriod as DateTime (10/15/2022 1:25:00 PM)
  • Day, Month Date, Year Hour:Minute:Second DayPeriod (Thursday, October 15, 2022 1:25:00 PM)
  • YearMonthDate Hour:Minute as Number (20221015 13:25)

Date Time data types have a configured default that is set at the grid level. If a column is set to use Default and the grid-level defaults are changed, the column will update to use the new format. For more information, see Configuring grid properties in a web report.

Date Only

 Use the date part of the date-time. Only applies to DateTime columns. Same formats that are available for Full Date.

YearMo

Use the combined year and month of the date.

Year and Month combined (202210)

Year

Use the year part of the date.

 Full year (2022)

Quarter

Use the quarter for the date.
  • Number of the Quarter (1-4)
  • Number of the Quarter with Prefix (Q1)
  • Text Description (1st quarter)

Month

Use the month part of the date.
  • Number of the Month (1-12)
  • Number of the Month with 2-Digits (01)
  • Short Name of the Month (Jan)
  • Name of the Month (January)

Week

Use the number of the week for the date, within the year.

 Number of the Week (1-52)

Day of Year

Use the day of the year for the date.

 Number of the Day (1-365)

Day of Month

Use the day of the month for the date.

 Number of the Date (1-31)

Day of Week

Use the day of the week for the date. The first day of the week is Sunday.
  • Number of the Day (1-7)
  • 2-Letter Abbreviation for the Day (Su)
  • Short Name of the Day (Sun)
  • Name of the Day (Sunday)

Hour

Use the hour of the date-time. Only applies to DateTime columns.
  • 24-Hour Clock Number (0-23)
  • 12-Hour Clock with Day Period (1 AM)
  • 24-Hour Clock as Hundreds (100)

Minute

 Use the minute of the date-time. Only applies to DateTime columns. Number of the minute (0-59)

NOTES:  

  • If a column is configured to display the full date or date-time, but the selected format only shows a part of it, the column sorting and filtering remains based on the full date or date-time value.

  • If a DateTime column is configured to display the hour, the column filtering is always based on 0-23, regardless of the display format. For example, if the display format is a 12-hour clock with day period, filtering by 13 displays values of 1 PM.

  • If a Date or DateTime column is configured to use a date part that is not the full date part—such as Year, Quarter, or Month—the column becomes a Dimension date type for purposes of inheriting the default alignment and column width.

Fiscal year options

The fiscal year for your system is determined by the system configuration setting ClientFiscalYearEndMonth. By default, this is set to 12, which means the fiscal year is the same as the calendar year. If your organization uses a different fiscal year end, your implementation consultant should adjust this setting accordingly. For more information on system configuration settings, see System configuration settings.

For example, if your organization's fiscal year ends in June, the ClientFiscalYearEndMonth setting should be changed to 6. This means:

  • A date of 6/1/2022 is in fiscal year 2022 and represents month 12 of the 2022 fiscal year.
  • A date of 7/12022 is in fiscal year 2023 and represents month 1 of the 2023 fiscal year.

When the fiscal year is different than the calendar year, the fiscal year options will return different date information than the corresponding standard date options. Continuing the example where the fiscal year end is June, the following return values apply to a date of 7/1/2022:

  • The Year part will return 2022, whereas the Fiscal Year part will return 2023.
  • The Quarter part will return Q3, whereas the Fiscal Quarter part will return Q1.
  • The Month part will return 7, whereas the Fiscal Month part will return 1.
Date Part Description Format

Fiscal YearMo

Use the combined fiscal year and month that the date belongs to.

Year and Month combined (202310)

Fiscal Year

Use the fiscal year that the date belongs to.

Full year (2023)

Fiscal Quarter

Use the fiscal quarter that the date belongs to.
  • Number of the Quarter (1-4)
  • Number of the Quarter with Prefix (Q1)
  • Text Description (1st quarter)

Fiscal Month

Use the fiscal month that the date belongs to.
  • Number of the Month (1-12)
  • Number of the Month with 2-Digits (01)
  • Number And Short Name of the Month (1 - Jan)
  • Number And Name of the Month (1- January)

Custom formats

When using the Full Date or Full DateTime parts, you can optionally specify a custom format to display the date or date-time value. When you select Custom as the format, a Custom Date Format box becomes available so that you can enter the custom format syntax. The following case-sensitive syntax can be used in the custom format:

Desired Date/Time Part Syntax Notes

Day Period

a

Returns the day period AM or PM.

Day of Month

d

For the day number (1), use one or two letters (d or dd). dd enforces 2 digits.

Day of Week

E or e
  • For the abbreviated day name (Sun), use one upper-case letter (E).
  • For the full name (Sunday), use four upper-case letters (EEEE).
  • For the numerical day of the week (1), use one lower-case letter (e). Sunday is the first day.

Hour

H or h
  • For the hour in the 12-hour clock (1-12), use one or two lower-case letters (h or hh). hh enforces two digits.

  • For the hour in the 24 hour clock (0-23), use one or two upper-case letters (H or HH). HH enforces two digits.

NOTE: Use k if you want to display the 24 hour clock as 1-24 instead of 0-23.

Minutes

m

For the minutes number (1), use one or two letters (m or mm). mm enforces 2 digits.

Month

M
  • For the numerical month (1), use one or two letters (M or MM). MM enforces 2 digits.
  • For the short name (Jan), use three letters (MMM).
  • For the wide name (January), use four letters (MMMM).

Seconds

s

For the seconds number (1), use one or two letters (s or ss). ss enforces 2 digits.

Quarter

Q
  • For the numerical quarter (1), use one or two letters (Q or QQ). QQ enforces 2 digits.
  • For the abbreviation (Q1), use three letters (QQ).
  • For the text description (1st quarter), use four letters (QQQQ).

Year

y
  • To render the full year (2022), use one letter (y).
  • To render a two-digit year (20), use two letters (yy).

For example, to render a date as 2022 Oct 10, you would enter the following into the Custom Date Format box: y MMM d.

Example custom date format