AX1444

Display date and date-time values in web reports

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

  • 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.

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

Use 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 choosing a date part for the purposes of this report, you effectively change 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 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 are 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 October 2023 but in this case, the column values in the report are still the full date. If you use this column as a row dimension, multiple instances of October 2023 appear as rows because the underlying column values are different dates in October 2023, such as 10/102023, 10/11/2023, and so on. You essentially group the report by the full date values and not by the display format. Instead, if you group the report by month and year (or "yearmo"), select the YearMo date part. 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 is 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 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.

  • Use date parts 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 Use 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.

Configure 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.

  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 pane 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 returns 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 choose a different date part to extract and retrieve a specific aspect of the date or date-time value. When you select a date part, the column data is effectively transformed into the date part values for purposes of this report. For more information about 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 appear.

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

    For more information about 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 about 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, the properties are labeled DateTime part to retrieve, DateTime format, and Custom DateTime Format.

Date part and format options

Configure Date and DateTime columns to appear in various ways using the date part and format options:

  • Date part: Specify the part of the date or date-time value to appear—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 show the selected date part. For example, if you select full date, you can show it as 10/15/2023 or October 2023 or Thursday, October 15, 2023. If you select Month, you can show it as 10 (the month number), Oct, or October.

The following tables describe 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, the Date format property does not appear.

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 2023)
  • Month Year (October 2023)
  • Month/Date/Year as Date (10/15/2023)
  • Day, Month Date, Year (Thursday, October 15,2023)
  • YearMonth as Number (202310)
  • YearMonthDay as Number (20231015)

Date data types have a configured default that is set at the grid level. If you set a column to use Default and change the grid-level defaults, the column updates to use the new default format. For more information, see Configure 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, and the following additional options:

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

Date Time data types have a configured default that you set at the grid level. If you set a column to use Default and you change the grid-level defaults, the column updates to use the new format. For more information, see Configure 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 (202310)

Year

Use the year part of the date.

 Full year (2023)

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)
NOTE:
• If you configure a column to show 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 you configure a DateTime column to show 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 you configure a Date or DateTime column 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 must adjust this setting, accordingly. For more information about system configuration settings, see System configuration settings.

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

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

When the fiscal year is different than the calendar year, the fiscal year options 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/2023:

  • The Year part returns 2023, whereas the Fiscal Year part will return 2024.
  • The Quarter part returns Q3, whereas the Fiscal Quarter part will return Q1.
  • The Month part returns 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 (202410)

Fiscal Year

Use the fiscal year that the date belongs to.

Full year (2024)

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 show 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. Use the following case-sensitive syntax 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 to show 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 (2023), use one letter (y).
  • To render a two-digit year (20), use two letters (yy).

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