Building Visualisations Guide

Building Visualisations Guide

This comprehensive guide walks you through the process of building visualisations in Tupaia, from data sourcing to presentation configuration.

Overview

Building a visualisation in Tupaia involves three main components:

1. Data Table - Where your data comes from (built-in tables or custom SQL)
2. Report Transform Pipeline - How you fetch, transform, and format your data
3. Dashboard Presentation - How the data is visualised to users

This guide synthesises information from multiple sources to provide a complete workflow for building visualisations.

Prerequisites

Before building visualisations, familiarise yourself with:

• SQL basics (for custom data tables)
• JSON structure
• Your data schema (data elements, organisation units, entities)
• The type of visualisation you want to create (chart, matrix, or view)

---

Step 1: Understanding Data Sources

All visualisations start with data. Tupaia provides both built-in data tables and the ability to create custom SQL data tables.

Built-in Data Tables

These are pre-configured data sources available in all reports:

Data Table	Purpose	Key Parameters
analytics	Survey and indicator data	dataElementCodes, organisationUnitCodes, period, aggregations
events	Event-level survey data	dataElementCodes, organisationUnitCodes, period
entities	Organisation unit information	entityCodes, type, filter
entity_relations	Hierarchical relationships	ancestorType, descendantType
entity_attributes	Custom entity attributes	entityCodes, filter
data_group_metadata	Survey and indicator metadata	dataGroupCodes, dataElementCodes
data_element_metadata	Individual data element details	dataElementCodes
survey_responses	Raw survey response data	surveyCodes, dataElementCodes

Example: Fetching analytics data

{
  "transform": "fetchData",
  "dataTableCode": "analytics",
  "parameters": {
    "dataElementCodes": ["BCD1", "BCD2"],
    "organisationUnitCodes": ["TO", "FJ"],
    "period": "2023"
  }
}

Custom SQL Data Tables

For more complex data requirements, you can create custom SQL data tables. These allow you to write custom queries and accept parameters.

Key characteristics:

• Use :paramName syntax for named parameters
• Always query from transform_table in SQL transforms
• Can connect to external databases
• Support parameter validation (type, required, defaultValue)

For detailed information on creating custom data tables, see [Data Table Configuration.md](Data Table Configuration.md).

---

Step 2: Building the Report Transform Pipeline

The transform pipeline is a sequence of transformations that fetch, manipulate, and format your data. Each transform is applied in order, with the output of one becoming the input to the next.

Recommended Transform Structure

While transforms can be combined in many ways, a common pattern for entity-based visualisations follows this three-step structure:

1. Entity Relations Fetch (Recommended first)

• Fetches the hierarchical relationships between entities
• Determines which facilities/districts/countries to include
• Typically uses entity_relations data table
• Respects the entity parameter from dashboard context

2. Entities Fetch (Recommended second)

• Fetches detailed information about the entities
• Provides entity names, codes, and types
• Uses output from entity relations
• Typically uses entities data table

3. Data Fetch (Recommended last)

• Fetches the actual survey/indicator data
• Uses entity codes from previous steps
• Typically uses analytics or events data table

Example: Three-step entity-based transform

{
  "transform": [
    {
      "transform": "fetchData",
      "dataTableCode": "entity_relations",
      "parameters": {
        "descendantType": "facility"
      }
    },
    {
      "transform": "fetchData",
      "dataTableCode": "entities",
      "parameters": {
        "entityCodes": "=$all.descendant"
      }
    },
    {
      "transform": "fetchData",
      "dataTableCode": "analytics",
      "parameters": {
        "dataElementCodes": ["BCD1", "BCD2"],
        "organisationUnitCodes": "=$all.code",
        "aggregations": [{"type": "MOST_RECENT"}]
      }
    }
  ]
}

Why this pattern works:

1. Entity relations respects the user's selected entity (country, district, facility)
2. Entity fetch provides human-readable names for the visualisation
3. Data fetch uses the filtered entity list from previous steps

Alternative patterns:

• Skip entity relations if you know the exact entity codes
• Skip entity fetch if you don't need entity names
• Start directly with data fetch for simple queries

---

Step 3: Data Transformation

After fetching data, you typically need to transform it into the right shape for your visualisation.

Common Transform Functions

Transform	Purpose	When to Use
insertColumns	Add calculated or static columns	Adding derived values, categories, or labels
updateColumns	Modify existing column values	Transforming data, applying calculations
excludeColumns	Remove columns	Cleaning up unnecessary fields
mergeRows	Combine rows with same key	Aggregating data across columns
sortRows	Order rows	Controlling display order
excludeRows	Filter out rows	Removing unwanted data
fillRows	Fill missing rows	Ensuring complete data for all entities/periods
insertRows	Add new rows	Adding totals, averages, or summaries
gatherColumns	Pivot columns to rows	Converting wide data to long format
orderColumns	Reorder columns	Controlling column display order
sql	Custom SQL query	Complex transformations not possible with other functions

