Adding Time-Based Comparison Columns to Reports

You can customize reports to include comparisons of values across different date ranges or period ranges. To include data from multiple time periods, you need to create multiple report columns for the same field, and define a different alternate date range (or alternate period range) for each column. Comparison columns can be added for any numeric value field available for a report, such as dollar amount or item count. After you add comparison columns to a report, you can also use them in formula fields to show variances between specific dates and date ranges.

For example, a CFO may want an income statement to compare dollar amounts between different fiscal years. A CFO can include this data by adding multiple amount columns to the income statement and selecting an alternate date range for each column. She can then also create a custom formula field that shows the variance between each year.

A report's overall date range is set in a date filter on the Filters page of the Report Builder. You can change this range by editing the date filter on the Filters page, or by changing the selection in the date or period dropdown list on the report results page footer at run time. See Choosing a Date or Period Range for a Report.

Alternate date ranges and period ranges for columns are set on the Edit Columns page of the Report Builder. For financial statements, use the Financial Report Builder to edit columns. See Financial Report Builder Edit Columns Page.

Alternate Range Types

After you add a column to a report, you can select one of the following alternate range types:

• Relative to report date

  • Defined in relation to the overall date range set for the report.

  • Changes when the overall report date range changes.

  • Most commonly used type.

  • Example: In December 2009, you add a column with an alternate date range of last month to a report and run it. It displays December 2009 data for most columns, and November 2009 data for the alternate date range column. When you change the report date range to January 2010, it displays January 2010 data for most columns and December 2009 data for the alternate date range column.

• Relative to today's date

  • Defined in relation to the current date when the report is run

  • Does not change when the overall report date range changes.

  • This type is provided for backward compatibility with the alternate date range functionality available before Version 2010 Release 1.

  • Example: In December 2009, you add a column with an alternate date range of last month to a report and run it. It displays December 2009 data for most columns, and November 2009 data for the alternate date range column. When you change the report date range to January 2010, it displays January 2010 data for most columns and still displays November 2009 data for the alternate date range column.

Note:

For an alternate range that is “to date”, such as this fiscal year to date, you should select an alternate range type of Relative to today's date. If you define an alternate range type of Relative to report date, and choose an alternate range like this fiscal year to date, alternate date range column results are calculated as of the From date of the overall report range, and this column's comparison with other report columns is not useful.

Predefined Relative Alternate Ranges

After you have selected an alternate range type for a report column, a new dropdown list appears, where you can select the alternate date range or alternate period range for the column.

The predefined date ranges and period ranges available for alternate date range columns are the same as those that can be set for a report overall. See the following for details:

• Period Selectors

• Date As Of Selectors

• Date Range Selectors

Absolute Alternate Ranges

If you add a column with an alternate range type of Relative to today's date, you can set up an absolute alternate date range or period range.

You define an absolute alternate range by selecting Custom as the alternate date range or alternate period range. Then do the following:

• For an absolute date range, enter dates in the From and To fields, such as 12/27/2009 and 1/27/2010, or for some reports, enter a date in the As of field.

• For an absolute period range, choose a period from the Periods list box, such as Jan 2010.

Balance Forward Alternate Range

If you add a column with an alternate range type of Relative to report date, an alternate range of Balance Forward is available.

A column with the Balance Forward alternate range reflects ALL activity prior to the beginning of the overall report range, or to the “As of” date, of the report.

This option is available for both alternate date range and alternate period range. For example:

• If you run a report with a range of 12/1/09-12/31/09, the Balance Forward alternate range is 1/1/1970-11/31/09.

• If you run a report with an “As of” date of 1/1/09, the Balance Forward alternate range is 1/1/1970-12/31/08.

• If you run a report with an “As of” period of January 2009, the Balance Forward alternate range is January 1970-December 2009.

The start date for any Balance Forward alternate range is January 1, 1970, and cannot be changed.

Steps for Adding Alternate Date Range Columns to Reports

To add a comparison column to a report:

1. On the Edit Columns page of the Report Builder (or Financial Report Builder), review the fields listed in the Report Preview pane and determine the field for which you want to add a comparison.

2. In the Add Fields pane, find the field corresponding to the one in the Report Preview pane, and click it. A second column of the field displays in the Report Preview pane.

3. In the Report Preview pane, select the newly added column, and select a type from the Alternate Date Range Type or Alternate Period Range Type dropdown list.

   In most cases, you will select Relative to report date.

4. Select an Alternate Date Range or Alternate Period Range.

5. Repeat steps 2-5 for each comparison column that you want to add.

6. Click Save.

Note:

If you want to set an absolute alternate range, such as 12/27/2009-1/27/2010 for a date range, or Jan 2010 for a period range, you need to select the Relative to today’s date alternate range type, and select Custom as the alternate range, then either enter dates in the From and To fields, or select a period in the Periods list box.

Steps for Adding Comparison Column Formula Fields to Reports

To add a comparison column formula field to a report:

1. Add two comparison columns to a report, with different Alternate Date Ranges or Alternate Period Ranges. See Steps for Adding Alternate Date Range Columns to Reports for details.

2. In the Add Fields pane, click Add Formula Field.

3. In the Formula Type field, select a formula to use to calculate values in the selected column.

4. In the X and Y fields, select the comparison columns from step 1 to be used as X and Y in the formula equation.

5. If needed, check the Add Grand Total box to add formula column grand totals in subtotal rows.

6. If you want formula grand totals to be sums of formula column values, clear the Apply Formula to Grand Total box.

7. Click Save.

For more information about adding formula fields to reports, see Adding Formula Fields to Reports.

Standard Report Alternate Ranges Are Relative to Report Date

Any standard report column with an alternate date range defined has an alternate range type of Relative to report date, so that whenever a user changes the main report date range or period range, the alternate range for the comparison column automatically changes as well.

The following standard report columns have columns with alternate ranges defined:

Report	Column	Notes
Comparative Balance Sheet	Comparison Amount	—
Comparative Income Statement	Comparative Amount	—
Comparative Sales	Revenue Comparison	This report is available from the related report snapshot.
Comparative Sales (Orders)	Amount Comparison	This report is available from the related report snapshot.
Comparative Sales (Orders Alt. Sales)	Alt. Sales Amount Comparison	This report is available from the related report snapshot.

Important:

Any custom report column with an alternate date or period range that was defined prior to Version 2010 Release 1 retains the alternate range behavior in effect prior to this release, and now has an alternate range type of Relative to today's date. This type of range is defined in relation to the current date when the report is run, and does not change when a user changes the report range. If you want a custom report column with an alternate range to take advantage of the new relative alternate date range functionality, you can edit the customized report. On the Edit Columns page of the Report Builder or Financial Report Builder, select the column, set the Alternate Range Type to Relative to report date, and edit Alternate Range field as necessary.

Segmenting Alternate Period and Date Ranges

You can segment reports using an additional hierarchy level for the current date dimension. This segmentation enables you to customize, for example, the Balance Sheet and Trial Balance reports, to fulfill specific legal requirements. A new Range Segment dropdown list is now available on the Edit Columns page of the Financial Report Builder when you select an Alternate Date Range. When you select either Relative to report date or Relative to today’s date in the Alternate Period Range Type dropdown list, the Alternate Period Range dropdown list appears. When you choose a selector, the Range Segment dropdown list appears.

When you preview or save the report, it is formatted based on your selections in the Alternate Period Range and Range Segment dropdown lists.

Related Topics

• Adding, Removing, or Reordering Report Columns
• Adding Formula Fields to Reports
• Adding Matrix Columns to Reports
• Tips for Adding Matrix Columns to a Report

General Notices