Introduction

This article describes how to use the Format as HTML Pro The Format as HTML Pro tool is used to create a task step that produces single or multiple HTML documents. You can choose to create the HTML template from scratch or import a pre-existing template. Recordsets and other task step properties can also be used to populate the HTML document to product dynamic content documents. These documents can then be consumed and then delivered by Output or Execute task steps. tool to create HTML documents that match the output from the Format as HTML tool.

The procedure below details how to create output from a single recordset. The Format as HTML Pro tool can consume output from multiple data sources (though, at the time of writing, in recordset form only); for a detailed description of how to create output from multiple sources, see our article How to use Multiple Recordsets in Format as HTML Pro.

The Scenario

The Format as HTML Pro tool has been designed to be used in any scenario. For this article, we use the sales manager analogy — we want to create a single document that details each sales manager’s orders, the total per manager, and the grand total of all orders.

This is the scenario that we will be referring to throughout this article; you should replace scenario-specific areas with your own business process objects.

Creating the Task

For ease of use, we’ll make the start and end steps the same for both scenarios — Database Query (ODBC) You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. and Save File The Save File tool saves output from another task step capable of producing document, as one or more files on the local drive, on-premise file server, external server, or cloud based server.. The task we are creating takes a recordset from the database query, creates an HTML document to display it, and saves the document to a .html file. This procedure does not detail how to configure the Database Query (ODBC) and Save File steps — for a detailed description of both tools, go to How to Add a Database Query (ODBC) Task Step and How to Add a Save File Task Step.

A Note About Creating the Recordset in the Database Query Step

It is important to structure your SQL statement correctly in order to create the recordset data (and therefore the HTML documents) you expect or need. For this scenario, we want to see a sub-total for each Sales Manager. Because of this, we need to ensure that we group the data according to Sales Manager otherwise we end up with multiple sub-total entries for each name.

Create the HTML Document in Format as HTML

Let's refresh our memories of what the output is from a task containing a Format as HTML task step.

The easiest way to display recordset data is in table form. We can then add sub- and grand totals to this.

In the General tab, consume the recordset from the Database Query step.

In the Formatting tab, we are going to create the table to display the data; we are not going to add any other HTML formatting as the goal is to use the Format as HTML Pro tool.

1. We want to generate One document.
2. Now we Design the HTML table.
   1. Add all required recordset columns to the table and adjust the formatting for each.
   2. Enable Sub Total and Grand Total for those columns that we want to add up.
3. Insert the table in the HTML design area.
4. In the Save File step, consume the output from the Format as HTML step.

When the task is run, it outputs the below with the sub-totals for each Sales Manager, with grand totals at the bottom:

Click the image to view full size.

Create the HTML Document in Format as HTML Pro

Now we're going to replace the Format as HTML step in the task with a Format as HTML Pro step.

Configuring the Data for the Document

In the Data tab, we specify the data source for the HTML document and any data manipulation required.

Keep in mind how we want to structure our output document — One document output with a sub-total for each sales manager and a grand total for all.

1. Specify the input recordset — drag the recordset (Results from ...) to Structure.
2. The Format as HTML Pro tool provides a template to create the same output as you would have from the Format as HTML tool:

   Right-click the new node and select Templates > Convert to Flat Table.

   

3. Now we need to specify the columns to total on:

   

   1. From Key, select the column that you want to sub-total for.
   2. From Column, choose the column that you want to sub-total on.
4. The Format as HTML Pro tool automatically creates group and grand total nodes according to what you select in step 3:

   

   If creating this structure manually using the other available templates, this is the order you must create the nodes:

■

Results from ... Group node — The parent node dictates the recordset column you are grouping the data on. For this scenario, we are grouping on sales manager as we want to create a sub-total for each.

■

Results from ... node — This node determines the recordset columns that are outputted in the HTML document. By default, all columns are selected. To remove a column from the display, highlight the node, and from the Table tab, select Remove for each non-required column:

■

Results from ... Subtotals node — This node dictates the type of Aggregation (calculation) for the total. For this scenario, we are creating a sub-total (SUM) for each group in the recordset.

■

Results from ... Grand Total node — This final node is the Aggregation for all records in the recordset. Again, this Aggregation uses a SUM calculation.

All relationships between each node are automatically created as well.

No other data manipulation is required at this point.

Designing the HTML Document

The Format as HTML Pro tool provides basic formatting for tables. For this exercise, we won’t make any changes to this.

1. Drag the parent recordset node from Structure to the HTML design area.
2. From the pop-up menu, select Flat Table.

As we are recreating the output from the Format as HTML tool, no further design changes are required. However, if you want to make changes to the design of the table, for example, change column headings, it is recommended you indent the HTML for ease of use.

A few things to note about the HTML:

►

class="-bpa-fah-row-<node_id>" — This class is a BPA Platform-specific class. It handles looping through the input recordset and outputs each record found on a new line. You can add this class to any valid HTML element (p, span, div, and so on).

For more information about available BPA Platform-specific classes, see A Note About the BPA Platform-Specific HTML Classes.

■

Putting class="-bpa-fah-row-ResultsfromDBQAllAccountManagerSales" in a <tr> element renders a new row in the table for each record in the input recordset. If this was the <table> element, it generate a new table for every record.

■

The classes in the above HTML reference the following nodes:

ResultsfromDBQAllAccountManagerSales — “Parent” group node

ResultsfromDBQAllAccountManagerSales2 — “Specific columns to display” node

ResultsfromDBQAllAccountManagerSales3 — “Sub-total” node

ResultsfromDBQAllAccountManagerSales4 — “Grand total” node

►

Marker fields — Marker fields are those wrapped in {{ }}. These are placeholders for BPA Platform data; in this scenario, for recordset data. The makers must belong to a parent -bpa-class otherwise a validation error may occur. Near the bottom of the above example HTML, {{ITEMS_GROSS}} belongs to -bpa-fah-row-ResultsfromDBQAllAccountManagerSales4.

The final step is to make sure the sub- and grand totals display as currency:

1. In the Structure pane, highlight the sub-total node.
2. From the Fields pane, highlight the field you are sub-totalling on; there should only be a single field.
3. In Field Properties, change Data Type to Currency.
4. Do the same for the grand total node.

That's it for the Format as HTML Pro tool; save and close the step.

Finally, we can add a Save File step to the task to save the HTML document to disk.

After queuing the task, when you view the new output, you'll notice that the totals are left-aligned whereas the original output had them right-aligned:

Click the image to view full size.

Using embedded styling in the HTML, you can manipulate the design to move elements around. The HTML design area is a completely free-form area, allowing you to add valid HTML elements as required.

About the Other Design Templates

When dragging nodes into the design area, a few templates are available to you:

►

Node ID — The internal unique identifier of the recordset node as plain text.

►

Table — The recordset data displayed as a table. If this recordset has children, selecting this option won't include them in the HTML template. All records from the recordset are used in the output template.

►

Table with Children — The recordset data, displayed as a table, with all child recordsets included as an embedded table per row. This option is only available when using the parent recordset. The image above had this option selected.

►

Flat Table — If you have used the Convert to Flat Table template when building your recordset structure, use this option to output an HTML document in the same format as outputted by the Format as HTML tool.