Skip to main content
Windward

Calculate running totals with ADDTOTAL/GETTOTAL_function

Overview

It is possible to get an updated “running total” of all products on order each time you run the report by using the ADDTOTAL/GETTOTAL functions.

 

Let’s say you are using a forEach tag to create a report of all products on order with a total dollar amount at the end of the report. The forEach tag retrieves this data from your data source at the time it is run, and you are running the report weekly. Each week, the data for products on order is going to change.

Example

We'll be walking through how to calculate a running total of the extended price totals, and that running total will then appear as the Product Total amount.

 

The quick overview:

In our table above, the last column has an Out tag called [ExtendedPrice]. This tag is multiplying the [UnitsOnOrder] with the [UnitPrice], and both items are being dynamically generated from the data source.

To get our Product Total in dollars, we need to get a running total of the Extended Price column as it expands in the forEach loop at the time the report is generated.

 

 

The sample below is connected to our public sample SQL database. Feel free to download the sample and follow along.

WindwardSampleAddtotalGettotal.PNG

Step 1: Make your Out Tag into a variable to use in another tag

We need to give our [ExtendedPrice] Out tag a variable name so that we can reference it in our ADDTOTAL function. In this example, we use price. You can set the variable name for the tag in the Properties pane of the Tag Editor in the var field.

(NOTE: Remember the syntax for calling a variable in AutoTag is ${name}, or in this case, ${price}).

RunningTotal1.png

Step 2: Create an Out tag that will tally your running total

Next we'll create an Out Tag that will tally our total, but be hidden. ADDTOTAL does not 'print' any data, so if you use it in an Out tag, the tag will be automatically hidden in the Output. 

 

Insert a new Out tag next to the [ExtendedPrice] Out tag and give it a nickname of [RunningTotal] in the properties pane. 

Now click the Equation button.

 

RunningTotal2.png

Step 3: Set up your ADDTOTAL Function, which will calculate your running total

In the Insert Function window, choose ADDTOTAL (in v13, choose Custom first) from the Select a Category pull-down menu.

RunningTotal3.png

Select the ADDTOTAL function from the list and click OK.

(v13 and v15 shown)

RunningTotal4.png

 

In the Function Arguments window, you have two values that need to be configured.

  • num = the number you are going to base your running total off of. In our sample, this is the Out tag variable we set in Step 1. Select it from the drop down.
  • key = the name by which you will refer to this running total in your GETTOTAL function (in Step 4)

RunningTotal5.png

To set the num value, use the pull-down menu next to the num field and select the price parameter from Step 1.

RunningTotal6.png

Create a value for the key by typing in the key field. In this sample we will use ‘total’ for the key. When you are finished, your Function Arguments window will look like this:

RunningTotal7.png

Click OK and save your [RunningTotal] tag.

Step 4: Create an Out tag that will tally and print your Final Total

Insert a new Out tag under the [ExtendedPrice][RunningTotal] tags and give it a nickname of [ProductTotal]. We are going to use this Out tag to hold our GETTOTAL function.

(NOTE: This is set up outside of your forEach tags because it does not need to loop through your data anymore -- it's the final sum.)

 

Double click your newly inserted Out tag to launch the Tag Editor, and click the Equation button.

RunningTotal8.png

Step 5: Set up your GETTOTAL function, which will print your running total to your report.

In the Insert Function window, choose GETTOTAL (in v13.1 chose Custom) from the Select A Category pull-down menu.

RunningTotal9.png

Select the GETTOTAL function from the list and click OK.

RunningTotal10.png

 

In the Function Arguments window you have one value that needs to be configured.

  • key = is the name you gave the ADDTOTAL function in Step 3. In this example, it is ‘total’.

To set the key value, simply type ‘total’ in the key field, and then click OK.

RunningTotal11.png

 

Output

Don’t forget to format your Out tag so your numbers show up as currency.

 

Now when you run the sample, you will have a Product Total based on the data that is returned by the forEach.

RunningTotal12.png

What's Next

You can learn more about other Windward equation functions.

  • Was this article helpful?