How to Use Calculated Fields

How to Use Calculated Fields

In this article, we look at how you can use Calculated Fields to generate better reports for your storefront. 

What are Calculated Fields

Much like formulas in Excel, calculated fields take data from one area of Report Toaster, run a custom calculation, and output the data somewhere else. 



For example, Suppose I wanted something to take whatever was in column B of a report and multiply it by 15%.
Maybe it's for tax purposes, perhaps, or to calculate a commission or a tip?

To do this, in Excel I might make a formula like so:  


In other words - take what is in column B, multiply it by 0.15 (15%) and display it in column C.

Of course, Excel can do more than simple addition, subtraction, and other basic math.
E.g. You can use vLookup (and other lookup formulas) to say "If column X contains value Y, put it in field Z' 
This can be very useful to lookup names, dates, times, or whatever you want really.

Calculated Fields can do the exact same thing.

Using a Calculated Field, Report Toaster can:
1.) Lookup the data you have stores in one field (e.g. the total on an order, a person's state, a certain product, notes attached on an order)
2.) Run a certain function (e.g. if X is here, do Y) and
3.) Put the output in it a specified custom column. 

Let's see how this works in practice. 

Setting up Calculated Fields

Due to the complexities involved in creating calculated fields, setting them up is currently only possible on the back-end.

Therefore, to get a calculated field setup for a report, all you need to do is:
1.) Sign up to a paid subscription for your store
2.) Contact us at support@cloudlab.com and tell us which fields you are looking to create, what data you want to use, and which report(s) you're looking to put the fields in.
3.) We'll take care of the rest. 

Note - Calculated Fields require a paid subscription to Report Toaster. You can sign up at any time by visiting https://reporttoaster.cloudlab.com/subscribe

Example 1 - Simple Math(s) 

For instance - suppose you wanted to create a simple 30% commission based on the Total Sales of your recent Orders? 

Much like our example above then, we can see how such a calculation looks in Report Toaster: 



Here we can see how a calculated field can be used to create an affiliate fee column by working out 30% of the Gross Sales.  

Note - The columns being used as inputs for the formulas don't have to be included as columns in the report for the calculated fields to work correctly.
So in this case, I could remove the Gross Sales column from the report and the Affiliate Fee 30% would still display correctly. 

This report would be very useful for displaying what you might owe to an affiliate without working out the calculations manually every time. 

Example 2 - Translating Staff/Location/Device ID's 

For instance - translating fields that are a string of numbers of an ID of some description into something more useable.

This can be quite useful with say, the Staff ID - where Shopify assigns an ID number to each staff member that store owner's are unlikely to know off-hand. 

Here's something similar we created for our demo store: 



As you can see here, the calculated field takes the Staff ID, maps it to a specific Staff Name, and then displays in the Staff Name column. 

Example 3 - Taxes

For instance - you need very specific fields to fill out a tax return. 

Certain jurisdictions might require that you include shipping as part of your taxable total. This is easy to do using a calculated field: 



Similarly, Canadian customers might need to split the taxes column into GST and PST respectively: 



Much like our simple math section above, Report Toaster can use calculated fields to take data from any field, run calculations, and display the outputs separately into their own columns.

Other Examples 

We've come up with several examples of how calculated fields can be used, but here's a few more possibilities. 
  1. Taking custom information out of the Note Attributes and Line Item Properties and displaying it in its own columns. (e.g. Monograms, additional variant product options, delivery details, and so on. Especially great for Variant Product Options users!). See here for more info.
  2. Categorizing data based on tags (e.g. taking date tags and calculating the day of the week for a daily fulfillment report. Especially great for Zapiet and Order Tagger users!)
  3. Working out and displaying fixed shipping rates or costs. (e.g. mark anything with UPS in the Delivery Method as a $7.00 shipping cost) 
  4. Displaying the age of an order in days. (e.g. a running day counter that starts when the order is submitted and ends when it's been fulfilled).
The possibilities are truly limitless. We can take just about any data field in the app, run some calculations, and put it all together to display in a custom column/filter accordingly. 
 

Where Next?

If you would like us to create some custom calculated fields for your account, you can email us at support@cloudlab.com.
All you need is a paid subscription for your store, then let us know what sorts of fields you're looking for and what data to incorporate and we'll take care of the rest. 



    • Related Articles

    • Reporting on Line Item Properties and Note Attributes

      What are Line Item Properties & Note Attributes In Shopify, there are two particular fields that are contain a additional information relating to an order. These are: 1.) Note Attributes - namely, the custom information you typically have inserted as ...
    • Taxes - Frequently Asked Questions

      We get a lot of tax-related questions on our support chat, so in the interest of sharing that knowledge with the wider Report Toaster community, we thought it was time we compiled an FAQ for all questions pertaining to taxes.  Note - A good place to ...
    • How to Set Up Tax Reports

      With the latest update to Report Toaster, you may have noticed our new tax reports over in the left-hand menu:  These reports are designed to help you narrow down your Shopify sales by state, country or city (based on either billing or shipping ...
    • What's New? - June 2022

      Summer is here, and with it comes a scorching hot new feature in Report Toaster. Calculated Fields are here!  ("Yes, finally!" I hear some of you saying. Quite literally in fact - a few of you even emailed to tell us!) Not only has this been one of ...
    • What's New? - May 2022

      May is finally here, and with another month comes another exciting development for Report Toaster.  Tax Reports  Helping a few of our merchants compile their data as they struggled through the tax season gave us an idea - we really needed our own ...

     Suggestion Box

    Didn't find what you were looking for in our knowledge base? Click here to suggest an article, and we'll try to put something together for you.