Expression Language

Tupaia transforms support an expression language for calculations and references:

Field References:

• =$columnName - Reference a column from the current row
• =$all.columnName - Reference a column from all rows (returns array)

Context Access:

• =@params.paramName - Access dashboard parameters
• =@all.rows - Access all rows in the transform table

Functions:

• Math: =sum($col1, $col2), =divide($numerator, $denominator)
• Conditionals: =if($value > 10, "High", "Low")
• Strings: =concat($firstName, " ", $lastName)
• Dates: =dateUtils.format($timestamp, "YYYY-MM-DD")
• Comparisons: =equals($status, "Active"), =greaterThan($value, 5)

Example: Adding calculated columns

{
  "transform": "insertColumns",
  "columns": {
    "percentage": "=divide($numerator, $denominator) * 100",
    "category": "=if($value > 50, 'High', 'Low')",
    "label": "=concat($entityName, ' - ', $period)"
  }
}

For complete details on all transform functions, see [Report Building Configuration.md](Report Building Configuration.md).

---

Step 4: Formatting Data for Visualisation Types

The final transform structure depends on your visualisation type.

Chart Visualisations

Charts expect data in rows format with specific column names:

Required columns:

• name (string) - The label for each data point
• value (number) - The numeric value to display

Optional columns:

• timestamp (number) - For time series charts
• Any other columns for grouping or metadata

Example: Chart data format

[
  {"name": "Category A", "value": 45},
  {"name": "Category B", "value": 78},
  {"name": "Category C", "value": 32}
]

Transform to create chart data:

{
  "transform": "updateColumns",
  "columns": {
    "name": "=$entityName",
    "value": "=$BCD1"
  }
}

Matrix Visualisations

Matrices expect data with row and column structure:

Row format:

• Each row is an object with column keys as properties
• Special fields: dataElement (row header), category/categoryId (row grouping)

Column format:

• Array of column objects with key, title, and optionally entityCode

Example: Matrix data format

{
  "rows": [
    {"dataElement": "Indicator 1", "TO": 45, "FJ": 67},
    {"dataElement": "Indicator 2", "TO": 78, "FJ": 89}
  ],
  "columns": [
    {"key": "TO", "title": "Tonga"},
    {"key": "FJ", "title": "Fiji"}
  ]
}

Transform to create matrix:

{
  "output": {
    "type": "matrix",
    "rowField": "dataElement",
    "columns": [
      {"key": "TO", "title": "Tonga"},
      {"key": "FJ", "title": "Fiji"}
    ]
  }
}

View Visualisations

Views expect simple rows format with flexible structure:

Format:

• Array of objects
• Each object represents one item to display
• Column names become display labels

Example: View data format

[
  {"Facility": "Nuku'alofa Hospital", "Status": "Active", "Beds": 150},
  {"Facility": "Vaiola Hospital", "Status": "Active", "Beds": 80}
]

---

Step 5: Complete Transform Pipeline Example

Let's build a complete example: A matrix showing facility indicators by district.

Goal: Display BCD1 and BCD2 indicators for all facilities in the selected district, with facilities as rows and indicators as columns.

{
  "transform": [
    {
      "transform": "fetchData",
      "dataTableCode": "entity_relations",
      "parameters": {
        "descendantType": "facility"
      }
    },
    {
      "transform": "fetchData",
      "dataTableCode": "entities",
      "parameters": {
        "entityCodes": "=$all.descendant"
      }
    },
    {
      "transform": "fetchData",
      "dataTableCode": "analytics",
      "parameters": {
        "dataElementCodes": ["BCD1", "BCD2"],
        "organisationUnitCodes": "=$all.code",
        "aggregations": [{"type": "MOST_RECENT"}]
      }
    },
    {
      "transform": "mergeRows",
      "groupBy": ["organisationUnit"],
      "using": "single"
    },
    {
      "transform": "updateColumns",
      "columns": {
        "facilityName": "=$name",
        "BCD1": "=$BCD1",
        "BCD2": "=$BCD2"
      }
    },
    {
      "transform": "excludeColumns",
      "columns": ["code", "name", "organisationUnit", "period"]
    },
    {
      "transform": "sortRows",
      "by": "facilityName"
    }
  ],
  "output": {
    "type": "matrix",
    "rowField": "facilityName",
    "columns": [
      {"key": "BCD1", "title": "Indicator 1"},
      {"key": "BCD2", "title": "Indicator 2"}
    ]
  }
}

What this does:

