Sage Intacct Reports: Dimension Groups and Dimension Structures

If you’re a Sage Intacct user, you’re hopefully aware Sage Intacct’s most powerful feature is its multidimensional capabilities.

Did you know you can create an infinite amount of dimension groupings for reporting purposes? Each of Sage Intacct’s core dimensions allows for custom groupings to provide the strategic reporting you’ve always wished for and permits report readers to choose to show only relevant data. No longer do you need to dump data into Excel for grouping or consolidating; Sage Intacct can handle multiple combinations of grouping data.

Let’s start by defining the two major terms for grouping data for reporting purposes:

• Dimension group — A group of members in a dimension, such as customer, vendor, item, employee, department, location/entity, and class. Dimension groups can be used in any report or dashboard filter drop-down menu in Intacct.
• Dimension structure — A group of dimension groups or a group of dimension structures. Dimension structures are only available for when creating Financial Statement reports in the General Ledger module.

Sage Intacct dimension groups

To set up dimension groups, just follow these two easy steps:

Step 1: Navigation

Reports > Open Setup > Dimension Groups > Location (example) > Add

Step 2: Add new group and edit the filters

• Assign the dimension group ID — I named my location group “South” (anything you would like to identify your group by, just note that it cannot be changed once created) to include southern location dimensions.
• Name your group — I named this group “South Region” (can be changed).
• Group type — Choose a group type. (Either “Specific members” or “All members.”)
  • Specific members: This option allows you to add individual members. This is a great type for novice users. However, if more locations are added in the future, the location group will need to be manually updated, creating more maintenance for reporting. To set it up, select the members from the dropdown list under the members section and add each member.

  

  • All members: This option allows you to filter for group members based on a field. The benefit of this group type is if more locations are added in the future, the location group will be updated automatically for reporting purposes. To set it up, change the filter by choosing the field you would like to filter (Location ID) and the “Operator” to “greater than” and “less than.” The below example accomplishes the same dimension group as the specific member group noted above, but won’t need to be updated as much in the future. (For example, if location 250-Arizona is added in the future, it will be included in this account group because it’s between the ID values of 99 and 401).

  

Tip: How do you include exceptions in your group when they don’t meet the basic filter condition?

The filters allow for conditional expressions. The default is AND — or in other words — filter for location IDs greater than 99 AND less than 401. But what if I wanted to add location 500-Colorado that falls outside of my expression? At the bottom of the location group screen, you can choose between filter conditions All (AND), Any (OR), or Expression (custom expression). To add 500-Colorado, I would write the following expression:

The expression I’ve written above relates to the filter line items. It essentially says, include “Location IDs greater than 99 AND location IDs less than 401 OR if the ID equals 500.” Now when I use this location group in a report, it will include any location ID between 99 and 401, as well as location ID 500.

Another neat feature with dimension groups is if you use a custom field in your Sage Intacct instance, the custom fields become a “field” option in the filter section, allowing you to create groups based on custom fields.

Tip: To understand how to use report groups when filtering reports/dashboards, see our blog post on Filtering Reports and Dashboards for Current User with Intacct

Now that you’ve created a dimension group, you should see this grouping available on any report that exposes dimension filter drop-down option.

Sage Intacct dimension structures

Let’s now move to understanding more about dimensions structures. Dimension structures are very similar to dimension groups, but as mentioned above, they’re not technically the same. Let’s learn more by creating dimension structures by following these three steps:

Step 1: Navigation

Reports > Open Setup > Dimension Structures > Location (example) > Add

Helpful tip: Remember dimension structures are a dimensional group or groups of dimensional groups. There’s an easy way to create a dimension structure when setting up a dimension group — just check the dimension structure box when creating any dimension group.

Step 2: Add location group or groups to dimension structure

• Assign name — I named my location dimension structure “North/South Regions” (anything you would like to identify your group by, just note that it cannot be changed once created) to include both my north and south location dimensions. This name will show up as the header if used on columns.
• Display fields — These fields are adjustable and will dictate how the rows appear when used in a financial report with a dimension structure type (non-traditional report structure).
• Structure type — Select from either a list current location group(s) (Location Groups) or a list of dimension structures (Hierarchy of Location structures). I chose location groups and included a location group of my north locations and a group of my south locations.
• Roll up child amount — If your dimension hierarchy contains children members and you would like them included in your dimension structure, check the box. This option is only available if your structure type is set to Location Groups. I chose to not show my child locations.

Step 3: Add dimension structure to rows or columns on financial report

Dimension structures can be used in either the rows or columns tab in the financial report wizard. The traditional and more common approach is to use dimension structures in columns. However, if you wish to use in rows, your report structure must be set up as Dimensions.

When creating or editing a financial report, on the Columns tab you will have an option for Dimension structure. This is where you can select the dimension structure you created in step 2 above.

Report using North/South Regions Dimension Structure:

As you can see, the dimension structure has combined both location groups for the North and South locations into one column. You can also expand on the dimension structure if you wish to view its members in detail.

Tip: To understand more about expanding columns, see our blog post on Intacct Financial Reports: Expanding Columns, Report Groups, and Scheduling

You should now have a better understanding on how to filter and group your dimensions in Sage Intacct for reporting purposes. Excel is a great program, but having Intacct do the work for you should allow for more time to focus on driving your mission and not “massaging” data.