1. SuiteAnalytics
  2. Reports
  3. Report Customization
  4. Adding, Removing, or Reordering Report Columns
  5. Adding Time-Based Comparison Columns to Reports

Adding Time-Based Comparison Columns to Reports

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

For example, a CFO may want an income statement to compare dollar amounts for different fiscal years. She can do this by adding multiple amount columns and selecting an alternate date range for each column. Then, she creates a custom formula field that shows the variance between years.

Time-based comparison columns in the Custom Income Statement.

The overall report date range is set in a date filter on the Filters page of the Report Builder. You can change the date range by editing the date filter on the Filters page, or by choosing another date or period in the footer when you run the report. See Choosing a Date or Period Range for a Report.

You set alternate date and period ranges for columns 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.

If you run a report with an “As of” date of 1/1/09

Alternate Range Types

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

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:

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:

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:

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

Steps for Adding Alternate Date Range Columns to Reports

Use the steps in the following procedure to add alternate date range columns to a report.

To add a comparison column to a report:

  1. On the Edit Columns page of the Report Builder (or Financial Report Builder), look at the fields in the Report Preview pane and decide which one you want to add a comparison for.

  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'll select Relative to report date.

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

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

  6. Click Save.

Note:

You can set an absolute alternate range, such as 12/27/2009-1/27/2010 for a date range or Jan 2010 for a period range. 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

Use the steps in the following procedure to add comparison column formula fields to a report.

To add a comparison column formula field to a report:

  1. Add two comparison columns to a report, each with a different Alternate Date Range or Alternate Period Range. 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 calculate values in the column.

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

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

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

  7. Click Save.

For more information, see Adding Formula Fields to Reports.

Standard Report Alternate Ranges Are Relative to Report Date

Any standard report column with an alternate date range uses the of Relative to report date type, so that when you change the main report date or period range, the alternate range for the comparison column updates automatically.

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 defined before 2010.1 keeps its previous behavior and now has an alternate range type of Relative to today's date. This range uses the current date when the report runs, and doesn't change if you adjust the report range. If you want a custom report column to use the new relative alternate date range functionality, edit the customized report. On the Edit Columns page, select the column, set the Alternate Range Type to Relative to report date, and edit the Alternate Range field as needed.

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 Range Segment dropdown list is 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.

Range Segment period and date ranges.

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

Custom Balance Sheet preview based on the Alternate Period Range and Range Segment selections.

Related Topics

General Notices