1. Fetches all facilities in the selected entity (respects dashboard context)
2. Gets facility details (names, codes)
3. Fetches BCD1 and BCD2 data for those facilities
4. Merges rows so each facility has one row
5. Renames columns for clarity
6. Removes unnecessary columns
7. Sorts facilities alphabetically
8. Outputs as matrix with facilities as rows, indicators as columns

---

Step 6: Dashboard Presentation Configuration

After building your report transform, you configure how it's presented to users.

Basic Configuration

Every dashboard item requires:

• code - Unique identifier
• reportCode - Links to your report transform
• name - Display title
• config - Presentation configuration

Example: Basic chart presentation

{
  "code": "FACILITY_CHART",
  "reportCode": "FACILITY_REPORT",
  "name": "Facility Indicators",
  "config": {
    "type": "chart",
    "chartType": "bar",
    "labelType": "fractionAndPercentage",
    "valueType": "number"
  }
}

Interactive Features

Entity Links (Clickable Entities): Make matrix cells or rows clickable to navigate to entity pages.

{
  "type": "matrix",
  "columns": [
    {"entityCode": "TO", "entityLabel": "Tonga"},
    {"entityCode": "FJ", "entityLabel": "Fiji"}
  ]
}

DrillDown (Clickable Rows): Make matrix rows open enlarged view of another dashboard item.

{
  "type": "matrix",
  "drillDown": {
    "itemCode": "DETAIL_MATRIX",
    "parameterLink": "organisationUnitCode"
  }
}

Export Options: Allow users to export data.

{
  "exportWithLabels": true,
  "exportWithTableDisabled": false
}

For complete details on presentation configuration, see [Dashboard Presentation Configuration.md](Dashboard Presentation Configuration.md).

---

Complete End-to-End Example

Let's create a complete visualisation from start to finish.

Requirement: Show a time series chart of population growth for Pacific Island countries.

Step 1: Create Data Table (if needed)

For this example, we'll use the built-in analytics data table which already contains our population data element.

Step 2: Create Report Transform

File: report.json

{
  "code": "POPULATION_GROWTH_REPORT",
  "config": {
    "transform": [
      {
        "transform": "fetchData",
        "dataTableCode": "entity_relations",
        "parameters": {
          "descendantType": "country"
        }
      },
      {
        "transform": "fetchData",
        "dataTableCode": "entities",
        "parameters": {
          "entityCodes": "=$all.descendant"
        }
      },
      {
        "transform": "fetchData",
        "dataTableCode": "analytics",
        "parameters": {
          "dataElementCodes": ["POP_TOTAL"],
          "organisationUnitCodes": "=$all.code",
          "period": "RANGE(2018-01-01, 2023-12-31);YEARLY"
        }
      },
      {
        "transform": "updateColumns",
        "columns": {
          "name": "=concat($name, ' ', $period)",
          "value": "=$value",
          "timestamp": "=periodToTimestamp($period)"
        }
      },
      {
        "transform": "excludeColumns",
        "columns": ["code", "period", "organisationUnit", "dataElement"]
      },
      {
        "transform": "sortRows",
        "by": "timestamp"
      }
    ]
  }
}

Step 3: Create Dashboard Item

File: dashboard_item.json

{
  "code": "POPULATION_GROWTH_CHART",
  "reportCode": "POPULATION_GROWTH_REPORT",
  "name": "Population Growth (2018-2023)",
  "config": {
    "type": "chart",
    "chartType": "line",
    "valueType": "number",
    "labelType": "fractionAndPercentage",
    "periodGranularity": "year",
    "chartConfig": {
      "cartesian": {
        "xAxisDomain": {
          "min": {
            "type": "number",
            "value": "2018"
          },
          "max": {
            "type": "number",
            "value": "2023"
          }
        }
      }
    },
    "exportWithLabels": true,
    "presentationOptions": {
      "hideAverage": true
    }
  }
}

Step 4: Add to Dashboard

Add your dashboard item to the appropriate dashboard via the dashboard relation.

Result: Users see a line chart showing population growth over time for each country in the Pacific region.

---

Best Practises

Performance

• Fetch only the data you need (limit date ranges, entity codes, data elements)
• Use aggregations in fetchData rather than later transforms
• Avoid unnecessary transforms (each step processes all rows)
• Use excludeColumns to remove fields you don't need

Maintainability

• Use descriptive column names
• Add comments in complex expressions
• Break complex calculations into multiple steps
• Follow the recommended entity relations → entities → data pattern

Data Quality

• Use fillRows to ensure complete data for all entities/periods
• Handle missing values with defaultValue in expressions
• Validate data ranges with conditional expressions
• Use appropriate aggregations (MOST_RECENT, FINAL_EACH_YEAR, etc.)

User Experience

• Provide clear, descriptive titles
• Use appropriate visualisation types for your data
• Enable exports when users need raw data
• Add entity links for navigation
• Use drillDown for detailed views

---

Common Patterns

Pattern 1: Facility Matrix by District

