How to Calculate Running Totals with ADDTOTAL/GETTOTAL Function
This article will explain how 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 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.
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.
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:
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.
Remember the syntax for calling a variable in AutoTag is $[name], or in this case, $[price].
Next we'll create an Out tag that will tally your 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.
Then click the Equation button.
In the Equation window, choose ADDTOTAL from the All section of the Select a Category drop-down menu.
In the Function Arguments window, you have two values that need to be configured:
- 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.
- The name by which you will refer to this running total in your GETTOTAL function (in Step 4).
To set the num value, use the drop-down menu next to the num field and select the price parameter from Step 1.
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:
Click OK and save your [RunningTotal] tag.
Insert a new Out tag under the [Extended Price][Running Total] tags and give it a nickname of [Product Total]. 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 is the final sum.
Double click your newly inserted Out tag to launch the Tag Editor and click the Equation button.
In the Equation window, choose GETTOTAL from the Select a function drop-down menu.
Select the GETTOTAL function from the list and click OK.
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.