Malloy Documentation
search
previousTable Transpose
Listsnext

Dashboards

VSCode document

The dashboard style can be invoked on something that will render as a table # dashboard tag. When a query is rendered as a dashboard, dimensions aligned at the top, and agregates and nested queries float within the dashboard.

document
source: airports is duckdb.table('../data/airports.parquet') extend {
  measure: airport_count is count()

  view: by_state_and_county is {
    limit: 10
    group_by: state
    aggregate: airport_count
    nest: by_fac_type is  {
      group_by: fac_type
      aggregate: airport_count
    }
  }
}

Queries in Malloy are often very complex and multifaceted, which makes them difficult to read in one nested table:

document
run: airports -> by_state_and_county
QUERY RESULTS
stateairport_​countby_​fac_​type
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
ULTRALIGHT8
STOLPORT8
GLIDERPORT5
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
STOLPORT2
ULTRALIGHT2
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
GLIDERPORT2
BALLOONPORT2
STOLPORT2
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
OH749
fac_​typeairport_​count
AIRPORT537
HELIPORT201
STOLPORT4
ULTRALIGHT2
SEAPLANE BASE2
GLIDERPORT2
BALLOONPORT1
IN643
fac_​typeairport_​count
AIRPORT497
HELIPORT115
ULTRALIGHT17
SEAPLANE BASE12
STOLPORT2
AK608
fac_​typeairport_​count
AIRPORT474
SEAPLANE BASE104
HELIPORT30
NY576
fac_​typeairport_​count
AIRPORT393
HELIPORT156
SEAPLANE BASE23
GLIDERPORT2
ULTRALIGHT2
WI543
fac_​typeairport_​count
AIRPORT439
HELIPORT85
SEAPLANE BASE16
ULTRALIGHT3
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1389
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 435
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 569
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 396
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 625
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 245
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 6
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 511
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 280
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 43
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 5
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 468
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 307
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 13
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 10
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 3
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "OH",
    "airport_count": 749,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 537
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 201
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 4
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "state": "IN",
    "airport_count": 643,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 497
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 115
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 17
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "AK",
    "airport_count": 608,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 474
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 104
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 30
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 576,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 393
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 156
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 23
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "WI",
    "airport_count": 543,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 439
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 85
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 16
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."fac_type"
      END as "fac_type__1",
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as "airport_count__1"
  FROM '../data/airports.parquet' as airports
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "state__0" as "state",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "fac_type": "fac_type__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10

In such cases, the # dashboard renderer is useful for making the results easier to read:

document
# dashboard
run: airports -> by_state_and_county
QUERY RESULTS
state
TX
airport_count
1,845
by_fac_type
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
ULTRALIGHT8
STOLPORT8
GLIDERPORT5
state
CA
airport_count
984
by_fac_type
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
STOLPORT2
ULTRALIGHT2
state
IL
airport_count
890
by_fac_type
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
BALLOONPORT2
GLIDERPORT2
STOLPORT2
state
FL
airport_count
856
by_fac_type
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
state
PA
airport_count
804
by_fac_type
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
state
OH
airport_count
749
by_fac_type
fac_​typeairport_​count
AIRPORT537
HELIPORT201
STOLPORT4
SEAPLANE BASE2
ULTRALIGHT2
GLIDERPORT2
BALLOONPORT1
state
IN
airport_count
643
by_fac_type
fac_​typeairport_​count
AIRPORT497
HELIPORT115
ULTRALIGHT17
SEAPLANE BASE12
STOLPORT2
state
AK
airport_count
608
by_fac_type
fac_​typeairport_​count
AIRPORT474
SEAPLANE BASE104
HELIPORT30
state
NY
airport_count
576
by_fac_type
fac_​typeairport_​count
AIRPORT393
HELIPORT156
SEAPLANE BASE23
ULTRALIGHT2
GLIDERPORT2
state
WI
airport_count
543
by_fac_type
fac_​typeairport_​count
AIRPORT439
HELIPORT85
SEAPLANE BASE16
ULTRALIGHT3
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1389
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 435
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 569
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 396
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 625
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 245
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 6
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 511
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 280
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 43
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 5
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 468
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 307
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 13
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 10
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 3
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "OH",
    "airport_count": 749,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 537
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 201
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 4
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "state": "IN",
    "airport_count": 643,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 497
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 115
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 17
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "AK",
    "airport_count": 608,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 474
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 104
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 30
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 576,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 393
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 156
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 23
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "WI",
    "airport_count": 543,
    "by_fac_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 439
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 85
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 16
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."fac_type"
      END as "fac_type__1",
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as "airport_count__1"
  FROM '../data/airports.parquet' as airports
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "state__0" as "state",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "fac_type": "fac_type__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10