Show facility-level data for all facilities in selected district.

{
  "transform": [
    {"transform": "fetchData", "dataTableCode": "entity_relations", "parameters": {"descendantType": "facility"}},
    {"transform": "fetchData", "dataTableCode": "entities", "parameters": {"entityCodes": "=$all.descendant"}},
    {"transform": "fetchData", "dataTableCode": "analytics", "parameters": {"organisationUnitCodes": "=$all.code"}}
  ]
}

Pattern 2: Country Comparison Matrix

Compare multiple countries across indicators.

{
  "transform": [
    {"transform": "fetchData", "dataTableCode": "analytics", "parameters": {"organisationUnitCodes": ["TO", "FJ", "VU"]}},
    {"transform": "mergeRows", "groupBy": ["dataElement"], "using": "single"}
  ],
  "output": {
    "type": "matrix",
    "rowField": "dataElement",
    "columns": [
      {"key": "TO", "title": "Tonga", "entityCode": "TO"},
      {"key": "FJ", "title": "Fiji", "entityCode": "FJ"},
      {"key": "VU", "title": "Vanuatu", "entityCode": "VU"}
    ]
  }
}

Pattern 3: Time Series with Grouping

Show trends over time grouped by category.

{
  "transform": [
    {"transform": "fetchData", "dataTableCode": "analytics", "parameters": {"period": "RANGE(2020-01-01, 2023-12-31);MONTHLY"}},
    {"transform": "insertColumns", "columns": {"category": "=if($value > 50, 'High', 'Low')"}},
    {"transform": "updateColumns", "columns": {"name": "=concat($category, ' - ', $period)"}}
  ]
}

Pattern 4: Summary Row in Matrix

Add totals or averages to matrix.

{
  "transform": [
    {"transform": "fetchData", "dataTableCode": "analytics"},
    {"transform": "mergeRows", "groupBy": ["dataElement"], "using": "single"},
    {"transform": "insertRows", "position": "end", "rows": [{"dataElement": "TOTAL", "value": "=sum($all.value)"}]}
  ]
}

---

Troubleshooting

Problem: No data appears in visualisation

Possible causes:

• Entity parameter not passed correctly - check entity relations fetch
• Date range excludes available data - check period parameters
• Organisation unit codes don't exist - verify entity codes
• Data element codes incorrect - check spelling and case

Solution:

1. Test each transform step individually
2. Check intermediate data with console.log equivalents
3. Verify parameter syntax (=$column vs $column)

Problem: Matrix rows/columns in wrong format

Possible causes:

• Missing mergeRows transform
• Incorrect rowField in output configuration
• Column keys don't match data keys

Solution:

1. Ensure data is pivoted correctly with mergeRows
2. Verify rowField matches actual column name
3. Check column key matches data object keys

Problem: Expressions not evaluating

Possible causes:

• Missing = prefix
• Incorrect column reference syntax
• Function name typo
• Column doesn't exist in current transform step

Solution:

1. All expressions must start with =
2. Use =$column not $column
3. Verify column exists at that transform step
4. Check function names in documentation

Problem: DrillDown not working

Possible causes:

• parameterLink field doesn't exist in row data
• itemCode doesn't exist
• Wrong visualisation type (drillDown only works with matrix)

Solution:

1. Verify field exists in transform output
2. Check dashboard item code spelling
3. Ensure type: "matrix" in config

Problem: Entity links not clickable

Possible causes:

• Missing entityCode in column definition
• Entity code doesn't exist in database
• Wrong format (should be object with entityCode and entityLabel)

Solution:

1. Add entityCode to column objects
2. Verify entity codes are valid
3. For row data, use {"entityCode": "CODE", "entityLabel": "Name"}

---

Additional Resources

• [Report Building Configuration.md](Report Building Configuration.md) - Complete reference for all transform functions, aliases, and expressions
• [Dashboard Presentation Configuration.md](Dashboard Presentation Configuration.md) - Complete reference for all visualisation types and configuration options
• [Data Table Configuration.md](Data Table Configuration.md) - Guide for creating custom SQL data tables

---

Glossary

• Data Element - A specific data point tracked in Tupaia (e.g., "Number of beds", "Population")
• Organisation Unit - An entity in the hierarchy (country, district, facility)
• Entity - Generic term for organisation units
• Transform - A step in the data pipeline that modifies the data structure
• Aggregation - Combining multiple data points (e.g., MOST_RECENT, SUM, AVERAGE)
• Period - Time range or point for data (e.g., "2023", "2023Q1", "20230615")
• Matrix - Table visualisation with rows and columns
• DrillDown - Clicking a row to open detailed view
• Entity Link - Clickable entity that navigates to entity page
• Expression - Dynamic calculation using = syntax
• Context - Runtime information (parameters, user, entity)