If you require create one Excel file from multiple data sources, please follow the below instructions:
Illustrated below is an example of a flow that return data from 2 sources. The first query will count the number of records by a field and the second query will list all records from the table. The results will get stored in a buffer. The buffer gets queried, perform a split on the worksheet using the Calculator step. Then the Excel file gets created which will be emailed to a recipient.
In order to send data from multiple sources to a single Excel workbook, add a series control flow to the main flow. then add a Buffer destination step to store the returned data as shown in the below image.
The below image contains the first SQL query does a count on the number of records in the database as this will serve as a summary for the Summary worksheet in the same Excel workbook.
The second SQL query will list all the fields and values from the table as per the image below.
NB: To split the workbook with different worksheets, a common field is required from both queries to perform a split on. In this example, Status is being used as the split field.
The below image shows the output from the buffer after both queries have been executed.
In the 2nd part of the flow, a sub-flow is used to retrieve the data stored in the buffer using a Buffer Query source step. The field that is used to split is added as a parameter in the Calculator step and then the file is created and sent via Email as illustrated in the image below.
Add the Buffer Query source step to get the data out of the buffer by using a SQL query as shown in the below image.
In the Calculator step, a parameter called “splitter” is created to hold the field which we will split on. In this example, the field is status. The default buffer fields are also deleted as shown in the image below.
In the Excel writer step, the parameter name is referenced in the WorkSheet Name field and in the WorkSheet Split Field by using the following syntax as per the image below.
@parameters.NameOfParameter
Add the Email destination step. Fill in the required details and ensure that the Use Attachment checkbox is checked as illustrated in the below image.
