3. Compass Record Selection — Waterfall
In this step, the Compass record that the Waterfall widget will use is selected. The record must return an ordered delta series: category (X) and delta value (Y).
Wizard route: /cartography/add/selectcompass
1. Compass Record List
2. Record Structure Suitable for Waterfall
-- Quarterly profit/loss flow (category-based delta)
SELECT category, delta, sequence
FROM (
VALUES
('Opening', 1000, 1),
('Q1 Sales', 250, 2),
('Q1 Cost', -120, 3),
('Q2 Sales', 300, 4),
('Q2 Loss', -80, 5),
('Total', 1350, 6)
) AS t(category, delta, sequence)
ORDER BY sequence;
| Column | Role in Step 4 | Type |
|---|---|---|
category |
X Axis Column (CategoryAxis) | TEXT |
delta |
Y Axis Column (NumericAxis) | NUMERIC |
Sequence Column
Waterfall order is critical. Make sure your query returns a deterministic order. Use ORDER BY sequence_no or UNION ALL in SQL; otherwise categories may arrive in different order on each refresh and the chart becomes meaningless.
Make the Total Category Optional
Write the last row with the name 'Total' and turn on Treat Last Point As Total = On in Step 5. The total bar is shown with a special color (Total Sum Color).
3. Data Sources Suitable for Waterfall
| Data Source | Suitability for Waterfall | Notes |
|---|---|---|
| PostgreSQL / MSSQL / MySQL | ✅ Ideal | Practical with aggregate + ordered union |
| REST API | ✅ Suitable | API must return a sorted array |
| MQTT | ❌ Not suitable | Single snapshot cannot represent a waterfall |
| Excel Offline | ✅ Suitable | Rows must be ordered in the sheet |
4. Record Search and Tag Filter
| Action | Effect |
|---|---|
| Search box | Instant filter |
| Tag filter | Only used tags |
| Clear | Clear filters |
Waterfall Tag
Use waterfall, pnl, delta tags in Compass for Waterfall queries.
5. Confirm the Record
Click on a record to highlight the row. Proceed with the Next button at the top right.
Proceeding Without Selecting a Record
If no record is selected, Next gives a warning: "Please select a Compass query first".
6. Excel Offline Sheet Selection
For an Excel record, an additional Sheet dropdown appears.
| Step | Description |
|---|---|
| 1 | Select the Excel record |
| 2 | Select a sheet name from the dropdown that appears |
| 3 | Next |
7. Preview Execution
When the Next button is pressed, the record is executed and the result is kept in memory.
8. Common Errors
| Symptom | Possible Cause | Solution |
|---|---|---|
| Bars arrive in different order | Missing ORDER BY |
Add ORDER BY sequence_no to the query |
| Total bar not visible | Category named 'Total' but setting is off |
Set Treat Last Point As Total = On in Step 5 |
| Negative value shown in positive color | Query returns absolute values | Leave negative values as-is (negative) |