Calculation Columns
Updated over a week ago

Apricot's Report Builder has columns that allow you to concatenate columns, add mathematical calculations and pull summaries from one section of a report to another.

Unlike traditional report columns, which must be pulled from a list of fields that have been added to forms, these special columns exist only in the Report Builder and can be added to any report.

Report Builder has two kinds of special columns which can be added to any report section: calculation columns and summary columns.

Calculation Columns

Calculation columns can perform simple mathematical functions with data pulled from another column - like addition, subtraction, multiplication or division between columns with numeric values. They can also be used to combine two columns with text values or establish the difference between two dates or two times.

Multiple calculation columns can be added to a section. Results from one calculation column can be used in subsequent calculation columns.

Summary Columns

Summary columns can pull section counts or column counts from one section of a report into another.

Summary columns cannot be used to display data that is in the same section of the report as the summary column. Best practice when using summary columns is to keep them in a separate report section of their own.

Report filters cannot be used with summary columns.

Adding a Calculation Column

  1. In the upper right hand corner of the Report Builder, expand the gray arrow next to "Field Choices." 

  2. Expand the black arrow next to "Special Columns."

  3. Expand the black arrow next to "Calculation Columns." Select a column name and drag it into your report section.

Number Calc Column

The number calc column can perform simple mathematical calculations to show the relationship between multiple columns of numeric data, or the relationship between a column of data and a static number that you can designate.

  1. To access the properties, select the gear box in the upper right hand corner of the calculation column.

  2. Select a calculation type. Each number calc column can only perform one function; if you need more complex mathematical functions, you can add several number calc columns and have each column perform a separate step.

  3. Choose your column of data by expanding the black arrow. The only columns that will appear here are columns that contain numeric data or columns that have been set to display a numeric value. [Static Value] is an additional option. When selected, [static value] allows you to type in your own numeric value. It will remain a constant value when you run your report.

  4. Normal data styles and summary options are available for calculation columns.

  5. When you have finished configuring your column, select "Apply."

Date/Time Calc Column

The date/time calc column can display the difference between two dates or times, or the days before or days after a date.

  1. To access the properties, select the gear box in the upper right hand corner of the calculation column.

  2. Select a calculation type. "Days between" requires two columns with date data. "Time between" requires two columns with time data. "Days before" and "days after" use a date column and a static value.

  3. Choose your column of data by expanding the black arrow. If you have selected a calculation type that includes "days," all the columns with date values will be available here. If you have selected a calculation type that includes "time," all the columns with time values will be available here. [Static Value] is an additional option. When selected, [static value] allows you to type in your own numeric value. It will remain a constant value when you run your report.

  4. Normal data styles and summary options are available for calculation columns.

  5. When you have finished configuring your column, select "Apply."

Note: Date/Time Calculations truncate values. For example, if you're running a calculation to see how many years are between the dates 8/31/2021 and 8/30/2023, the calculation column will display a "1," as the second year has not technically occurred yet.

Text Calc Column

The text calc column combines the values of multiple text columns. It can also combine a text column with a static value.

  1. To access the properties, select the gear box in the upper right hand corner of the calculation column.

  2. Select the + sign to add more columns to the text calc.

  3. Choose your column of data by expanding the black arrow. Any kind of data can be combined in a text calc field. [Static Value] is an additional option. When selected, [static value] allows you to type in your own value. It will remain a constant value when you run your report. In the example below, we used the static field to hold the comma between the last name and the first name.

  4. Normal data styles and summary options are available for calculation columns.

  5. When you have finished configuring your column, select "Apply."

Note: Static Values have a character limit of 35

Adding a Summary Column

  1. In the upper right hand corner of the Report Builder, expand the gray arrow next to "Field Choices."

  2. Expand the black arrow next to "Special Columns," and

  3. Expand the black arrow next to "Summary Columns." Select a column name and drag it into your report section.

Section Summary Columns

The section summary column takes the value from any column in a report that has been set to display a summary value and brings it into its own column. In the example above, the "age column" from the previous report section has been set to show a summary value of the "average age" of the clients. This average age has been brought down to the next section by the section summary column. This can be useful to highlight a particular data point and/or to allow us to use that average age in more complex mathematical functions.

  1. To access the properties, select the gear box in the upper right hand corner of the summary column.

  2. Choose a section and a column by expanding the black arrows. Only columns that have been configured to show a summary value and the section they are in will appear here.

  3. Limited data styles and summary options are available for summary columns.

  4. When you have finished configuring your column, select "Apply."

Section Count Column

The section count column takes the value of the "total rows" from one section and brings it into another section. This can be useful to highlight a particular data point and/or to allow us to use that count in more complex mathematical functions.

  1. To access the properties, select the gear box in the upper right-hand corner of the summary column.

  2. Choose the section of data by expanding the black arrow. All sections in the report will appear here.

  3. Limited data styles and summary options are available for calculation columns.

  4. When you have finished configuring your column, select "Apply."

Did this answer your question?