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.
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.
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:
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.
Remember the syntax for calling a variable in AutoTag is $[name], or in this case, $[price].
Step 2: Create an Out tag that will tally your running total
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.
Step 3: Set up your ADDTOTAL function, which will calculate your running total
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.
Step 4: Create an Out tag that will tally and print your Final Total
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.
Step 5: Set up your GETTOTAL function, which will print your running total to your report
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.
Step 6: 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.
0 Comments
Add your comment