Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Display Data as Custom in Reports

Display Data as Custom in Reports

As a matrix report developer, use the Display As Custom element to customize the data in a report.
Example:
  • Display values as a percentage of the total for a roll-up account.
  • Display values as a percentage of a different denominator.
  • Reverse the sign for a variance amount such as from positive to negative.
The article walks you through many use cases for using the Display As Custom element.

Prerequisites

Navigation

Navigation Icon5.png From the Reports Overview page, open a report and select
Edit
.

Display Values as a Percentage of the Total

Watch the video:
 58s

Example: Your Salaries by Level report currently displays Q1 And Q2 salaries for all US levels and the total for US. You want to understand which department is making up the most of the payroll in the US rollup.
salaries_by_level_after.png
Do the following:
  1. Drag the
    Display as Value
     element to the columns.
  2. Drag the
    Display as Custom
    element to the columns.
  3. Right-click the
    Display as Custom
     element and select
    Formula Assistant
    .
  4. In the
    Formula
    field, enter the formula:
    (ACCT.this/ACCT.this[level=United States(+)])*100
    . Select
    OK
    to close the Formula Assistant.
  5. Right-click the
    Display as Custom
     element and select
    Properties
    . Enter the label "Percent of Total. Select
    Apply
    .
  6. Save and run report. The report now displays a new Percent of Total column. In this column, you can see that Sales North is the highest percent of US from a payroll perspective. The salaries for all US levels continue to display under a new Value column.

Display Different Denominators for Different Rows

Example: Your report uses the Display As Percent element to show both revenue and cost line items as a percent of revenue. The report currently uses only one denominator (Revenue) for the whole report. You want to display the cost line items as a percent of cost and the revenue line items as a percent of revenue.
display as percentage_updated.png
Do the following:
  1. Delete the Display As % of Account element. Example: Delete "As % of Revenue".
  2. Drag the
    Display As Custom
     element in the placement for the Display As % of Account element.
  3. Build an if statement using GL account codes. The account codes differentiate the accounts so the if statement can identify the different rows for their denominators:
    1. Navigate to
      Modeling > Model Management > Accounts > General Ledger Accounts
      .
    2. Search for
      Custom Accounts
      . Under P&L Accounts, note that the account codes for all the revenue accounts have "Rev". None of the cost accounts have "Rev" in their codes.
    3. Navigate back to
      Reports
      .
    4. Right-click the
      Display As Custom
       element and select
      Formula Assistant
      .
    5. In the
      Formula
      field, enter an if statement that checks if the account code for a row contains "rev". If yes, then it divides the account by the Revenue denominator and multiplies by 100. If not, then it divides the account by the Cost denominator and multiplies by 100.
      if(search(this.account.code,"rev")>0, (ACCT.this/ACCT.PL_Revenue)*100, (ACCT.this/ACCT.PL_COS)*100)
  4. Select
    OK
    to close the formula assistant.
  5. Right-click the
    Display as Custom
     element and select
    Properties
    . Enter the label "Percent of Total. Select
    Apply
    .
  6. Save and run the report. The report now displays the revenue line items as percentage of Revenue and the cost line items as a percentage of Cost of Sales.

Display a Variance Differently

Example: Your Per Employee Metrics report displays the Revenue and Cost per Employee and the variance over time. Currently the variance shows as positive (increased) for both the accounts. Although an increased variance for revenue is favorable, you want to see an increased variance for cost as unfavorable or negative.
display_variance_differently.png
The Reverse Sign option available with the Difference element only applies to GL accounts. You cannot use this option with metric accounts such as cost of sales or gross margin percentage.
Do the following:
  1. Add a new column segment and drag over the Difference element (Variance) to the new segment.
  2. Drag the
    Display as Custom
     element to the new segment (below the Difference element) to display the variance differently.
  3. Drag the
    Display As Value
     element to the first segment to continue to display the quarterly amounts as values.
  4. Build an if statement using metric account attributes:
    1. Navigate to
      Modeling > Model Management > Accounts > Account Attributes
      .
    2. Create a new ReverseSign account attribute with values Yes and No. Do not include any spaces in the account attribute name for use in a formula.
    3. Navigate to
      Accounts > Metric Accounts
      . Expand Employee Metrics and then Per Employee Metrics.
    4. Select the
      Revenue per Employee
       account and assign the ReverseSign attribute value No.
    5. Select the
      Cost per Employee
       account and assign the ReverseSign attribute value Yes.
  5. Back in Reports, right-click the Display As Custom element and select
    Formula Assistant
    .
  6. In the
    Formula
    field, enter an if statement that checks if the account has the ReverseSign attribute value set to Yes. If yes, then it takes the variance and multiplies by
    -1
    . Else, it just gives the variance for the account:
    if(this.account.ReverseSign.name="yes", (ACCT.this*-1), ACCT.this)
  7. Close the Formula Assistant and run the report.

Display Trailing Three Months

Example: Your Expense Report currently displays actuals and budget amounts for a specific month, say March. You want to look at the values for the last three months (Jan - Mar) aggregated in a single column.
trailing_three_months.png
Do the following:
  1. Drag the
    Display As Custom
     element to the columns below the versions (Actuals and Working Budget).
  2. Right-click the
    Display As Custom
     element and select
    Formula Assistant
    .
  3. In the
    Formula
    field, enter the formula:
    ACCT.this[time=this-2:this]
    .
  4. Select
    OK
    to close the formula assistant.
  5. Right-click the
    Display as Custom
     element and select
    Properties
    . Enter the label "Trailing Three Months. Select
    Apply
    .
  6. Save and run report. You can now see the rollup for Jan to Mar in the versions columns. Since time is parameterized in this report, you can select a different month from the Time drop-down. Example: Select April to see the rollup for Feb - April in the columns.

Display Different Time Periods for Different Versions

Example: You want to create a matrix report that displays the following data for operating expenses:
  • Working Budget for the whole year
  • Actuals for year to date (beginning of the year to now)
  • Forecast for the rest of the year (all months after this month till the end of the year)
  • Remaining Budget (variance of the three versions)
higher-ed-example_2.png
Do the following:
Create the Report
  1. Drag Operating Expenses to the rows.
  2. Drag a time element to the columns such as Feb-2017). Parameterize the time element.
  3. Drag the versions Working Budget, Actuals, and Forecast to the columns below the time element.
  4. Drag a custom calculation element to the columns after the versions.
  5. Right-click the custom calculation element and select
    Formula Assistant
    .
  6. In the Formula field, enter the formula:
    RPT.Working_Budget-RPT.Actuals-RPT.Forecast
    .
  7. Right-click the custom calculation element and select
    Properties
    . Change the label to "Remaining Budget".
  8. Save and run the report.
Change the Report
  1. Drag the
    Display as Custom
     element to the columns above the versions and time.
  2. Right-click the
    Display As Custom
     element and select
    Formula Assistant
    .
    higher-ed-example.png
  3. In the
    Formula
    field, enter the following if statements. Use the pound symbol (#) to add text that describes each statement.
  4. Select
    OK
    to close the Formula Assistant.
  5. Save and run report.