AX1021

Calendar refresh variable

Calendar refresh variables prompt users to specify a date from a calendar. The date is written back to the data source, where it can be used to impact data queries.

For example, you can use the calendar variable to prompt users to select a start date for the data in a report. The date can then be used in a filter that affects the data refresh.

When used in the Web Client, calendar variables can also be used to select a month or year.

Variable behavior

The variable displays as a text box with a calendar button next to it. The user can click the button to select a value from the calendar.

Desktop Client: Example Calendar refresh variable

Web Client: Example Calendar refresh variable

In the Web Client, the current date shows at the bottom of the calendar for reference.

Once the user has selected a date from the calendar, the selected date displays in the text box. The user cannot type a date into the text box; only selection from the calendar is allowed.

In the Web Client only, Calendar refresh variables can also be used to select a month/year combination, or a year. The month and year options are not available in the Desktop Client.

Web Client: Example Calendar refresh variable to select month/year

Web Client: Example Calendar variable to select year

Variable properties

This section explains how to complete a variable row in the RefreshVariables data source when defining a Calendar variable. Some data source columns do not apply in this case and are not discussed here. If these inapplicable columns are present in the data source, they should be left blank on rows that define Calendar variable types. If you are using the Data Source Assistant to complete the variable properties, then only the applicable columns will be shown in the task pane.

For more information on the RefreshVariables data source in general, see Defining refresh variables.

General variable properties

All refresh variables use a common set of general properties such as the variable name, display name, and whether the variable is enabled or required. Any special considerations for Calendar variables are noted.

Column Tag Description

[Name]

 (missing or bad snippet)

[DisplayName]

 (missing or bad snippet)

For example, you might want to define the variable name as "StartDate" but use a display name of "Start Date" or "Select a start date".

[VariableType]

Specifies the variable type. Enter Calendar to allow users to select a date from a calendar.

[IsEnabled]

 (missing or bad snippet)

[SelectedValue]

The user's selected value for the variable will be placed in this cell. For Calendar variables, the value is an Excel date serial number.

When setting up the file to use the variable value, point your formulas to this cell.

(missing or bad snippet)

NOTE: The cell format for this property should be set to Date. DateTime formats are not supported and may cause errors.

[IsRequired]

 (missing or bad snippet)

[DependsOn]

 (missing or bad snippet)

[DefaultValueonOpen]

 (missing or bad snippet)

[ClearSelectedValueonSave]

 (missing or bad snippet)

[ClearSelectedValueonOpen]

 (missing or bad snippet)

[GroupName]

 (missing or bad snippet)

[CollapseOnOpen]

 (missing or bad snippet)

Variable-specific properties

The following additional properties apply to Calendar variable types:

Column Tag Description

[MinDate]

Optional. The earliest date that is valid for a user to select in the calendar. If specified, the calendar control will not allow the user to select a date that is earlier than this date.

When using the Month or Year display format, the minimum date must still be a full date. The appropriate minimum month and year will be determined from that date.

NOTE: The cell format for this property should be set to Date. DateTime formats are not supported and may cause errors. For more information, see Handling date formats.

[MaxDate]

Optional. The latest date that is valid for a user to select in the calendar. If specified, the calendar control will not allow the user to select a date that is later than this date.

When using the Month or Year display format, the minimum date must still be a full date. The appropriate minimum month and year will be determined from that date.

NOTE: The cell format for this property should be set to Date. DateTime formats are not supported and may cause errors. For more information, see Handling date formats.

[DisplayFormat]

Optional, applies to Web Client only. Specifies the type of date value for selection:

  • Date: Users select specific dates from a calendar control. This is also the default behavior if no display format is specified.
  • Month: Users select a month and year combination from a drop-down selection.

  • Year: Users select a year from a drop-down selection.

The display format determines the values for selection and the display of the selected value in the variable. However, the return value written to the [SelectedValue] field is always a full date. See Handling date formats for more information.

[AutoQuoteString]

Optional. Specifies whether the date value is placed in single quotation marks when it is written to the [SelectedValue] column (True/False). If omitted or blank, the default setting is False, which means the date value is not quoted.

This option is intended to make it easier to create filters based on the selected value, when the selected value must be wrapped in single quotation marks. For example: Request.Date='2/1/2021'.

The following properties do not apply to Calendar variables: PlaceHolderText, ListChoices, ColumnName, AdditionalColumns, ColumnFilter, AllowMultiSelect, DataSourceName, Hierarchies, UseAsQuickFilter, TooltipColumn, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Example data source

The following screenshot shows example Calendar variables.

  • The first two variables define start and end dates. The End variable is dependent on the Start variable, so that the minimum value of the end date can use a formula that points to the selected value of the start date. This is to ensure that the user does not accidentally set the end date to an earlier value than the start date.

  • The third and fourth variables use the [DisplayFormat] to select a month/year combination and a year. This configuration is only valid for use in the Web Client; it will be ignored in the Desktop Client.

Handling date formats

Within the Calendar refresh variable, the selected values are displayed as follows, depending on the specified [DisplayFormat]. The exact format depends on your system locale.

  • Date: Dates are displayed in an Excel "short date" format, such as 1/1/2021.
  • Month: Months are displayed in month/year format, such as January 2021. Only applies to Web Client.
  • Year: Years are displayed as the year number, such as 2021. Only applies to Web Client.

However, in all cases, the selected value is written to the [SelectedValue] field as an Excel date serial number. If you want to use just the selected month or the year in your calculations, you may need to use functions such as MONTH or YEAR to extract the information from the full date.

If you want to set a default value for the refresh variable (or set minimum and maximum dates to restrict the valid selections), then your value must be resolvable as an Excel date value. For example, if you want to set a default value when using the Year selection type, you cannot simply enter the number 2021. Instead, you must enter a date such as 1/1/2021. The refresh variable will resolve this date value as the year 2021. If you enter just the number, then the refresh variable will not interpret that value as a date.