Reports: custom formula columns

Overview

Spiro's reporting environment has a useful feature called "Formula Columns." This allows you to create a new column in the table with entirely new data points, using standard Excel-like equations. Note that this new column will only exist in the table you create it in; it will not show up in the front end of Spiro.

A common use-case is using a formula column to calculate a weighted pipeline value for an opportunity. Often, a company will assign a win probability to each sales stage, and then want to see a weighted value to give them a more realistic pipeline amount.

How to Add a Formula Column

First, find the table you want to add the formula column to. Staying consistent with the weighted pipeline example, we're going to use the Opportunities table. Click on Add → Formula Column.

You'll see an editor appear, for you to build the new formula column. You can see all the functions that are available for you to use, as well as all the columns in the table you are able to reference.

From here, it's just a matter of typing or selecting the columns and functions you want to use in the formula. As you can see in the screenshot below, I've built a formula using a nested IF statement that multiplies the opportunity amount by some percent, depending on the sales stage. If you have any experience with calculations in Excel, this will be very straightforward.

When you hit save, Spiro will give you an error message if the formula is incorrect. Once you've saved successfully, scroll to the end of the table. You should see you column appear with all the new calculated values. And now you're able to go use this new column in other tables.