Column comparisons in financial reports

Tired of adding duplicate columns to your financial report, and then changing the time period 1-by-1 to show trending over time? Or, do you want to compare the same column of data across locations, departments, or another dimension?

Using the Expand by option on the Columns tab of the Financial Report Writer, you can:

  • Break down data by time period (for example, splitting a quarter into individual columns by month).
  • Create columns that compare data by dimension (such as department, location, or project) for side-by-side reporting.

Expand a single column into multiple columns

The Columns tab of the Financial Report Writer provides a shortcut for expanding a single column into multiple columns. For example, instead of adding separate columns for each month or quarter of the year, you can add a single column for the year and expand it into multiple columns by month or quarter.

To expand a column:

  1. Go to the Columns tab of the Financial Report Writer.
    • Edit the financial report (General Ledger > All > Core > Financial reports > [report] > Edit).
    • Select the Columns tab.
  2. Create a new column, or change an existing one.

    Select at least the Column type and the Reporting period.

  3. Select an Expand by option.

How "group by columns" works

Sometimes you might notice that group by columns doesn't work the way you expect. When expanding, Intacct checks for these options, in order:

  • Align if the Expand by column is a common period interval
  • Are custom headings used? Custom headings can make a column incompatible with grouping
  • Are offset periods used? Offset periods can make a column incompatible with grouping

Types of expansion

Time period

You can quickly create additional columns of data that compare amounts by time period. For example, to display actual amounts for 3 months of the previous quarter, choose Prior Quarter as your reporting period and then choose Expand months from the Expand by dropdown menu.

To compare non-consecutive time periods, you can use reporting period offsets.

Dimension

You can split 1 column into multiple columns that compares the data by dimension.

Many dimensions can have parent-child relationships that are hierarchical in nature. Tor example, a US location might have locations in different regions, with additional locations in different states. For this reason, the Show dropdown menu lets you choose how you want to handle multi-level dimensions.

Dimension structure or account group

For certain reports, you also have the option to expand columns by account group or dimension structure. For more information about how these options work, select the help (question mark) icon to the right of the Expand by dropdown menu.

The name of the expanded account group or dimension structure appears centered above the columns when the report is displayed in HTML, but not in the Print (PDF) version.

Options to "Show" 1 level or all levels

When you expand by an account group, dimension, or dimension structure, you use the Show dropdown menu to choose how much detail you want to show and how total amounts will be treated.

Options for dimensions

The following Show options appear when you expand by a dimension, such as locations or customers.

Dimensions show options
Option Description

One level down, with rollup

Displays columns for the selected dimension and one level down in the hierarchy, plus a total column.

For example, suppose that you have a US location with sublocations in California and New York. In addition, the California location has sublocations in Los Angeles and San Jose. In this case, the report:

  • Includes columns for California, New York, US, and US (All).
  • Doesn't include columns for Los Angeles and San Jose. However, amounts from Los Angeles and San Jose are included in the summary amount for California.

One level down, with summary balance

Displays columns for the selected dimension and one level down in the hierarchy. Instead of displaying an additional total column, the amount for each dimension is a summary balance.

For example, suppose that you have a US location with sublocations in California and New York. In addition, the California location has sublocations in Los Angeles and San Jose. In this case, the report:

  • Includes columns for California, New York, and US, without a total column.
  • Displays amounts from sublocations in the summary amounts at each level of the hierarchy.

All levels, with rollup

Displays columns for all levels. All amounts roll up into total columns.

For example, suppose that you have a US location with sublocations in California and New York. In addition, the California location has sublocations in Los Angeles and San Jose. In this case, the report:

  • Includes columns for San Jose, Los Angeles, California, California (All), New York, US, and US (all).
  • Displays individual amounts at each level, but also includes total columns that rollup amounts.

All levels, with individual balance

Displays columns for all levels, with each level showing only the individual amounts assigned to that level, plus a single total column.

For example, suppose that you have a US location with sublocations in California and New York. In addition, the California location has sublocations in Los Angeles and San Jose. In this case, the report:

  • Includes columns for San Jose, Los Angeles, California, New York, US, and US (all).
  • Displays individual amounts at each level, with only a single total column.

All levels, with summary balance

Displays columns for all levels. Instead of displaying additional total columns at each level, all amounts are summary balances.

For example, suppose that you have a US location with sublocations in California and New York. In addition, the California location has sublocations in Los Angeles and San Jose. In this case, the report:

  • Includes columns for San Jose, Los Angeles, California, New York, US, and US (All).
  • Displays a summary balance at each level that includes amounts from sublocations.

Options for account groups and dimension structures

The following Show options appear when you expand by an account group (such as Revenue) or a dimension structure (such as "Customers by type").

Account groups and dimensions show options
Option Description

One level down

Displays columns for one level down in the hierarchy, plus a total column.

For example:

  • A "Customers by type" structure that contains 2 substructures for "Direct Customers" and "Channel Customers" would show columns for Direct Customers, Channel Customers, plus a total column.
  • A Revenue account group that contains subgroups for Services revenue and Subscriptions revenue would show columns for Services, Subscriptions, plus a total column for Revenue.

All level

Displays columns for all levels, with each level showing only the individual amounts assigned to that level, plus a total column.

For example:
  • A multi-level Location structure would show columns for each level in the hierarchy, with columns for totaling.
  • A multi-level Assets account group would show all the subgroups, with columns for totaling.

Show individual members in a dimension structure

You can expand a flat dimension structure to show individual members, such as customers, suppliers, projects, and locations. Instead of expanding by dimension structure, select Expand by and then choose the name of the dimension. For example, to show the customers in a Customer dimension structure, set the Expand by option to expand by Customers.