Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Display Data as Custom in a Report

Explains with examples how to add a custom Display As element to customize a formula used in a report.

As a matrix report developer, use the Display As Custom element to customize the data in a report.

For 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.

Before You Begin

How You Get There

Navigation Icon5.png Open a report and select Modify Report

Display Values as a Percentage of the Total

Watch the video: 58s

Salaries By Level - Display As Custom in a Report
 

For 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

For 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. For 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

For 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 revenues and costs.

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 ReveseSign attribute value No.
    5. Select the Cost per Employee account and assign the ReveseSign 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

For 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. For example, select April to see the rollup for Feb - April in the columns. See Report Parameters for more.

Display Different Time Periods for Different Versions

For 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 (for example, 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.

Edit 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.
  3. In the Formula field, enter the following if statements. Use the pound symbol (#) to add text that describes each statement. 

    higher-ed-example.png
  4. Select OK to close the Formula Assistant.
  5. Save and run report.
  • Was this article helpful?