Skip to main content

Creating a Template in Excel with SQL Data

Welcome to Fluent Designer​

Apryse takes a unique approach to reporting. Our Fluent Designer sits directly inside Microsoft Office which means you get to use a familiar and powerful tool without having to learn a new designer. If you’ve ever used one of our competitors’ products, you may be familiar with their modular designers that require you to build reports using bits and pieces, where every bit of text or every piece of data is a distinct object in your template. Fluent changes all of that. With Fluent in Microsoft Office, you design report templates just like you design any other document, utilizing Tags to insert your data wherever you need it seamlessly in-line with your other text and content.

Training Overview​

This Training Guide walks you through the fundamental steps you need to begin designing templates with Fluent Designer in Microsoft Word using a SQL Datasource.

The Four Steps to Fluent Designer:​

Step 1 - Install​

To get started, you need to install the Designer. If you have it already installed, please skip this step! If you already have a license key, please visit the downloads page to install the software. Or start a free 14-day trial!

Step 2 - Connect to Data​

In this training guide, you are connecting to a sample SQL Datasource provided by Fluent. To connect to the sample data:

1. Open Microsoft Excel. Now that you’ve installed Fluent Designer, you will see two new tabs in the Microsoft Office Ribbon – the “Fluent” Tab and the “Fluent Tools” Tab.

2. Click on the “Fluent Tab” and click on the “Data Sources” button located on the left-hand side of the ribbon. This opens the Connection Editor where you will manage all of your data connections.  (Alternatively, you can click on the lower half of the button to expand the drop down and then click “Data Sources.”)

3. In the Connection Editor window, select the data connection labeled “SqlServer” under “Recent Inactive” and click the “Connect” button.

4. Close the Connection Editor by clicking “Close.” You have now successfully connected to the sample data!

Step 3 - Design Your Template & Insert Your Data​

In this training guide, you will build a very basic template to get the feel of Fluent Designer. While your final product may not seem very impressive, you will learn the basic skills to making any template! You will make a template which will output the product name and unit price for every product in the sample data, with each product separated by a line.

In situations where you want to output many records returned from a select, you can use a ForEach tag. The ForEach tag will generate output once for each record returned by its select. The ForEach tag itself will not insert data into the template, but rather stores data in a variable that can be used by other tags to generate output.

1. Make sure your cursor is placed on cell A1. To place a ForEach tag, expand the drop down under the button “Tags” in the Fluent ribbon and click “ForEach.”

2. Place your cursor on cell A5, expand the drop down under the button “Tags” in the Fluent ribbon, and click “EndForEach” to place a closing tag for the ForEach tag.

Now all of the content that you place between the ForEach and EndForEach tags will be generated once for each record returned by the ForEach tag’s select.

3. For the ForEach tag, you will select the Product node so that you can output information on each product.  You can use the Tag Editor to easily create a query for your tag. Place your cursor on the A1 cell containing the ForEach tag that was inserted in your template and click the “Edit Tag” button in the Fluent ribbon to open the Tag Editor.

4. In the Tag Tree, expand the node mssql.Fluent.net : Northwind -> Tables -> and double-click the “Products” table. This will automatically write a select for the Products table.

5. Click the “Save Tag” button. Congratulations! You have just inserted your first tag into your template!

6. In the next cell after the ForEach tag, enter the text “Product Name:” and in the next cell “Unit Price:”

The “Product Name:” and “Unit Price” text will be output for every product returned by your ForEach tag’s select.

Out tags will be used to insert data into your final report.  These tags will output the Product Name and Unit Price for each product after these labels.

7. Place your cursor in the cell to the right of the “Product Name:” text, and click “Tags” in the Fluent ribbon and click “Out.” This places an Out tag in the cell.

8. Open the tag Editor by placing your cursor on the cell containing the Out tag and clicking “Edit Tag.”

9. You can now see your ForEach variable in the tag editor since the Out tag occurs in the template between the ForEach tag and the EndForEach tag. In the Tag Tree, expand <wr:forEach> -> varName1 and double-click ProductName. This automatically queries the ProductName currently held by the ForEach variable.

10. Click the “Save Tag” button to save your tag's query.

11. Place your cursor in the cell to the right of the “Unit Price:” text, and click “Tags” in the Fluent ribbon and click “Out.” This places an Out tag in the cell.

12. Open the tag Editor by placing your cursor on the cell containing the Out tag and clicking “Edit Tag.”

13. You can now see your ForEach variable in the tag editor since the Out tag occurs in the template between the ForEach tag and the EndForEach tag. Expand <wr:forEach> -> varName1 and double click UnitPrice. This automatically queries the UnitPrice currently held by the ForEach tag.

14. Click the “Save Tag” button to save your tag's query.

15. Finally, you are going to add some indicator between each product so you can easily distinguish between them in your output.  Click on the 4th row on the sidebar to select the entire row.

16. Select any fill color for the row so that you will be able to differentiate between each product.

Now you are all finished with your template design!

Step 4: Output​

1. Save your template. You can save your template to anywhere you want on your machine and give it any name you want. Be sure to save it as a “.xlsx” document.

2. In the “Fluent” tab, click the “Output” button and from the drop down select “XLSX.”

Your template will output, and you should see a list of product names and their unit price with each product separated by a filled row in the excel sheet.

Congratulations! You have created your first template in Fluent Designer and produced your first output!