Create Dynamic Formulas in Microsoft Excel

you can take advantage of microsoft excel's dynamic formulas – formulas that produce data on the fly – in blueprint document templates 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 while keeping in mind that autotag has much more sophisticated dynamic formula capabilities this tutorial begins with a blank template connected to the northwind xml data source for more information on how to connect to a data source, see about data sources docid\ cspkol51ailrlf97 8rtm the examples in this tutorial use the sample data source northwind you can perform the same steps on your own by using the northwind xml file that ships with autotag or, you can follow these steps with your blueprint project xml data, keeping in mind that your specific choices (data source location, and so on) will be different from what's listed here tutorial step 1 create a table before you can begin placing tags in the template, you need to know where they will go in this example, you will 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 subtotalling the cost of each product ordered choose ten cells in excel in which you will create a 5 by 2 table this size is chosen because it provides one column for each data category in the first row, place the data titles, and in the second row, 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 we recommend you not use the table command in excel instead, use cells only for table construction in the table's first row, enter the column headings step 2 create a \<foreach> tag click the first cell in what will be the second row of the table (the cell a4 in the image above) click the tag builder button in the tag editor, click the tags tab, and click the \<foreach> tag icon click the select tab since you will be creating a table of data from subgroups of the order details data group, that's the group you 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 give the variable a descriptive name, such as "orderdetails " the tag editor now looks like this save the tag step 3 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, and click the tag builder button a prompt appears, asking you where you'd like to place the tag place your cursor just after the colon ( ) and click the tag builder window opens follow the same general procedure as you did with the \<foreach> tag, with a few changes click the tags tab and click the \<out> tag icon the \<out> tag is the default tag type, so this is already done for you click the select tab in the data source pane, click the + sign next to the \<foreach> tag name that you created – in this case, orderdetails – to expand it the orderid group contains the invoice number, so drag the orderid data subgroup from the orderdetails variable into the select bar 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 listed below it save the tag step 4 create additional \<out> tags using the same procedures that you followed in step three, create \<out> tags for the three remaining cells 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 of course, 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 step 5 close the \<foreach> loop next is to place data subgroups into the individual cells click the cell that holds the \<foreach> tag, and click the tag builder button a prompt appears, asking you where you'd like to place the tag if you're working with an sql data source, the tag language you see above will vary slightly step 6 create a 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 equals sign, the location of the first cell to be multiplied, an asterisk, and the location of the second cell to be multiplied your template should resemble the following the power in using this formula is that autotag automatically expands the subtotal cells to match the data instead of one subtotal, you will see a subtotal for each row the database contains hundreds of orders, but you only have to input the formula once 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 step 7 (optional) filter the table it is not necessary to add filters to our table in order to demonstrate formula features in excel, but because you are working with a large set of data, you will do so now for two reasons your data source is quite large, and this will make illustrating the next formula much simpler plus, the formula you are going to create will sum items, and using a filter now will allow you to demonstrate a very practical application summing items and coming up with a total cost for a particular invoice in your system to begin click the cell containing the \<foreach> tag and click the tag editor button in the prompt, click the \<foreach> tag in the tag editor, click the wizard icon xml data source in the conditions pane (the left pane), click the statement \[click here to add a group] click the statement \[click here to enter a condition] click the statement \[click here to select a node], and select the subnode (subgroup) orderid (remember, orderid is the invoice number ) by default, the comparison is set to equal to, so you do not need to select a comparison 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 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 click the save tag icon your \<foreach> tag (as seen in the formula bar) now looks like this sql database drag the desired node (in this case, order details) from the left pane into the columns area of the middle pane in the filter area of the middle pane, click the statement \[click here to add a group] click the statement \[click here to add a filter] 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 ) by default, the comparison is set to equal to, so you do not need to select a comparison 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 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 click the save tag icon the end of your \<foreach> tag (as seen in the formula bar) now looks like this step 8 create a dynamic formula using excel's autosum feature now it's time to place data subgroups into the individual cells click the cell that holds the \<foreach> tag, and click the tag builder button a prompt appears, asking you where you'd like to place the 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 step 9 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 document template using excel's save command then, from the autotag run report group, click the desired report format icon in this example, we chose to view the report as a pdf file, and this is the result you may need to tweak your table dimensions a bit, as excel does not let you auto expand a cell as needed