Exporting a dataset to HTML in Database Tour is trivial. But what if we need to add a line with totals to the target HTML table? Here, we'll show how to do that.
Note: This instruction can be also applied to exporting data to PDF or Excel.
Let's suppose we have a table of payments:
| PAYMENT_ID | CUSTOMER_ID | FIRST_NAME | LAST_NAME | PAYMENT_DATE | PAYMENT_SUM |
|---|---|---|---|---|---|
| 1 | 1 | Sandra | Bush | 8/30/2020 | 300 |
| 2 | 4 | Marek | Przybylsky | 8/31/2020 | 3467.35 |
| 3 | 5 | John | Hladni | 10/10/2020 | 21.89 |
| 4 | 6 | Bogdan | Vovchenko | 10/10/2020 | 250.5 |
| 5 | 2 | Eric | Miles | 11/22/2020 | 19 |
| 6 | 3 | Berndt | Mann | 11/24/2020 | 1456 |
| 7 | 4 | Marek | Przybylsky | 11/26/2020 | 690.04 |
| 8 | 1 | Sandra | Bush | 8/3/2021 | 221.99 |
| 9 | 4 | Marek | Przybylsky | 9/21/2021 | 900 |
| 10 | 5 | John | Hladni | 9/27/2021 | 124.45 |
| 11 | 6 | Bogdan | Vovchenko | 10/23/2021 | 500.79 |
| 12 | 3 | Berndt | Mann | 11/25/2021 | 609 |
...And we want to export it to HTML/PDF/Excel format with adding a row for totals.
Export Steps
- Click Export button.
- Switch to the HTML tab. When exporting to PDF or Excel format, choose PDF or Excel (XLSX) tab, respectively.
- Specify a target file name.
- Click Next to continue with field mappings.
- Delete the mapping for CUSTOMER_ID field as it does not show us any useful information.
- In the Totals Expression column of the mappings grid, type the following formulas:
for PAYMENT_ID field: vle_expr('Total')
for FIRST_NAME field: vle_expr(to_string(dataset_count(1, 0, 'PAYMENT_ID')) + ' payments')
for PAYMENT_SUM field: vle_expr('Sum: ' + to_string(dataset_sum(1, 0, 'PAYMENT_SUM')))
- Click Export.
Let's check the results:
| PAYMENT_ID | FIRST_NAME | LAST_NAME | PAYMENT_DATE | PAYMENT_SUM |
|---|---|---|---|---|
| 1 | Sandra | Bush | 8/30/2020 | 300 |
| 2 | Marek | Przybylsky | 8/31/2020 | 3467.35 |
| 3 | John | Hladni | 10/10/2020 | 21.89 |
| 4 | Bogdan | Vovchenko | 10/10/2020 | 250.5 |
| 5 | Eric | Miles | 11/22/2020 | 19 |
| 6 | Berndt | Mann | 11/24/2020 | 1456 |
| 7 | Marek | Przybylsky | 11/26/2020 | 690.04 |
| 8 | Sandra | Bush | 8/3/2021 | 221.99 |
| 9 | Marek | Przybylsky | 9/21/2021 | 900 |
| 10 | John | Hladni | 9/27/2021 | 124.45 |
| 11 | Bogdan | Vovchenko | 10/23/2021 | 500.79 |
| 12 | Berndt | Mann | 11/25/2021 | 609 |
| Total | 12 payments | Sum: 8561.01 |
Saving Export Field Mappings
Before closing the Export window, you might want to save these field mappings for future use.
Save the field mappings to a file by clicking Save button (above the mapping grid). Next time, when exporting the same or similar table, you can load the mapping from that file by clicking Load button.
Created field mappings file can also be used in building your command line when automating the exporting process.
See also
