Skip to main content
Windward

Creating Dynamic Formulas in Microsoft Excel

Overview

You can use Microsoft Excel's dynamic formulas – formulas that produce data on the fly – in Windward templates. 

Details

AutoTag carries functions across cells, and it knows when to include new cells and when to change range numbers. Here we will walk though two simple examples.  This tutorial begins with a blank template connected to the Northwind XML data source.

Instructions

Note: This tutorial covers both XML and SQL data sources, and the examples shown here use the sample data source Northwind. You can "play along" with this tutorial by using the Northwind XML file that ships with AutoTag. Or, you can follow these steps with your organization's own data sources, keeping in mind that your specific choices (data source location, server name, database name, data groups, etc.) will be different from what's listed here. 

Create a Table

Before we can begin placing tags in the template, we need to know where they will go. In this example, we'll be creating a table of products and information about those products. This table will have columns for the invoice number, the product ID, the product's unit price, the product's quantity, and a column subtotaling the cost of each product ordered.

 

Choose ten cells in Excel in which you will create a 5 by 2 table. We choose this size because it gives us one column for each data category. In the first row we will place the data titles, and in the second row we will place the tags. You do not need to know how many rows of data will be output when you run the report; AutoTag takes care of expanding that for you automatically. Note: We recommend you do not use the table command in Excel. Instead, use cells only for table construction.

 

In the table's first row, enter the column headings:

excels_functions_1.png

Create a <forEach> tag

  1. Click the first cell in what will be the second row of the table (the cell A4 in the image above).

  2. Click the Tag Builder button.

    excels_functions_1b.png

  3. In the Tag Editor, click the Tags tab, and click the <ForEach> Tag icon.

  4. Click the Select tab. Since we'll be creating a table of data from subgroups of the Order Details data group, that's the group we want to loop through. Click the Order Details data group in the Data Source pane, and drag and drop that group into the Select Bar.

  5. Give the variable a descriptive name, such as "orderdetails." The tag editor now looks like this:

    excels_functions_2.png

  6. Save the tag. 

Create the first <out> tag

Now it's time to place data subgroups into the individual cells. Click the cell that holds the <forEach> tag, then click the forEach tag button (the Tags button changes based on what tag has been inserted) and select the Out tag.

Tag

A prompt appears, asking you where you'd like to place the tag:

excels_functions_3.png

Place your pointer just after the colon (:) and click. AutoTag places the Out tag next to the forEach tag in the same cell.

 

Attach a data field to your Out tag:

 

  1. Select the cell that contains your forEach and Out tag. Click the Tag Bulider button. A prompt appears, asking you which tag you'd like to select:

 Select

  1. Click the [out] tag and the Tag Editor window appears.

  2. In the Tag Editor, click the + sign next to the <forEach> tag var name that you created in step four above– in this case, orderdetails – to expand it.

    excels_functions_3b.PNG

  3. The OrderID group contains the invoice number, so you will drag the OrderID data subgroup from the orderdetails variable into the Select Bar. Note: Be sure to drag and drop the correct OrderID data subgroup, as there are two listed in the Data Source Pane. Drag and drop the data subgroup from the orderdetails variable, not from the main data source (in this case XML : Northwind) listed below it.

  4. Save the tag.

Create additional <out> tags

Using the same five step procedure that you followed above, create <out> tags for the three remaining cells. However, each of these <out> tags will be in its own cell, so you will not see a prompt asking you where to insert the tag. The ProductID subgroup goes in the select bar for the tag in the Product ID column, the UnitPrice subgroup goes in the select bar of the tag in the Unit Price column, and the Quantity subgroup goes in the select bar of the tag in the Unit Quantity column.

Close the <forEach> loop

Click the empty cell under your first forEach tag (in our case, A5).  Click the Tags button and select the End (forEach) button.  This will close your forEach loop in your table:

excel

 

Create a Subtotal

Here's where we get down to business – create a subtotal using Excel's built-in dynamic formula for multiplication.

 

In the first example, we want to know how much is being spent on each item. In other words, we want to multiply the unit price by the unit quantity for each item, and that will give us our subtotal.

 

Click the cell below the cell labeled "subtotal." Enter the formula Excel uses for multiplication, which is an equal sign, the location of the first cell to be multiplied, an asterisk, and the location of the second cell to be multiplied. Your template looks like this (note the formula in the formula bar):

