Skip to main content

Report Formatting and Calculation Options

A reference for every summary, formula function, filter operator, and table formatting option available in CX reports.

Written by Andrew Flowers

Overview

This is the reference article for the report builder. It lists every way you can summarize a number, every function you can use in a formula, every filter operator, and every table formatting option. Use it when you know what you want and just need to find the right control.

For a walkthrough of building a report from scratch, see Building a Report.

Summarizing numbers: the Values shelf

Drag a number onto Values and CX summarizes it. Click the chip to change the summary. Seven options are available:

  • SUM adds the values together. This is the default for numbers.

  • AVG returns the average.

  • MIN returns the smallest value.

  • MAX returns the largest value.

  • COUNT counts the rows.

  • COUNT DISTINCT counts unique values, ignoring repeats.

  • MEDIAN returns the middle value.

How grouping works

Adding anything to Values turns the report into a grouped report:

  • Fields on Columns become the groups. You get one row per unique combination of those fields.

  • With nothing on Columns, you get one row summarizing everything.

When to use COUNT DISTINCT

If your report pulls in related data that can match more than once, a plain COUNT can count the same record twice. COUNT DISTINCT is the safe choice when you are counting plans, companies, or any record that could appear more than once in the joined result.

Note: Summaries inherit the format of the field they summarize. A SUM over a currency field shows as currency. A COUNT always shows as a whole number, because a count of dollar amounts is still a count.

Computed columns

A computed column is a column you define with a formula. It runs once for every row, using the other fields in that row.

Creating one

  1. In the builder, click Add computed column.

  2. Enter a Column name, such as "Days Open".

  3. Write your Formula. Type an opening square bracket to search your fields, or pick one from the FIELDS list. Pick functions from the FUNCTIONS list.

  4. Click Validate. CX checks the formula and tells you what is wrong if anything is.

  5. Click Add to put it on the report.

Field references go in square brackets, like [created_at].

The two rules that trip people up

  • No summaries in a formula. SUM, AVG, COUNT, MIN, MAX, and MEDIAN do not work inside a computed column, because the formula runs once per row and there is nothing to total. To summarize a column, drag it onto the Values shelf instead.

  • No related fields in a formula. A formula only sees the dataset's own fields. To show a related field, add it as a column instead.

A report can have computed columns or summarized Values, but not both at the same time.

Functions you can use

Logic: AND, COALESCE, IF, NOT, NULLIF, OR

Math: ABS, CEIL, FLOOR, POWER, ROUND, SQRT

Text: CONCAT, CONTAINS, LEFT, LEN, LOWER, RIGHT, TRIM, UPPER

Dates: DATEADD, DATEDIF, DAY, MONTH, START_OF_DAY, START_OF_WEEK, START_OF_MONTH, START_OF_QUARTER, START_OF_YEAR, TODAY, TO_DATE, YEAR

Working with dates

  • Subtracting one date from another gives whole days: [completed_at] - [created_at]

  • Adding a number to a date shifts it by that many days: [due_at] + 30

  • To choose a different unit, use DATEDIF with a unit in quotes: DATEDIF([created_at], TODAY(), "month"). Supported units are day, week, month, quarter, year, hour, minute, and second. If you leave the unit out, you get days.

  • To group by month or quarter, build a computed column with START_OF_MONTH or START_OF_QUARTER, then put that column on the report.

Useful examples

  • Days a task has been open: DATEDIF([created_at], TODAY(), "day")

  • Total contributions: [employee_contributions] + [employer_contributions]

  • Safe division that avoids errors on zero: [balance] / NULLIF([participants], 0)

  • A status band you can group on: IF([days_open] > 30, "Overdue", "On track")

Filter operators

The operators you see depend on the field type.

Text fields: is equal to, is not equal to, is one of, is not one of, contains, is empty

Number and currency fields: is equal to, is not equal to, is one of, is not one of, greater than, greater or equal, less than, less or equal, between, is empty

Date fields: is equal to, is not equal to, is after, is before, between, relative date, is empty

Choice fields: is equal to, is not equal to, is one of, is not one of, is empty

Yes and no fields: is equal to, is not equal to, is empty. Values are True and False.

Multi-value fields (fields that hold a list, such as tags): is one of, is not one of, has all of, contains, is empty

Relative dates

Choose relative date and enter an Amount and a Unit of Days, Weeks, Months, or Years. The filter re-evaluates every time the report runs, so "the last 30 days" always means the last 30 days.

How many values each operator needs

  • is empty takes no value.

  • between and relative date take exactly two.

  • is one of, is not one of, and has all of take one or more.

  • Everything else takes exactly one.

Important: A filter that does not have all its values yet is ignored, and the report runs as though it is not there. Always finish a filter or remove it.

Table options

Click Table options above the preview. These settings save with the report and apply for everyone who opens it.

  • Default sort sets the column the report sorts on when it opens.

  • Direction sorts ascending or descending.

  • Align numbers puts numeric columns on the left or the right. Right is the default.

  • Date format chooses how dates print: Mmm D, YYYY, MM/DD/YYYY, DD/MM/YYYY, or YYYY-MM-DD.

  • Time chooses No time, 12-hour, or 24-hour.

  • Show times in UTC displays times in UTC instead of your local time.

  • Wrap cell text lets long text wrap onto more than one line instead of being cut off.

How values are formatted

CX formats each cell based on what kind of field it is. You do not set this per column; it comes from the data.

  • Currency shows with a dollar sign. Currency is US dollars.

  • Percent shows with a percent sign, rounded to one decimal place.

  • Whole numbers show with no decimal places.

  • Dates and times follow your Table options settings.

  • Choice fields show the friendly label, not the stored code.

  • Empty values show as a dash, so you can tell blank apart from zero.

  • List values, such as the contacts on a company, show separated by commas in a single cell.

  • Record links let you click a value through to the underlying record in CX.

Limits worth knowing

  • The preview shows up to 500 rows. An export includes up to 500,000 rows. If a report looks like it stops at 500, export it.

  • Use the page size control on the results table to page through 25, 50, 100, 200, or 500 rows at a time.

  • An Excel export splits onto extra sheets past roughly one million rows.

  • You can reach related data through a connected record and one more hop. Very deep relationships are not available.

  • Selecting a very large number of columns at once can fail. Trim the report to the columns you actually need.

Related articles

  • Building a Report

  • Dashboards and Reporting Overview

  • Building and Sharing Dashboards

  • Exporting Data to Excel

Related Articles
Did this answer your question?