Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Matrix Report Builder

The Matrix Report Builder lets you build reports with dimensions on both the rows and columns of the report. Matrix reports often have the Account dimension in the rows and the Time dimension in the columns, such as an Income Statement.

Rules to consider when building matrix reports.

  • Elements of like-kind share the same axis: Once you place an element on the columns axis, you cannot place an element from the same class on the rows axis or in the filter.  For example, if you place Current Assets (from the Accounts element) on the columns axis, you are not able to place Fixed Assets (also from the Accounts element) on the rows axis or use it as a filter. The same rule applies to the row axis.

  • Filters restrict element use. Elements used as filters are not available for use on rows, columns, or sheet tabs. Once an element is placed into the filters section of the report design area, this element cannot be used on the column or row axis.  Elements placed as filters can be used as Parameters, but this changes the function of the filter.

  • Element used as parameters must already be placed on the report in the rows, columns or filters. As a report viewer, you select parameters to filter the data displayed on the report. For example, if time is a parameter, a viewer can select a time period and view the data for it. You can also connect multiple time elements from the report to a single time parameter, which allows the viewer to shift all the time elements based on the parameter.

Create a Matrix Report

Required Permission: Access Report

Create matrix reports by dragging elements from the Elements panel on the left to the report design area on the right. The report design area consists of:

  • Rows axis,
  • Columns axis,
  • Filter area,
  • Parameters area, and
  • Optional spreadsheet tab axis for creating repeating reports.

At least one element must exist on each of the row and columns axis.  Design Elements, found at the bottom of the Elements panel, can be positioned in the report to create segments, make spaces between elements, and create custom headers.

To create a Matrix Report:

  1. Navigate to Reports and choose Matrix Report from the New Reports menu.
  2. Click an element in the Elements panel.
  3. Drag one of the items from the Elements panel into the columns axis in the report area.
  4. In the Elements panel, drag another element into the rows axis in the report area.
  5. Optional elements to include in the columns or rows sections include:
  • Segment, Blank space or Custom header; found at the bottom of the elements pane under the Design Elements header. Drag onto Axis to customize report.
  • Filters section: drag an element to the filter area at the top of the report. Double click the filter to set filter properties.
  • Parameters section: drag an element already placed on the report to the parameters area at the top of the report. Right-click the parameter to set parameter properties.
  1. Once all items are in position, click Save.
  2. In the Save Report dialog, enter a name, choose the report output, set sharing, set a save location, and determine level visibility.
  3. To run the report using the default output, click Run. To run the report in an output format other than the default, click the down arrow to the right of the Run button and select an output format.

Matrix Report Tiers

On the report builder, tiers are nested within elements of the row and column axis. Drag items above or below one another to set new tiers. For example, a time element with a version tier displays a report with a column header for the time and sub-columns below it with the versions. 

Matrix Report Elements

Reporting elements, such as accounts, organization levels, time and versions, are contained in separate lists within the Elements panel. Clicking an element reveals that element's list. To get back to the list of elements, click Back to Elements above the search box. The search box allows searching all elements. Elements listed in the search results can be dragged into the report design area.

Expand or Collapse Elements 

Most elements of a matrix report have sub-elements, or multiple layers of hierarchies. An element with sub-elements is called a "parent" and its sub-elements are called "children". By default a report viewer has the option to expand and collapse elements in rows unless you lock them. A report viewer cannot expand or collapse columns.

You can tell if elements are expanded or collapsed from the report builder screen:

  the element is collapsed in the report, showing the rollup value of its sub-elements.

  the element is expanded, showing the values of the sub-elements. 

You can set how the element's hierarchies appear before you add an element or after you add an element. 

Expand or Collapse Elements Before They're in the Report

To display only the collapsed element, collapse the element in the Elements tab and drag and drop it into the column or row. What you see in the Elements tab is what appears on the report. 

To display the sub-elements, expand the element in the Elements tab. Then drag and drop ONLY the parent element into the column or row.  If you expand an element, and drag in both the parent and it's children, the report displays duplicate data. Notice that the columns in the example below, unlike the rows, are not collapse-able or expandable on the report.


Expand or Collapse Elements Already in the Report

If an element is already in the report, right-click on the element within the report and click Manage Hierarchies. Expand and collapse the elements and sub-elements by clicking the expand arrows. What you see in the window is what appears on the report. The image below shows an unexpanded column:

 

 

 

For more than 500 hierarchies within an element, you can only choose to expand all or collapse all. Keep in mind that for rows,  you can allow viewers to  expand rows as needed through the report settings. 

Allow Viewers to Expand or Collapse Rows 

You can control whether or not viewers can expand and collapse rows:

  1. Click reportProperties.png to open Report Properties. 
  2. In the Display tab, select or deselect Allow rows to be expanded  and click Apply
  3. Save and run the report.  

