Skip to main content
Windward

Processing Data Sources Simultaneously

Overview

Use data from one of your data sources to filter data in another data source in your Windward templates.

Feature Details

 

Before V13.1, you could process your data sources only in a serial order. You could still work with multiple data sources in a template, but first you worked with one, then the next, and so on.

 

In V13.1 and later, you can process them simultaneously through Windward’s SET tag. A SET tag is basically a variable tag in Windward. It does not print anything but merely holds a value that you can reference by the SET tag’s variable name elsewhere in the document.

The ability to retrieve data from multiple data sources simultaneously in a single template was added in version 13.1.6.0 and newer.

The primary functionality added allows you to share variables from set tags across data sources as well as processing data source simultaneously in the template regardless of order.

A Simple Example

 

Suppose you have two data sources:

  • Data source #1 contains a list of orders.
  • Data source #2 contains more details about each order.

You can create a table of orders in data source #1 with a SET tag that references the Order ID. Then, in a table referencing data source #2, you could filter the order details by the Order ID from the SET tag in data source #1.

How to Process Data Sources Simultaneously

Relating data between two different data sources requires the following steps:

  1. You create two separate data sources and give each a unique nickname so your tags know which data source that are gathering information from.
  2. You create Windward tags that access data source #1. This can be a table with forEach tags, OUT tags or any other tag that returns data.
  3. You create a SET tag and assign it to the value of the previous tag you created. Keep in mind the SET tag must reference data source #1.
  4. You create Windward tags in data source #2 and filter your data in these tags with the SET tag from data source #1.

Example

This example will demonstrate using two separate data source, XML and Microsoft SQL, creating tables from each and then combining them in a master detail table.   This example will prove that while generating data from one data source we can simultaneously generate data from a different data source.   This will be accomplished by creating a SET tag from the first data source that will be used as a filter in the second data source.

Datasources

  • Data Source #1 – XML File Southwind.xml
  • Data Source #2 – Microsoft SQL Server Northwind Database

Tables

  • Orders Table – XML
  • Product Table – SQL
  • Combined Master Detail Table – XML Orders correlated to SQL Products

Data Source and Variable Creation

The Customer variable is created to allow a user of this example to select a customer from a drop down list and use that value to filter the XML Order table forEach tag.

Mulitple_Data_Source_Template_1.png

Image #1 - Multiple Data Source Template – page 1 displaying Orders and Products tables

Orders Table Creation

Here you will create a table of orders from an XML data source and then insert a SET tag with a new variable name ${orderid} that will reference a variable created by the forEach loop xmlorders.  From Image #1 this is the green table.

  • Create the Orders table by dragging and dropping the Orders/Order sub node from the XML data source OR drag and drop the POD Orders Table.
    • Select the following nodes in the table wizard:
      • OrderID
      • OrderDate
      • ShippedDate
      • ShipName
      • ShipPostalCode
      • ShipCountry
  • Filter the forEach tag in the XML Wizard by assigning node CustomerID equal to ${Customer} from the variable drop down list.
  • Name the forEach tag variable:  xmlorders
  • Updates all OUT tags to the new forEach variable name and node reference, IE ${xmlorders.@OrderID}
  • Create the SET tag directly after the forEach tag but before the Order ID tag in the Orders table.  
    • Variable Name: orderid
    • Variable Value: ${xmlorders.@OrderId}
  • Check all tags and ensure they are using the Southwind data source
  • Run your report to test your Orders table output

Product Table Creation

Here you will create a table of product details from an SQL data source and use the SET tag from the XML data source (by manually writing it in the select) to filter and return only the products from the order number referenced in the SET tag ${orderid} variable.  There are optional steps to create a subtotal and total fields by adding variables to OUT tag values and creating formulas from them.

  • Create the Product table by dragging and dropping the Orders Details table from the SQL data source OR drag and drop the POD Products Table.
    • Select the following nodes in the table wizard:
      • OrderID
      • ProductID
      • UnitPrice
      • Quantity
      • Discount
  • Update the forEach tag in the SQL Wizard by removing the Order Details.ProductID node and dragging over the Products.ProductName column.   A join between the Products and Order Detail table will be created automatically.
  • Manually edit the SQL select by adding the SET tag filter in the tag editor by clicking on the orders forEach tag and expanding the select in the tag editor window. Add the text below to the end of the query
    • WHERE(dbo.[Order Details].OrderID = ${orderid})
    • IMPORTANT this is where we filter by the SET tag we created earlier and the key to correlating data between two data sources.
  • Change the forEach var name attribute to sqlproductloop
  • Update the Product ID column by renaming it to Product Name and assigning the OUT tag below this value - ${sqlproductloop.ProductName}
  • Updates all OUT tags to the new forEach variable name and column reference, IE ${sqlproductloop.ProductName}
  • Check all tags and ensure they are using the Northwind data source
  • OPTIONAL:  Create extra queries for the subtotal and total
    • Add variables names to the following tags
      • Unit Price – VAR: price
      • Quantity   – VAR: qty
      • Discount   – VAR: discount
    • Create a new SET tag before the Products table with these attributes.  Used to reinitialize the total as the forEach loop repeats each time
      • NICKNAME: [Init Total]
      • SELECT: =0
      • VAR: total
    • Create a new column titled Subtotal and add two OUT tags below with nicknames [Subtotal] and [Add Subtotal]
    • [Subtotal]
      • SELECT:  = (${price} * ${qty}) - (${price} * ${qty} * ${discount})
      • VAR: subtotal
    • [Add Subtotal]
      • SELECT:  =${subtotal} + ${total}
      • VAR: total
    • Create an OUT tag [Total] below the subtotal cell
      • SELECT: ${total}
  • Run your report to test your Products table output and ensure it is only showing products from the Order Id defined.

