Skip to main content
Windward

The Select Variable

Overview

When creating or using a tagged document that outputs a large amount of data, it can be helpful to use variables to limit the data in different ways. You can change the variable value any time you Output in order to change the information that is included in your generated document. By using the Filter pane of your Select Statement Wizard (usually SQL or XML wizards) and previously defined variables, AutoTag can prompt you to enter a value to limit what data is returned when you Output.

 

When Do I Use This Feature?

This feature is most often used in situations where an end user needs to produce a dynamic report by entering data values (dates, account numbers, etc.). Variables allow the flexibility of changing the value(s) in the select statement (which is what you are creating in the background with each tag you insert) every time the report is Output. Furthermore, the newly created variable can be referenced by other tags in the data selection and Select Statement Wizard's filters. This gives great flexibility when your users need to enter filtering information to change the data returned by a report when it is run. 

 

For example, you can use a single report template to output different documents:

  • Create several different reports based on the country of origin 
  • Print all invoices for a single month
  • Print only employee information for those employees with a last name starting with A

 

The major benefit of using a select variable is the fact that it is validated at runtime, so there is no way a user can put in an invalid Order ID because everything gets checked against the data source before generating the report. Error checking the data entered by end users (that your template needs in order to run) will prevent errors when generating the final report. When the user has selected a valid value (from the variable prompt in AutoTag or a custom variable form in your application) this value is then inserted into the select and sent to the data source to process and return the filtered information to the generated report.

Sample Template 

You can use Windward's Sample templates to practice using variables.

 

Select Variable - Template.docx (all data sources)

Select Variable - JSON Template.docx

Select Variable - OData Template.docx

Select Variable - SQL Template.docx

Select Variable - XML Template.docx

 

The SQL sample template uses Windward's public MS SQL database to show an example of using variable values in a select statement.

 

This sample shows a list of Orders that a user can select from a dropdown menu or enter directly in a field from the Northwind database.

Data Source Supported 

All data sources are supported

Examples in the attached template for:

  • SQL
  • XML
  • JSON
  • ODATA

Variable Example

Step 1 - Create a variable

Before you can begin using variables, you must first create one! Follow this example on the page Define a Select Variable to create the template variable that will be used in the example below.  

Step 2 - Using the variable in the template

Now that you have the variable created which allows the end user to select a value dynamically, we need to make use of it in other tags in the template. 

 

In the sample template attached we are generating an invoice based on an order ID that will return the associated customer information as well as the items purchased in that order. The order variable is needed to filter the results of both the customer information as well as the products loop in the table.

Open the [Order Query Tag] Tag

  1. In the sample template, select the [Order Query Tag] in the template and click the Wizard icon in the AutoTag Ribbon.

Select the data you want to filter and output

  1. Expand the Customers column from the Tables tree in the far left window under your data source.
  2. Drag the following columns over to the Customers area to the Columns area.
    1. CompanyName, ContactName, Address, City, Region, PostalCode, Country
  3. Drag the OrderID column from the Orders table over to the bottom of the Columns area.

Sort your data

  1. Drag the CompanyName and ContactName columns into the Sort area.  This will sort your results first by Company Name and then it will additionally sort that CompanyName list by ContactName if there is more than one contact present.

Filter you data by your newly created variable ${order}

  1. Create a new filter in the Filter area by choosing the column Orders.OrderID.
  2. In the Filter area click the value area to reveal the dropdown menu with the ${order} variable you created earlier.
  3. The variable ${order}, set to a default value of 10537 earlier, now filters the output columns you chose earlier for the Order ID 10537 as shown in the output preview to the far right of the SQL wizard in the image below.

 

(Optional) Filtering SQL Stored Procedure Results with Filter Variables

A unique feature of select variables when working with SQL data sources is that you can use them with SQL stored procedures to insert a user selected value into the stored procedure which is then sent to the SQL data source to be processed which then returns the calculated results. In this case, we are using the store procedure CustOrderDetails(@OrderID) in the tag [Products Loop] to return all products in the order as well as their calculated subtotals, including discounts.

  1. Select the [Products Loop] tag in the template and open the Tag Editor.
  2. From the data source pane on the left, expand the Stored Procedures section and drag and drop the CustOrderDetails(@OrderID) in to the select area  (above V13 and earlier, to the right V15 and later).  You will see the store procedure is now formatted as
    • EXEC CustOrdersOrders @CustomerID = S'0'
      
  3. Delete the SQL variable placeholder S'0'
  4. Drag the order variable you created earlier by clicking and holding order  and dragging it with your cursor just past the = sign.
  5. Click Preview to ensure that the stored procedure with your custom variable works properly and returns the list of calculated products in the preview pane on the lower right of the Tag Editor.  You should now see your results and your stored procedure should appear as follows
    • EXEC CustOrdersOrders @CustomerID = ${order}

 

Step 3 - Running the Template

Now that a variable has been created, its values are listed from valid values in the database and it is used to filter data returned by other tags in the document. This allows you to have an end user run the template, enter filter information and create a report on demand. 

 

When the template is run by selecting Output and the desired output type (DOCX in this example) the user is prompted to select an Order ID from a dropdown list of valid orders in the data source (as shown below). Notice the default value, order 10537, is already listed as the first value, but any value listed could be selected, and a unique report would be generated based on that Order. Once a value is selected, click OK and the template will generate an invoice for the selected order number, 10537.

 

 

 

 

This is the Output invoice for order number 10537. You can repeat the Output steps to generate other unique invoices using the instructions for Step 3 by selecting different OrderIDs when prompted (as shown above).

 

Explanation of Template Tags using Variable ${order} in this Example

[Order Number]

The full Out Tag to print the Order ID number appears as the following tag. This tag just replaces the variable order with its selected value and prints it after the Order: 

 

<wr:out value='${order}'/>

 

[Order Query Tag]

The Order Query tag is a single query that retrieves the customer information based on the selected order number. All Out Tags following it refer to the OrderQuery variable assigned to the returned results in the form ${OrderQuery.COLUMNNAME}, ie. ${OrderQuery.CompanyName}.

 

<wr:query select='SELECT dbo.Customers.CompanyName, dbo.Customers.ContactName, dbo.Customers.Address, dbo.Customers.City, dbo.Customers.Region, dbo.Customers.PostalCode, dbo.Customers.Country, dbo.Orders.OrderID FROM dbo.Customers INNER JOIN dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID WHERE(dbo.Orders.OrderID = ${order}) ORDER BY dbo.Customers.CompanyName, dbo.Customers.ContactName'/>

 

[Products Loop]

The product loop forEach tag executes the stored procedure CustOrderDetails(@OrderID). We use the order variable to  into the CustOrderDetails stored procedure as the @OrderID value. The stored procedure executes and returns to the products and their calculated subtotals to the products loop.

<wr:foreach select='exec CustOrdersDetail @OrderID =${order}' var='ProductLoop'/>

 

Order ID: Definitely more than 50 results (about 830) are returned so the user will see a textbox, that then validates an Order ID entered by the user against the database using the 'SELECT(value)' select statement.

 

 

 

  • Was this article helpful?