Excel

The power in using this formula is that AutoTag will automatically create the additional Subtotal cells to match the data. Instead of one subtotal, we will see a subtotal for each row. Our database contains hundreds of orders, but we only have to input the formula once.

 

Special Note: When AutoTag changes references in an Excel formula, it does not parse the formula to determine what parts are references. It just looks for XX123, where the letters are A – IV and then numbers.

So if you have macro A2, as in “A2(B1, C3)”, AutoTag will think the A2 is a reference. It's unlikely you will name a macro with 1 or 2 letters followed by a number. But if you do, AutoTag breaks because it will change the macro if it’s moved/replicated in the final report.

Optional Step - Filter the table

It is not necessary to add filters to our table in order to demonstrate formula features in Excel, but because we are working with a large set of data, we will do so now for two reasons.

One, our data source is quite large, and this will make illustrating the next formula much simpler. Two, the formula we are going to create will sum items, and using a filter now will allow us to demonstrate a very practical application: summing items and coming up with a total cost for a particular invoice in our system.

 

To begin:

• Click the cell containing the <forEach> tag (in our case, A4) and click the Tag Builder button.

• In the prompt, click the <forEach> tag.

• In the Tag Editor, click the Wizard icon.

What you do next will depend upon the data source you're using, because the wizards are different for XML and SQL data sources.

XML Data Source

For more information on using the XPath Wizard click here.

 

1. In the Conditions Pane (the left pane), click the statement [click here to add a group]

2. Click the statement [click here to enter a condition]

3. Click the statement [click here to select a node], and select the subnode (subgroup) OrderID. (Remember, OrderID is the invoice number.)

4. By default, the comparison is set to equal to, so you do not need to select a comparison.

5. Take a look at the various OrderID values listed in the Data Pane on the right. There's one for invoice number 10248, and that's the one we'll use in our example. Click the statement [click here to set the value] and in the text box, enter the number 10248.

6. You can see the entire select statement (the query that will be sent to the data source when you run the report) in the lower pane of the wizard. Click OK. This closes the XPath Wizard.

7. Click the Save Tag button. Your <forEach> tag (as seen in the formula bar) now looks like this:

excels_functions_6.png

SQL Database

For more information on using the SQL Wizard click here.

 

If you are not following along with our sample Northwind.xml datasource and are using a SQL datasource, here are the steps to set up your filter.

1. Drag the desired node (in this case, Order Details) from the left pane into the Columns area of the middle pane.

2. In the Filter area of the middle pane, click the statement [click here to add a group]

3. Click the statement [click here to add a filter]

4. Click the statement [click here to select a node], and select the subnode OrderID from the pop-up window. (Remember, OrderID is the invoice number.)

5. By default, the comparison is set to equal to, so you do not need to select a comparison.

6. Take a look at the various OrderID values listed in the Data Pane on the right. There's one for invoice number 10248, and that's the one we'll use in our example. Click the statement [click here to set the value] and in the text box, enter the number 10248.

7. You can see the entire select statement (the query that will be sent to the data source when you run the report) in the lower pane of the wizard. Click OK to close the wizard.

8. Click the Save Tag icon. The end of your <forEach> tag (as seen in the formula bar) now looks like this:

excels_functions_7.png

Create a Total

Now let's use Excel's Autosum feature to create a total.  

 

Make a Total cell in your spreadsheet.  We have placed ours under the subtotal cell (it can be on the same row as your endforEach tag). 

 

From Excel's Home tab, click the AutoSum icon. In the formula =SUM() put the location of the subtotal value, which in our example is E4, between the parentheses. Your template now looks like this:

Excel

Format the table

Use familiar Excel commands, such as those in Excel's Home menu, to clean up the table. Because columns C and E are currency, format the columns accordingly. Also, change the font, add colors, reposition text, place borders around cells, and otherwise enhance the look of the table.

Save your report template using Excel's Save command. Then, from the Output group in the AutoTag ribbon, click the desired report format icon. In this example, we chose to view the report as a PDF file, and this is the result:

excels_functions_9.png

Congratulations! You've now gotten the basics of harnessing the power of Excel formulas in your report template.  You may need to tweak your table dimensions a bit, as Excel does not let you auto-expand a cell as needed.  For more information concerning XLSX output limitations please review Output Format Limitations.