How do I build a weekly Excel report with one tab per query and a tab that grows?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A weekly report lives in one workbook: a tab per subject, a tab that grows by one block of rows each week, perhaps a tab that must always show the current state of a list. Rebuilding it by hand each Monday, or pasting rows below last week's, takes time and invites mistakes.

You will build world_report.xlsx from WORLD with four tabs, each written by one DUMP command: CONTINENTS and LANGUAGES, replaced at each run; HISTORY, which grows by one dated block each week; and FRANCE_CITIES, kept current by key through a local H2 table. You will see that each command touches only its own tab, and read the QUERIES worksheet that records where each tab came from.

Short answer

Name the file and the tab after TO: DUMP (<query>) TO world_report.CONTINENTS AS XLSX;, one command per tab. Add MODE APPEND for a tab that grows. A key (MODE MERGE KEY) works only for a local H2 table: merge there, then DUMP that table to its tab.

Before you start

Close the workbook in Excel before each run: a file open in Excel cannot be replaced, and the export fails without changing it. The commands run on WORLD and write files and a local database in the export folder (the DefaultFolder setting, c:\temp\ by default on Windows).

1. The first tab: one query, one worksheet

Connected to WORLD, this command writes the number of countries and the population of each continent into the tab CONTINENTS of world_report.xlsx:

DUMP (SELECT TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES, SUM(POPULATION) AS POPULATION FROM COUNTRY GROUP BY CONTINENT ORDER BY POPULATION DESC) TO world_report.CONTINENTS AS XLSX;

BroadSQL reports the tab and the file it wrote:

7 row(s) exported to tab 'CONTINENTS' of c:\temp\world_report.xlsx.
Completed: 2026-10-18 10:05:40 +02:00 (0:00:00).

The file is created with a header row and seven rows in CONTINENTS. (TRIM removes the trailing spaces of WORLD's fixed-length text columns.)

2. A second tab in the same file

The next subject goes into its own tab of the same workbook. This one counts the countries where each language is official:

DUMP (SELECT TRIM(LANGUAGE) AS LANGUAGE, COUNT(*) AS COUNTRIES FROM COUNTRYLANGUAGE WHERE ISOFFICIAL = 'T' GROUP BY LANGUAGE ORDER BY COUNTRIES DESC) TO world_report.LANGUAGES AS XLSX OPEN;

It writes 103 rows to LANGUAGES, and OPEN opens the file when it is written. The workbook now has three worksheets: CONTINENTS, LANGUAGES and QUERIES. QUERIES is maintained by BroadSQL: one row per tab, with its source, the date it was written and its row count, so that anyone who receives the file can tell where each tab came from.

3. Each command touches only its own tab

Close the workbook, then run the command of step 1 again. Only CONTINENTS is replaced: LANGUAGES keeps its rows, and so does any worksheet a person added by hand, such as a tab of comments. That is what lets one workbook be rebuilt one subject at a time.

4. A tab that grows: MODE APPEND

To follow a figure over time, add a dated block of rows each week instead of replacing the tab. The date is part of the result, so each block says when it was taken:

DUMP (SELECT CURRENT_DATE AS SNAPSHOT_DATE, TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO world_report.HISTORY AS XLSX MODE APPEND;

The first run creates the tab HISTORY. Run the same command next week; the message says the rows were added:

7 row(s) exported to tab 'HISTORY' of c:\temp\world_report.xlsx (MODE APPEND).
Completed: 2026-10-25 09:58:12 +02:00 (0:00:00).

The seven new rows are written below the last row of HISTORY; the rows already there, and the other tabs, are not changed. After four weekly runs, HISTORY holds 28 rows, seven per date.

What MODE APPEND requires and does:

• The same columns: the tab's header row must name the same columns as the result, in the same order (letter case aside): here SNAPSHOT_DATE, CONTINENT, COUNTRIES. Otherwise nothing is written.
• No comparison of rows: every row of the result is added. Running the command twice the same week adds the same rows twice.
• Types: they are not compared. Numbers and dates are written as real spreadsheet values.

5. The most common refusal

Rename a column in the query, for example COUNT(*) AS NB_COUNTRIES, and run it again with MODE APPEND. The header row of HISTORY no longer matches the result, so BroadSQL writes nothing and the error names the first column that differs. Alias the query's columns to match the header (... AS COUNTRIES), and the command works again.

6. A tab that stays current by key

A key is one or more columns that identify a row, such as CITY.ID. With a key, a refresh can tell new rows from rows it already has, and update the changed ones. A spreadsheet has no key and no index to compare against, so BroadSQL refuses the key modes for XLSX and ODS before writing anything:

MODE MERGE is supported only for AS H2 (spreadsheet and file destinations: use MODE OVERWRITE or MODE APPEND).

The way to a tab that stays current is to merge into a local H2 table first, by key, then write that table to the tab. Merge the cities of France into the local database WORKCOPY, by ID:

DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, POPULATION FROM CITY WHERE COUNTRYCODE = 'FRA') TO WORKCOPY.FRANCE_CITIES AS H2 MODE MERGE KEY(ID);

The first run creates WORKCOPY in the export folder and the table, then reports the counts:

MERGE into WORKCOPY.FRANCE_CITIES (table created): 40 source row(s), 40 inserted, 0 updated, 0 unchanged.
Completed: 2026-10-18 10:21:33 +02:00 (0:00:00).

Then write the merged table to its tab, replacing it, and come back to WORLD:

CONNECT WORKCOPY;
DUMP FRANCE_CITIES TO world_report.FRANCE_CITIES AS XLSX;
CONNECT WORLD;

The tab FRANCE_CITIES always holds one row per city, each with its latest values: a city added at the source appears once, a changed population is updated, and no row is ever duplicated. A city removed at the source stays, since MERGE never deletes.

7. Which mode for which tab

8. One command each Monday

Put the commands of steps 1, 2, 4 and 6 in a Script of the Scripts Library and run it with @: the whole workbook is rebuilt in one command. Write a report Script that stops at the first failure shows how to make such a Script stop at the first failed step, so that a half-built workbook is never sent.

Variations

• Export what is on screen: after running a query, DUMP / TO world_report.CONTINENTS AS XLSX; writes that result without running the query again.
• OpenDocument instead of Excel: AS ODS, with the same syntax and the same modes.
• Paste it into a spreadsheet yourself: COPY RESULT; copies the last result to the clipboard.

Watch out

• QUERIES is a reserved worksheet name.
• A worksheet holds at most 1048575 rows. For more, use AS CSV.
• Close the workbook before every run, including the runs of a Script.

Clean up

Delete world_report.xlsx from the export folder when you are done. For WORKCOPY, DEL CONNECTION WORKCOPY;, then delete WORKCOPY.mv.db from the export folder while BroadSQL is closed.

Related

• Export tables and query results with DUMP: the tutorial
• Files and spreadsheets
• Local H2 datasets: every rule of APPEND KEY and MERGE KEY
• Keep a local copy of a production table up to date

Commands used

• DUMP
• COPY RESULT

Found something wrong or unclear on this page? Tell us, with the page address.