Mulitple_Data_Source_Output_1.png

Image #2 - Multiple Data Source Output – page 1 displaying Orders and Products tables results

Mulitple_Data_Source_Template_2.png

Image #3 - Multiple Data Source Template – page 2 displaying combined Orders and Products tables

Combined Master Detail Table Creation

The final step shows you how to combine both tables together into a single table with two master/detail nested forEach loops in order to create the final output of tables for all orders and all products and details associated with each order.   This illustrates that while iterating through an SQL data source we can simultaneously iterate through an XML data source in order to correlate output from two distinct data sources.

  • Copy and paste the Orders table only (NOT including the end forEach tag) to a new section of the template.
  • Copy and paste the Product table (including the end forEach and total row if you created it) directly below the Orders table.
  • Insert an end forEach on the next line after the end of the Products table you just inserted.  This will create space between each order table when the template is generated.
  • Cut the Orders table forEach tag [MS SQL Order Details Loop] and paste it on the line above the order table.
  • Run your template.   This will create a report that will build a table of each order from the XML data source and print the order details for each order in a table below and create a space between each order and product detail table.

 

Mulitple_Data_Source_Output_2.png

Image #4 - Multiple Data Source Output – page 2 displaying combined Orders and Products tables results

 

Working with Multiple Data Sources in the Windward Engines

Java Engine

Java

 

// Define data source #1 - For Example: An XML File

DataSourceProvider datasource1 = new Dom4jDataSource(new FileInputStream(path + "/Northwind - Data.xml"));

 

// Define data source #2 - For Example: An SQL database

// connection to Windward's public Microsoft SQL Server Northwind database

DataSourceProvider datasource2 = new   JdbcDataSource("com.microsoft.sqlserver.jdbc.SQLServerDriver",

  "jdbc:sqlserver://mssql.windward.net;

  DatabaseName=Northwind", "demo", "demo");

 

// Define a hashmap containing the datasources with their names

Map mySources = new HashMap();

mySources.put("XMLNorthWind" , datasource1);

mySources.put("MSSQL" , datasource2);

 

 

// Process the template with the hashmap

report.processData(mySources);

.NET Engine

The code is very similar to the Java engine.

C#

 

//Create the data sources
  myReport.ProcessSetup();
            
  FileStream xmlFile = File.OpenRead("../../../Samples/SouthWind01.xml");
  IReportDataSource xmlData = new XmlDataSourceImpl(xmlFile, false);
            

  string MSSQLStrConn = "Data Source=mssql.windward.net;

     Initial Catalog=Northwind;User ID=demo;Password=demo";

  IReportDataSource MSSQLData =

     new AdoDataSourceImpl("System.Data.SqlClient", MSSQLStrConn);

  

 

//Create a dictionary object for the data sources and add them
  Dictionary<string, IReportDataSource> mySources =

new Dictionary<string , IReportDatasource>();

  mySources.add("XMLNorthWind", xmlData);

  mySources.add("MSSQL", MSSQLData);

           

//Process the report
  myReport.ProcessData(mySources);
  myReport.ProcessComplete();

AutoTag

By default, if your template has more than one data source then they will be processed simultaneously.

NOTE: 

When accessing a variable from another data source, the AutoTag interface currently will only display variables for the currently selected data source. It will not be displayed in the DataBin, Data source Tree, SQL or XPath wizards and you need to manually add the variable to the select query.   This functionality is in consideration for addition in the Windward Roadmap.

Restrictions

Variable sharing is only currently tested and working for SET tags. It may or may not work with OUT tags which have the var attribute set. It DOES NOT currently work with variables created from forEach tags or Query tags.

 

The data source the set tag references must be the one that is associated with the piece of data you are trying to share, IE if you have a XML data source returning information in a table and you want to use some of that information to filter a table that is referenced by an SQL data source, the SET tag needs to be created referring to the XML data source.

  • Was this article helpful?