A mixed expansion state for hierarchies with more than 500 elements can exist.  This indicates that the expansion state is modified and some of the parent tree details are viewable. For mixed expansion states, you can only use expand or collapse all from the manage hierarchy dialog.  You cannot get back to a mixed state hierarchy after using expand or collapse all.   

Filters 

Filters limit data being pulled into the report. For example, if you drag a specific version to the Filters segment, the report displays only the data from that version. When a filter element is also a parameter, the filter limits the data shown in the report when a viewer first runs it while the parameter creates a dropdown menu for viewers to filter the data in the report. 

To use filters in your report:

  1. Click an element from the Element tab that is not being used as a column or row.
  2. Expand the list and drag and drop the sub-element into the Filters segment of the report. If using Versions as the filter, right-click on the segment and click Properties to offset the data display. 


     
  3. Save and run the report.
     

Parameters

Parameters allow people viewing your report to filter it. To add parameters to your report, first verify that the element is already a row, column or filter. 

1 Drag the element from the row, column, or Filters segment and drop it into the Parameters segment.Matching plug icons appear on the the duplicate segments. In the example, the Version element from Filters was dragged into the Parameters segment. 

2 Right-click on the parameter element and select Properties to open the Parameter Settings window.

Drag elements from rows, columns or filters into parameters.

3 Enter a Label name, which displays as the name of the dropdown menu that your viewers see. For example, enter a name like, Select the Version. 

4 Click the Prompt before viewing checkbox if you want viewers to select the parameters before viewing the actual report. 

5 In the Available Choices section, click checkboxes of the options you want available to the viewer. Expand to select "children" of the element. 

6 Click any option to highlight it and then click the Set as initial choice button. The report runs with this parameter as the default. 

Click Apply and save and run the report.

Offset Versions

Anytime a version is used as an element, you can choose to offset its data according to the report's time element. For example, if the time element is 2017 and you offset the version back 12 months, the report displays the data from 2016. 

  1. Right-click the version element in the report and click Properties
  2. If the element is in row or column segment, click the Version Options tab. 
  3. Select the Strata (weeks, months, quarters, and so on). The options available depend on the instance's calendar setup.
  4. Enter the offset amount based on the Strata selected. 
  5. Click the Backward or Forward radio button to shift the data.
  6.  Click Apply.
  7. Run the report and click Information  to display the version offset: 

 

Manage Zeros and Blanks

By default, zeros appear when the value entered on the corresponding sheet cell is zero or the value has not been entered on the corresponding sheet cell yet. 

By default blanks, appear when the data cross sections don't exist. This could happen because the account has a coarser time configuration than the column. For example, the account Intern Expense has monthly data. Because there is no weekly data for the account, it displays a blank for the weekly columns:  

To manage how a report displays zeros:

  1. Run the report and click Modify .
  2. Click Properties .
  3. From the Display tab, you can click the checkbox for Suppress rows if all zeros or blanks, which will then hide rows that have no data or that have all zeros. 
  4. Click the Numbers tab and from the Display Zeros dropdown, choose to Use accounts' format, 0, - , or Blank
  5. Click Apply.
  6. Save and run the report.

The element properties in column and rows overrides the report settings. To manage zeros for each element:

  1. Right-click the element and click Properties
  2. Click the Numbers tab and from the Display Zeros dropdown, choose to Use accounts' format, 0, - , or Blank.   
  3. Click Apply.
  4. Save and run the report.

 

Best Practice: Display zeros as 0 or - to differentiate zero values from invalid data points. 

Considerations for Mixed Time Configurations

When managing the Time element hierarchy in your report, consider the time configurations of the accounts.

  • Expand the Time element to match the accounts' time configuration. For example, if you have only monthly accounts in the report, you may want to include only months or larger in the Time element hierarchy.
  • Expand the hierarchy of the Time element to match the largest time configuration of the accounts. For example, if some accounts are weekly and some are monthly, expand the Time element hierarchy to the month level only. The report displays the rollup value of the weekly accounts for months. If you do include weeks in the Time element, the weekly segments display blanks for the monthly accounts, as shown for Intern Expenses in the example:

  • Accounts as Filters: be mindful of how data displays when accounts with different time configurations are used as filters. For example, if filters include multiple accounts (some weekly and some monthly) and you choose to expand the Time element to a smaller time, the report displays the following data:
    • In the week segments: The total of all the weekly accounts.
    • In the month segments: The total of all the weekly accounts and monthly accounts.

Clear or Delete Reports

To clear a saved report, select all of the elements in the report and delete them. If the report has not yet been saved, navigate away from the report and begin creating a new report.

  • Was this article helpful?