Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Create Matrix Reports

Explains the basics of how to create matrix reports using the report builder.

A matrix report has dimensions on both the rows and columns. A typical income statement report is the most common matrix report with accounts down the rows and time across the columns. Create matrix reports by dragging elements from the Elements panel 1 to the canvas 2

Create matrix report

At least one element must exist on each of the row and columns axes.  

Before You Begin

  • New to reports, see Web Reporting for a list of articles.
  • Required Permission: Access Reports.
  • Consider the rules.

How You Get There

Compass.png From the nav menu, click Reports. Then from the tool bar, click New Report and select Matrix

Create a Basic Matrix Report

Watch the video: 47s

Create a Basic Matrix Report
 
  1. From the Elements panel, expand Accounts and drag one or more accounts into the row axis.
  2. From the Elements panel, expand Time and drag one or more time elements into the column axis.
  3. Click Run Report runReport.png.
  4. If you like what you see, click Save reportViewerSave.png and enter a Name for the report.
  5. Choose your output. HTML saves the report to Adaptive Insights where you can edit the report later. PDF saves the report as a hard copy that you can download, email, and share, but can't edit. 

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.

Elements

Report elements are accounts, levels, time, versions and so on. They are stored as separate lists in the Elements panel. Expand element lists to reveal the items you can add to the report.

To return to the main list of elements, click Back to Elements or the list drop-down:

Back to element list

Enter keywords to find elements and drag them directly from the search results.

Create Tiers

On the canvas you can nest elements within elements of the rows and columns. 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. 

Tiered elements in matrix reports

The example above reports on the assets account for each level in Company A.

Expand or Collapse Elements in Columns and Rows

Most elements have 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 once its added to the canvas:

  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 change this before or after you add an element. 

Collapse Elements

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. 

Collapsed Column in matrix reports

If an element is already in the report, right-click the element within the report and click Manage Hierarchies. Select Always Collapsed and then apply. What you see in the hierarchy window is what appears on the report. 

 manage a hierarchy by always expanding, always collapsing, or customizing the hierarchy tree

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. 

Expand Elements

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.

Expanded Columns in matrix report

If an element is already in the report, right-click the element within the report and click Manage Hierarchies. Select Always Expanded and then apply. What you see in the hierarchy window is what appears on the report. 

Select How to Display an Element Hierarchy

You can select an element hierarchy to show as always expanded or always collapsed in the report viewer. The default Custom option allows you to customize the the element hierarchy tree display. 

  1. In the report canvas, right-click the element and select Manage Hierarchy.
  2. Select how to display the element hierarchy:
    • Always expanded. Shows the parent with all its children exposed. For example, the parent Assets account shows with all Current Assets and Fixed Assets indented under it. If the hierarchy changes, any new parents are automatically expanded. 
    • Always collapsed. Shows only the parent without its children. For example, only the parent Assets account shows. No child accounts show under it. If the hierarchy changes, any new parents are automatically collapsed. 
    • Custom. Shows the element hierarchy tree as you define the hierarchy. For example, you can expand some portions of the hierarchy and collapse other parts.

Allow Viewers to Expand or 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.  

Filters 

Filters limit data being pulled into the report. For example, if you drag a specific level such as Engineering (Rollup) to the Filters segment, the report displays only the data for that level. 

  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. 


     
  3. Save and run the report.

Parameters

 Parameters allow you to select options and enable specific elements so you can re-run the report to show a different set of data. 

Parameters allow people viewing your report to choose different filters. 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. 

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 drop-down 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

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. 

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. 

Matrix Report Rules 

  • Elements of like-kind must share the same axis: Once you place an element on the columns axis, you cannot place the same element on the rows 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.

  • Once an element is placed in the filters section of the report, it isn't available for rows, columns, or the sheet tabs. Elements placed as filters are available for parameters.

  • Elements used as parameters must already be placed on the report in the rows, columns or filters. 

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.

Style and Formatting

To enhance the readability of a matrix report, you can specify formatting rules based on report values. See Conditional Formatting for Reports. You can use conditional formatting with the following types of formatting available to all types of web reports:

  • Was this article helpful?