Add a Total Row to the footer of html table

[drewbty]

I am running a SQL query that outputs the below

There are more than 3 EventTypes (maybe 12 in total), but I've left it 3 for simplicity

In my flow, the final step is to create a html table and everything is working fine there

However I want to add a Total Row with the SUM of the TotalTurnover, NetPosition, Count and ClientCount to the footer of the html table

I have seen solutions on here for using an Apply For Each and Initialize Variable (Total) to 0, but the issue here is that I have 4 different columns that I want to get the Total for and that seems very messy and complicated. 

I'm thinking there has to be a cleaner way of calculating a simple Total and adding that new row to the foot of your html table?

{
"statusCode": 200,
"headers": {
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Vary": "Accept-Encoding",
"x-ms-request-id": "7e5d61e5-6e93-406e-8cfc-b25386a6ea8a",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Content-Type-Options": "nosniff",
"X-Frame-Options": "DENY",
"x-ms-connection-gateway-object-id": "dd14613e-450d-411d-a8ae-03017cbcd0cc",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "false",
"x-ms-apihub-obo": "false",
"Cache-Control": "no-store, no-cache",
"Date": "Sun, 20 Nov 2022 11:29:59 GMT",
"Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
"Expires": "-1",
"Content-Length": "2446"
},
"body": {
"ResultSets": {
"Table1": [
{
"EventType": "Soccer",
"TotalTurnover": 989.1,
"NetPosition": 143.412,
"Margin": 0.14501,
"Count": 1649,
"ClientCount": 484
},
{
"EventType": "Tennis",
"TotalTurnover": 614.98,
"NetPosition": 173.5836,
"Margin": 0.28207,
"Count": 401,
"ClientCount": 136
},
{
"EventType": "Golf",
"TotalTurnover": 2071.5,
"NetPosition": 849.6833,
"Margin": 0.410177,
"Count": 58,
"ClientCount": 33
}
]
},
"OutputParameters": {}

[grantjenkins]

There sure is a better way to total the values - no loops required 🙂

How are you generating your HTML table?

[drewbty]

@grantjenkins With the create html table step that's available within flows. I'm using custom columns From the expression 

body('WeeklySummaryBySport')?['ResultSets']?['Table1']

and accessing the items inside with e.g., 

item()['EventType'] 

I run a SQL SP

Create html table

Add CSS

Send an email

Right now I'm Totalless... 🙂 

[grantjenkins · Accepted solution]

Here's my full flow that uses XML/XPath to sum the totals. No loops required 🙂

Compose contains your raw data (what you provided).

I then create a variable called tableData of type array and add the array from the JSON using the following expression.

outputs('Compose')?['body/ResultSets/Table1']

XML is a Compose that converts the raw JSON into XML. Note that it also appends a root node in order for it to be valid XML. The expression used is:

xml(json(concat('{"root": ', outputs('Compose'), '}')))

I then append a new object to the dataArray variable. This will use XPath to sum the totals and be outputted in the final HTML table. I've also got some styling to make it look like a footer. Note that I left EventType as an empty string.

The expressions used here are:

xpath(outputs('XML'), 'sum(//TotalTurnover/text())')
xpath(outputs('XML'), 'sum(//NetPosition/text())')
xpath(outputs('XML'), 'sum(//Margin/text())')
xpath(outputs('XML'), 'sum(//Count/text())')
xpath(outputs('XML'), 'sum(//ClientCount/text())')

The full code is:

{
"EventType":"",
"TotalTurnover":@{xpath(outputs('XML'), 'sum(//TotalTurnover/text())')},
"NetPosition":@{xpath(outputs('XML'), 'sum(//NetPosition/text())')},
"Margin":@{xpath(outputs('XML'), 'sum(//Margin/text())')},
"Count":@{xpath(outputs('XML'), 'sum(//Count/text())')},
"ClientCount":@{xpath(outputs('XML'), 'sum(//ClientCount/text())')}
}

Next, we create our HTML table using the tableData array.

I then added some Table styling (CSS) including the footer (last row). This is just a sample - looks like you've already got your own CSS. Just wanted to highlight the last row here.

<style>
table{
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
}
table td, table th {
  border: 1px solid #ddd;
  padding: 8px;
}
table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}
table tr:last-child {
background-color: #fafafa;
font-weight: bold;
}
</style>

And finally, sending the HTML table via email. The Outputs are the Style and HTML table.

And what the email would look like:

[grantjenkins]

One thing I didn't add was your formatNumber stuff, but you just have to modify the Create HTML table the same as yours (custom columns) as the footer data is already part of the array.

[drewbty]

Worked perfectly. Thanks @grantjenkins 

[drewbty]

@grantjenkins How could I divide one column by another in the Append to array variable step?

xpath(outputs('XML'), 'sum(//TotalTurnover/text())')
xpath(outputs('XML'), 'sum(//NetPosition/text())')
xpath(outputs('XML'), 'sum(//Margin/text())')
xpath(outputs('XML'), 'sum(//Count/text())')
xpath(outputs('XML'), 'sum(//ClientCount/text())')

e.g., Margin should be sum(NetPosition)/sum(TotalTurnover)

I have tried to just do something like the below but it failed with the following error

xpath(outputs('XML'), 'sum(//NetPosition/text())/sum(//TotalTurnover/text())')

Unable to process template language expressions in action 'Append_to_array_variable' inputs at line '0' and column '0': 'The template language function 'xpath' parameters are invalid: the 'xpath' parameter must be a supported, well formed XPath expression. Please see https://aka.ms/logicexpressions#xpath for usage details.'.

[grantjenkins]

@drewbty Try the following expression:

xpath(outputs('XML'), 'sum(//NetPosition/text()) div sum(//TotalTurnover/text())')

[drewbty]

Thanks!

[Anonymous]

Hi @grantjenkins. I have the same request. I need the last row to be bold but I was using Dax and HTML Table to achieve the table format. I need to send the table to users via email (Outlook). I used your code in the email body but I don't see bold letters for last row. . I also tried embedding the HTML code in compose action after creating an HTML table but I don't see desired output.
Attaching the Image 1 and 2.
Image 1 is the flow .
Image 2 is the HTML code that embedded in the email body.

[drewbty]

@Anonymous  this is a known bug in the outlook app. If you load the email in the web based OWA you’ll see it bold

[Anonymous]

Thanks @drewbty