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 firstname.lastname@example.org 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.
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.
We've come up with several examples of how calculated fields can be used, but here's a few more possibilities.
- 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.
- 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!)
- 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)
- 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.
If you would like us to create some custom calculated fields for your account, you can email us at email@example.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.