Igor Lukanin for Cube

Posted on Sep 24, 2020 • Originally published at material-ui-dashboard.cube.dev

Material UI Dashboard 📊 with React

#javascript #materialui #tutorial #react

Material UI is the most popular React UI framework. Created with inspiration from Google's Material Design, Material UI provides a lot of ready-to-use components to build web applications, including dashboards, fast and easy.

In this tutorial, we'll learn how to build a full-stack dashboard with KPIs, charts, and a data table. We'll go from data in the database to the interactive, filterable, and searchable admin dashboard.

We're going to use Cube.js for our analytics API. It removes all the hustle of building the API layer, generating SQL, and querying the database. It also provides many production-grade features like multi-level caching for optimal performance, multi-tenancy, security, and more.

Below you can see an animated image of the application we're going to build. Also, check out the live demo and the full source code available on GitHub.

Analytics Backend with Cube.js

We're going to build the dashboard for an e-commerce company that wants to track its overall performance and orders' statuses. Let's assume that the company keeps its data in an SQL database. So, in order to display that data on a dashboard, we're going to create an analytics backend.

First, we need to install the Cube.js command-line utility (CLI). For convenience, let's install it globally on our machine.

$ npm install -g cubejs-cli

Then, with the CLI installed, we can create a basic backend by running a single command. Cube.js supports all popular databases, and the backend will be pre-configured to work with a particular database type:

$ cubejs create <project name> -d <database type>

We’ll use a PostgreSQL database. Please make sure you have PostgreSQL installed.

To create the backend, we run this command:

$ cubejs create react-material-dashboard -d postgres

Now we can download and import a sample e-commerce dataset for PostgreSQL:

$ curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom-dump.sql

Once the database is ready, the backend can be configured to connect to the database. To do so, we provide a few options via the .env file in the root of the Cube.js project folder (react-material-dashboard):

CUBEJS_DB_NAME=ecom
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=secret

Now we can run the backend!

In development mode, the backend will also run the Cube.js Playground. It's a time-saving web application that helps to create a data schema, test out the charts, and generate a React dashboard boilerplate. Run the following command in the Cube.js project folder:

$ node index.js

Next, open http://localhost:4000 in your browser.

We'll use the Cube.js Playground to create a data schema. It's essentially a JavaScript code that declaratively describes the data, defines analytical entities like measures and dimensions, and maps them to SQL queries. Here is an example of the schema which can be used to describe users’ data.

cube(`Users`, {
  sql: `SELECT * FROM users`,

  measures: {
    count: {
      sql: `id`,
      type: `count`
    },
  },

  dimensions: {
    city: {
      sql: `city`,
      type: `string`
    },

    signedUp: {
      sql: `created_at`,
      type: `time`
    },

    companyName: {
      sql: `company_name`,
      type: `string`
    },
  },
});

Cube.js can generate a simple data schema based on the database’s tables. If you already have a non-trivial set of tables in your database, consider using the data schema generation because it can save time.

For our backend, we select the line_items, orders, products, and users tables and click “Generate Schema.” As the result, we'll have 4 generated files in the schema folder—one schema file per table.

Once the schema is generated, we can build sample charts via web UI. To do so, navigate to the “Build” tab and select some measures and dimensions from the schema.

The "Build" tab is a place where you can build sample charts using different visualization libraries and inspect every aspect of how that chart was created, starting from the generated SQL all the way up to the JavaScript code to render the chart. You can also inspect the Cube.js query encoded with JSON which is sent to Cube.js backend.

Frontend with Material UI

Creating a complex dashboard from scratch usually takes time and effort.

The Cube.js Playground can generate a template for any chosen frontend framework and charting library for you. To create a template for our dashboard, navigate to the "Dashboard App" and use these options:

Framework: React
Main Template: React Material UI Static
Charting Library: Chart.js

Congratulations! Now we have the dashboard-app folder in our project. This folder contains all the frontend code of our analytical dashboard.

Now it's time to add the Material UI framework. To have a nice-looking dashboard, we're going to use a custom Material UI theme. You can learn about creating your custom Material UI themes from the documentation. For now, let's download a pre-configured theme from GitHub:

$ curl -LJO https://github.com/cube-js/cube.js/tree/master/examples/material-ui-dashboard/dashboard-app/src/theme/theme.zip

Then, let's install the Roboto font which works best with Material UI:

$ npm install typeface-roboto

Now we can include the theme and the font to our frontend code. Let's use the ThemeProvider from Material UI and make the following changes in the App.js file:

// ...

- import { makeStyles } from "@material-ui/core/styles";
+ import { makeStyles, ThemeProvider } from "@material-ui/core/styles";
+ import theme from './theme';
+ import 'typeface-roboto'
+ import palette from "./theme/palette";

// ...

const useStyles = makeStyles((theme) => ({
  root: {
    flexGrow: 1,
+    margin: '-8px',
+    backgroundColor: palette.primary.light,
  },
}));

const AppLayout = ({children}) => {
  const classes = useStyles();
  return (
+   <ThemeProvider theme={theme}>
      <div className={classes.root}>
        <Header/>
        <div>{children}</div>
      </div>
+   </ThemeProvider>
  );
};

// ...

The only thing left to connect the frontend and the backend is a Cube.js query. We can generate a query in the Cube.js Playground. Go to http://localhost:4000/, navigate to the "Build" tab, and choose the following query parameters:

Measure: Orders Count
Dimension: Orders Status
Data range: This week
Chart type: Bar

We can copy the Cube.js query for the shown chart and use it in our dashboard application.

To do so, let's create a generic <BarChart /> component which, in turn, will use a ChartRenderer component. Create the src/components/BarChart.js file with the following contents:

import React from "react";
import clsx from "clsx";
import PropTypes from "prop-types";
import { makeStyles } from '@material-ui/styles';
import ChartRenderer from './ChartRenderer'
import {
  Card,
  CardContent,
  Divider,
} from "@material-ui/core";

const useStyles = makeStyles(() => ({
  root: {},
  chartContainer: {
    position: "relative",
    padding: "19px 0"
  }
}));

const BarChart = props => {
  const { className, query, ...rest } = props;
  const classes = useStyles();

  return (
    <Card {...rest} className={clsx(classes.root, className)}>
      <CardContent>
        <div className={classes.chartContainer}>
          <ChartRenderer vizState={{ query, chartType: 'bar' }}/>
        </div>
      </CardContent>
    </Card>
  )
};

BarChart.propTypes = {
  className: PropTypes.string
};

export default BarChart;

We'll need some custom options for the <ChartRenderer /> component. These options will make the bar chart look nice.

Create the helpers folder inside the dashboard-app/src folder. Inside the helpers folder, create the BarOptions.js file with the following contents:

import palette from '../theme/palette';
export const BarOptions = {
  responsive: true,
  legend: { display: false },
  cornerRadius: 50,
  tooltips: {
    enabled: true,
    mode: 'index',
    intersect: false,
    borderWidth: 1,
    borderColor: palette.divider,
    backgroundColor: palette.white,
    titleFontColor: palette.text.primary,
    bodyFontColor: palette.text.secondary,
    footerFontColor: palette.text.secondary,
  },
  layout: { padding: 0 },
  scales: {
    xAxes: [
      {
        barThickness: 12,
        maxBarThickness: 10,
        barPercentage: 0.5,
        categoryPercentage: 0.5,
        ticks: {
          fontColor: palette.text.secondary,
        },
        gridLines: {
          display: false,
          drawBorder: false,
        },
      },
    ],
    yAxes: [
      {
        ticks: {
          fontColor: palette.text.secondary,
          beginAtZero: true,
          min: 0,
        },
        gridLines: {
          borderDash: [2],
          borderDashOffset: [2],
          color: palette.divider,
          drawBorder: false,
          zeroLineBorderDash: [2],
          zeroLineBorderDashOffset: [2],
          zeroLineColor: palette.divider,
        },
      },
    ],
  },
};

Let's edit the src/components/ChartRenderer.js file to pass the options to the <Bar /> component:

// ...

import TableHead from '@material-ui/core/TableHead';
import TableRow from '@material-ui/core/TableRow';
+ import palette from '../theme/palette'
+ import moment from 'moment';
+ import { BarOptions } from '../helpers/BarOptions.js';
- const COLORS_SERIES = ['#FF6492', '#141446', '#7A77FF'];
+ const COLORS_SERIES = [palette.secondary.main, palette.primary.light, palette.secondary.light];

// ...

    bar:
 ({ resultSet }) => {
    const data = {
-      labels: resultSet.categories().map((c) => c.category),
+      labels: resultSet.categories().map((c) => moment(c.category).format('DD/MM/YYYY')),
      datasets: resultSet.series().map((s, index) => ({
        label: s.title,
        data: s.series.map((r) => r.value),
        backgroundColor: COLORS_SERIES[index],
        fill: false,
      })),
    };
-    return <Bar data={data} options={BarOptions} />;
+    return <Bar data={data} options={BarOptions} />;
  },

//...

Now the final step! Let's add the bar chart to the dashboard. Edit the src/pages/DashboardPage.js and use the following contents:

import React from 'react';
import { Grid } from '@material-ui/core';
import { makeStyles } from '@material-ui/styles';

import BarChart from '../components/BarChart.js'

const useStyles = makeStyles(theme => ({
  root: {
    padding: theme.spacing(4)
  },
}));

const barChartQuery = {
  measures: ['Orders.count'],
  timeDimensions: [
    {
      dimension: 'Orders.createdAt',
      granularity: 'day',
      dateRange: 'This week',
    },
  ],
  dimensions: ['Orders.status'],
  filters: [
      {
        dimension: 'Orders.status',
        operator: 'notEquals',
        values: ['completed'],
      },
    ],
};

const Dashboard = () => {
  const classes = useStyles();
  return (
    <div className={classes.root}>
      <Grid
        container
        spacing={4}
      >
        <Grid
          item
          lg={8}
          md={12}
          xl={9}
          xs={12}
        >
          <BarChart query={barChartQuery}/>
        </Grid>
      </Grid>
    </div>
  );
};

export default Dashboard;

That's all we need to display our first chart! 🎉

In the next part, we'll make this chart interactive by letting users change the date range from "This week" to other predefined values.

Interactive Dashboard with Multiple Charts

In the previous part, we've created an analytical backend and a basic dashboard with the first chart. Now we're going to expand the dashboard so it provides the at-a-glance view of key performance indicators of our e-commerce company.

Custom Date Range

As the first step, we'll let users change the date range of the existing chart.

We'll use a separate <BarChartHeader /> component to control the date range. Let's create the src/components/BarChartHeader.js file with the following contents:

import React from 'react';
import PropTypes from 'prop-types';
import { makeStyles } from '@material-ui/styles';
import { CardHeader, Button } from '@material-ui/core';
import ArrowDropDownIcon from '@material-ui/icons/ArrowDropDown';
import Menu from '@material-ui/core/Menu';
import MenuItem from '@material-ui/core/MenuItem';

const useStyles = makeStyles(() => ({
  headerButton: {
    letterSpacing: '0.4px',
  },
}));

const BarChartHeader = (props) => {
  const { setDateRange, dateRange, dates } = props;
  const defaultDates = ['This week', 'This month', 'Last 7 days', 'Last month'];
  const classes = useStyles();

  const [anchorEl, setAnchorEl] = React.useState(null);
  const handleClick = (event) => {
    setAnchorEl(event.currentTarget);
  };
  const handleClose = (date) => {
    setDateRange(date);
    setAnchorEl(null);
  };
  return (
    <CardHeader
      action={
        <div>
          <Button
            className={classes.headerButton}
            size="small"
            variant="text"
            aria-controls="simple-menu"
            aria-haspopup="true"
            onClick={handleClick}
          >
            {dateRange} <ArrowDropDownIcon />
          </Button>
          <Menu
            id="simple-menu"
            anchorEl={anchorEl}
            keepMounted
            open={Boolean(anchorEl)}
            onClose={() => handleClose(dateRange)}
          >
            {dates ?
              dates.map((date) => (
                <MenuItem key={date} onClick={() => handleClose(date)}>{date}</MenuItem>
              ))
             : defaultDates.map((date) => (
                <MenuItem key={date} onClick={() => handleClose(date)}>{date}</MenuItem>
              ))}
          </Menu>
        </div>
      }
      title="Latest Sales"
    />
  );
};

BarChartHeader.propTypes = {
  className: PropTypes.string,
};

export default BarChartHeader;

Now let's add this <BarChartHeader /> component to our existing chart. Make the following changes in the src/components/BarChart.js file:

// ...

import ChartRenderer from './ChartRenderer'
+ import BarChartHeader from "./BarChartHeader";

// ...

const BarChart = (props) => {
-  const { className, query, ...rest } = props;
+  const { className, query, dates, ...rest } = props;
  const classes = useStyles();

+  const [dateRange, setDateRange] = React.useState(dates ? dates[0] : 'This week');
+  let queryWithDate = {...query,
+    timeDimensions: [
+      {
+        dimension: query.timeDimensions[0].dimension,
+        granularity: query.timeDimensions[0].granularity,
+        dateRange: `${dateRange}`
+      }
+    ],
+  };

  return (
    <Card {...rest} className={clsx(classes.root, className)}>
+      <BarChartHeader dates={dates} dateRange={dateRange} setDateRange={setDateRange} />
+      <Divider />
      <CardContent>
        <div className={classes.chartContainer}>
          <ChartRenderer vizState={{ query: queryWithDate, chartType: 'bar' }}/>
        </div>
      </CardContent>
    </Card>
  )
};

// ...

Well done! 🎉 Here's what our dashboard application looks like:

KPI Chart

The KPI chart can be used to display business indicators that provide information about the current performance of our e-commerce company. The chart will consist of a grid of tiles, where each tile will display a single numeric KPI value for a certain category.

First, let's use the react-countup package to add the count-up animation to the values on the KPI chart. Run the following command in the dashboard-app folder:

npm install --save react-countup

New we're ready to add the new <KPIChart/> component. Add the src/components/KPIChart.js component with the following contents:

import React from 'react';
import clsx from 'clsx';
import PropTypes from 'prop-types';
import { makeStyles } from '@material-ui/styles';
import { Card, CardContent, Grid, Typography, LinearProgress } from '@material-ui/core';
import { useCubeQuery } from '@cubejs-client/react';
import CountUp from 'react-countup';
import CircularProgress from '@material-ui/core/CircularProgress';

const useStyles = makeStyles((theme) => ({
  root: {
    height: '100%',
  },
  content: {
    alignItems: 'center',
    display: 'flex',
  },
  title: {
    fontWeight: 500,
  },
  progress: {
    marginTop: theme.spacing(3),
    height: '8px',
    borderRadius: '10px',
  },
  difference: {
    marginTop: theme.spacing(2),
    display: 'flex',
    alignItems: 'center',
  },
  differenceIcon: {
    color: theme.palette.error.dark,
  },
  differenceValue: {
    marginRight: theme.spacing(1),
  },
  green: {
    color: theme.palette.success.dark,
  },
  red: {
    color: theme.palette.error.dark,
  },
}));

const KPIChart = (props) => {
  const classes = useStyles();
  const { className, title, progress, query, difference, duration, ...rest } = props;
  const { resultSet, error, isLoading } = useCubeQuery(query);
  const differenceQuery = {...query,
    "timeDimensions": [
      {
        "dimension": `${difference || query.measures[0].split('.')[0]}.createdAt`,
        "granularity": null,
        "dateRange": "This year"
      }
    ]};
  const differenceValue = useCubeQuery(differenceQuery);

  if (isLoading || differenceValue.isLoading) {
    return (
      <div style={{ display: 'flex', alignItems: 'center', justifyContent: 'center' }}>
        <CircularProgress color="secondary" />
      </div>
    );
  }
  if (error || differenceValue.error) {
    return <pre>{(error || differenceValue.error).toString()}</pre>;
  }
  if (!resultSet || !differenceValue.resultSet) {
    return null
  }
  if (resultSet && differenceValue.resultSet) {
    let postfix = null;
    let prefix = null;
    const measureKey = resultSet.seriesNames()[0].key;
    const annotations = resultSet.tableColumns().find(tableColumn => tableColumn.key === measureKey);
    const format = annotations.format || (annotations.meta && annotations.meta.format);
    if (format === 'percent') {
      postfix = '%'
    } else if (format === 'currency') {
      prefix = '$'
    }

    let value = null;
    let fullValue = resultSet.seriesNames().map((s) => resultSet.totalRow()[s.key])[0];
    if (difference) {
      value = differenceValue.resultSet.totalRow()[differenceQuery.measures[0]] / fullValue * 100;
    }
    return (
      <Card {...rest} className={clsx(classes.root, className)}>
        <CardContent>
          <Grid container justify="space-between">
            <Grid item>
              <Typography className={classes.title} color="textSecondary" gutterBottom variant="body2">
                {title}
              </Typography>
              <Typography variant="h3">
                {prefix}
                <CountUp
                  end={fullValue}
                  duration={duration}
                  separator=","
                  decimals={0}
                />
                {postfix}
              </Typography>
            </Grid>
          </Grid>
          {progress ? (
            <LinearProgress
              className={classes.progress}
              value={fullValue}
              variant="determinate"
            />
          ) : null}
          {difference ? (
            <div className={classes.difference}>
              <Typography className={classes.differenceValue} variant="body2">
                {value > 1 ? (
                  <span className={classes.green}>{value.toFixed(1)}%</span>
                ) : (
                  <span className={classes.red}>{value.toFixed(1)}%</span>
                )}
              </Typography>
              <Typography className={classes.caption} variant="caption">
                Since this year
              </Typography>
            </div>
          ) : null}
        </CardContent>
      </Card>
    );
  }
};

KPIChart.propTypes = {
  className: PropTypes.string,
  title: PropTypes.string,
};

export default KPIChart;

Let's learn how to create custom measures in the data schema and display their values. In the e-commerce business, it's crucial to know the share of completed orders. To enable our users to monitor this metric, we'll want to display it on the KPI chart. So, we will modify the data schema by adding a custom measure (percentOfCompletedOrders) which will calculate the share based on another measure (completedCount).

Let's customize the "Orders" schema. Open the schema/Orders.js file in the root folder of the Cube.js project and make the following changes:

add the completedCount measure
add the percentOfCompletedOrders measure

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  // ...

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },
    number: {
      sql: `number`,
      type: `sum`
    },
+    completedCount: {
+      sql: `id`,
+      type: `count`,
+      filters: [
+        { sql: `${CUBE}.status = 'completed'` }
+      ]
+    },
+    percentOfCompletedOrders: {
+      sql: `${completedCount}*100.0/${count}`,
+      type: `number`,
+      format: `percent`
+    }
  },

    // ...
});

Now we're ready to add the KPI chart displaying a number of KPIs to the dashboard. Make the following changes to the src/pages/DashboardPage.js file:

// ...

+ import KPIChart from '../components/KPIChart';
import BarChart from '../components/BarChart.js'

// ...

+ const cards = [
+  {
+    title: 'ORDERS',
+    query: { measures: ['Orders.count'] },
+    difference: 'Orders',
+    duration: 1.25,
+  },
+  {
+    title: 'TOTAL USERS',
+    query: { measures: ['Users.count'] },
+    difference: 'Users',
+    duration: 1.5,
+  },
+  {
+    title: 'COMPLETED ORDERS',
+    query: { measures: ['Orders.percentOfCompletedOrders'] },
+    progress: true,
+    duration: 1.75,
+  },
+  {
+    title: 'TOTAL PROFIT',
+    query: { measures: ['LineItems.price'] },
+    duration: 2.25,
+  },
+ ];

const Dashboard = () => {
  const classes = useStyles();
  return (
    <div className={classes.root}>
      <Grid
        container
        spacing={4}
      >
+        {cards.map((item, index) => {
+         return (
+           <Grid
+             key={item.title + index}
+             item
+             lg={3}
+             sm={6}
+             xl={3}
+             xs={12}
+           >
+             <KPIChart {...item}/>
+           </Grid>
+         )
+       })}
        <Grid
          item
          lg={8}
          md={12}
          xl={9}
          xs={12}
        >
          <BarChart/>
        </Grid>
      </Grid>
    </div>
  );
};

Great! 🎉 Now our dashboard has a row of nice and informative KPI metrics:

Doughnut Chart

Now, using the KPI chart, our users are able to monitor the share of completed orders. However, there are two more kinds of orders: "processed" orders (ones that were acknowledged but not yet shipped) and "shipped" orders (essentially, ones that were taken for delivery but not yet completed).

To enable our users to monitor all these kinds of orders, we'll want to add one final chart to our dashboard. It's best to use the Doughnut chart for that, because it's quite useful to visualize the distribution of a certain metric between several states (e.g., all kinds of orders).

First, just like in the previous part, we're going to put the chart options to a separate file. Let's create the src/helpers/DoughnutOptions.js file with the following contents:

import palette from "../theme/palette";
export const DoughnutOptions = {
  legend: {
    display: false
  },
  responsive: true,
  maintainAspectRatio: false,
  cutoutPercentage: 80,
  layout: { padding: 0 },
  tooltips: {
    enabled: true,
    mode: "index",
    intersect: false,
    borderWidth: 1,
    borderColor: palette.divider,
    backgroundColor: palette.white,
    titleFontColor: palette.text.primary,
    bodyFontColor: palette.text.secondary,
    footerFontColor: palette.text.secondary
  }
};

Then, let's create the src/components/DoughnutChart.js for the new chart with the following contents:

import React from 'react';
import { Doughnut } from 'react-chartjs-2';
import clsx from 'clsx';
import PropTypes from 'prop-types';
import { makeStyles, useTheme } from '@material-ui/styles';
import { Card, CardHeader, CardContent, Divider, Typography } from '@material-ui/core';
import { useCubeQuery } from '@cubejs-client/react';
import CircularProgress from '@material-ui/core/CircularProgress';
import { DoughnutOptions } from '../helpers/DoughnutOptions.js';

const useStyles = makeStyles((theme) => ({
  root: {
    height: '100%',
  },
  chartContainer: {
    marginTop: theme.spacing(3),
    position: 'relative',
    height: '300px',
  },
  stats: {
    marginTop: theme.spacing(2),
    display: 'flex',
    justifyContent: 'center',
  },
  status: {
    textAlign: 'center',
    padding: theme.spacing(1),
  },
  title: {
    color: theme.palette.text.secondary,
    paddingBottom: theme.spacing(1),
  },
  statusIcon: {
    color: theme.palette.icon,
  },
}));

const DoughnutChart = (props) => {
  const { className, query, ...rest } = props;

  const classes = useStyles();
  const theme = useTheme();

  const { resultSet, error, isLoading } = useCubeQuery(query);
  if (isLoading) {
    return (
      <div style={{ display: 'flex', alignItems: 'center', justifyContent: 'center' }}>
        <CircularProgress color="secondary" />
      </div>
    );
  }
  if (error) {
    return <pre>{error.toString()}</pre>;
  }
  if (!resultSet) {
    return null
  }
  if (resultSet) {
    const COLORS_SERIES = [
      theme.palette.secondary.light,
      theme.palette.secondary.lighten,
      theme.palette.secondary.main,
    ];
    const data = {
      labels: resultSet.categories().map((c) => c.category),
      datasets: resultSet.series().map((s) => ({
        label: s.title,
        data: s.series.map((r) => r.value),
        backgroundColor: COLORS_SERIES,
        hoverBackgroundColor: COLORS_SERIES,
      })),
    };
    const reducer = (accumulator, currentValue) => accumulator + currentValue;
    return (
      <Card {...rest} className={clsx(classes.root, className)}>
        <CardHeader title="Orders status" />
        <Divider />
        <CardContent>
          <div className={classes.chartContainer}>
            <Doughnut data={data} options={DoughnutOptions} />
          </div>
          <div className={classes.stats}>
            {resultSet.series()[0].series.map((status) => (
              <div className={classes.status} key={status.category}>
                <Typography variant="body1" className={classes.title}>
                  {status.category}
                </Typography>
                <Typography variant="h2">{((status.value/resultSet.series()[0].series.map(el => el.value).reduce(reducer)) * 100).toFixed(0)}%</Typography>
              </div>
            ))}
          </div>
        </CardContent>
      </Card>
    );
  }
};

DoughnutChart.propTypes = {
  className: PropTypes.string,
};

export default DoughnutChart;

The last step is to add the new chart to the dashboard. Let's modify the src/pages/DashboardPage.js file:

// ...

import DataCard from '../components/DataCard';
import BarChart from '../components/BarChart.js'
+ import DoughnutChart from '../components/DoughnutChart.js'

// ...

+ const doughnutChartQuery = {
+  measures: ['Orders.count'],
+  timeDimensions: [
+    {
+      dimension: 'Orders.createdAt',
+    },
+  ],
+  filters: [],
+  dimensions: ['Orders.status'],
+ };

//...

return (
    <div className={classes.root}>
      <Grid
        container
        spacing={4}
      >

        // ...

+        <Grid
+          item
+          lg={4}
+          md={6}
+          xl={3}
+          xs={12}
+        >
+          <DoughnutChart query={doughnutChartQuery}/>
+        </Grid>
      </Grid>
    </div>
  );

Awesome! 🎉 Now the first page of our dashboard is complete:

If you like the layout of our dashboard, check out the Devias Kit Admin Dashboard, an open source React Dashboard made with Material UI's components.

Multi-Page Dashboard with Data Table

Now we have a single-page dashboard that displays aggregated business metrics and provides at-a-glance view of several KPIs. However, there's no way to get information about a particular order or a range of orders.

We're going to fix it by adding a second page to our dashboard with the information about all orders. On that page, we'll use the Data Table component from Material UI which is great for displaying tabular data. It privdes many rich features like sorting, searching, pagination, inline-editing, and row selection.

However, we'll need a way to navigate between two pages. So, let's add a navigation side bar.

Navigation Side Bar

First, let's downaload a pre-built layout and images for our dashboard application. Run these commands, extract the layout.zip file to the src/layouts folder, and the images.zip file to the public/images folder:

curl -LJO https://github.com/cube-js/cube.js/tree/master/examples/material-ui-dashboard/dashboard-app/src/layouts/layouts.zip
curl -LJO https://github.com/cube-js/cube.js/tree/master/examples/material-ui-dashboard/dashboard-app/public/images/images.zip

Now we can add this layout to the application. Let's modify the src/App.js file:

// ...

import 'typeface-roboto';
- import Header from "./components/Header";
+ import { Main } from './layouts'

// ...

const AppLayout = ({children}) => {
  const classes = useStyles();
  return (
    <ThemeProvider theme={theme}>
+      <Main>
        <div className={classes.root}>
-         <Header/>
          <div>{children}</div>
        </div>
+      </Main>
    </ThemeProvider>
  );
};

Wow! 🎉 Here's our navigation side bar which can be used to switch between different pages of the dashboard:

Data Table for Orders

To fetch data for the Data Table, we'll need to customize the data schema and define a number of new metrics: amount of items in an order (its size), an order's price, and a user's full name.

First, let's add the full name in the "Users" schema in the schema/Users.js file:

cube(`Users`, {
  sql: `SELECT * FROM public.users`,

    // ...

  dimensions: {    

        // ...

    firstName: {
      sql: `first_name`,
      type: `string`
    },

    lastName: {
      sql: `last_name`,
      type: `string`
    },

+    fullName: {
+      sql: `CONCAT(${firstName}, ' ', ${lastName})`,
+      type: `string`
+    },

    age: {
      sql: `age`,
      type: `number`
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  }
});

Then, let's add other measures to the "Orders" schema in the schema/Orders.js file.

For these measures, we're going to use the subquery feature of Cube.js. You can use subquery dimensions to reference measures from other cubes inside a dimension. Here's how to defined such dimensions:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
+      shown: true
    },

    status: {
      sql: `status`,
      type: `string`
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    },

+    size: {
+      sql: `${LineItems.count}`,
+      subQuery: true,
+      type: 'number'
+    },
+
+    price: {
+      sql: `${LineItems.price}`,
+      subQuery: true,
+      type: 'number'
+    }
  }
});

Now we're ready to add a new page. Open the src/index.js file and add a new route and a default redirect:

import React from "react";
import ReactDOM from "react-dom";
import "./index.css";
import App from "./App";
import * as serviceWorker from "./serviceWorker";
- import { HashRouter as Router, Route } from "react-router-dom";
+ import { HashRouter as Router, Route, Switch, Redirect } from "react-router-dom";
import DashboardPage from "./pages/DashboardPage";
+ import DataTablePage from './pages/DataTablePage';

ReactDOM.render(
  <React.StrictMode>
    <Router>
      <App>
-               <Route key="index" exact path="/" component={DashboardPage} />
+        <Switch>
+         <Redirect exact from="/" to="/dashboard"/>
+          <Route key="index" exact path="/dashboard" component={DashboardPage} />
+          <Route key="table" path="/orders" component={DataTablePage} />
+          <Redirect to="/dashboard" />
+        </Switch>
      </App>
    </Router>
  </React.StrictMode>,
  document.getElementById("root")
); 

serviceWorker.unregister();

The next step is to create the page referenced in the new route. Add the src/pages/DataTablePage.js file with the following contents:

import React from "react";
import { makeStyles } from "@material-ui/styles";

import Table from "../components/Table.js";

const useStyles = makeStyles(theme => ({
  root: {
    padding: theme.spacing(4)
  },
  content: {
    marginTop: 15
  },
}));

const DataTablePage = () => {
  const classes = useStyles();

  const query = {
    "limit": 500,
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
        "granularity": "day"
      }
    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ]
  };

  return (
    <div className={classes.root}>
      <div className={classes.content}>
        <Table query={query}/>
      </div>
    </div>
  );
};

export default DataTablePage;

Note that this component contains a Cube.js query. Later, we'll modify this query to enable filtering of the data.

All data items are rendered with the <Table /> component, and changes to the query result are reflected in the table. Let's create this <Table /> component in the src/components/Table.js file with the following contents:

import React, { useState } from "react";
import clsx from "clsx";
import PropTypes from "prop-types";
import moment from "moment";
import PerfectScrollbar from "react-perfect-scrollbar";
import { makeStyles } from "@material-ui/styles";
import { useCubeQuery } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";
import {
  Card,
  CardActions,
  CardContent,
  Table,
  TableBody,
  TableCell,
  TableHead,
  TableRow,
  TablePagination, Typography
} from "@material-ui/core";

import StatusBullet from "./StatusBullet";
import palette from "../theme/palette";

const useStyles = makeStyles(theme => ({
  root: {
    padding: 0
  },
  content: {
    padding: 0
  },
  head: {
    backgroundColor: palette.background.gray
  },
  inner: {
    minWidth: 1050
  },
  nameContainer: {
    display: "flex",
    alignItems: "baseline"
  },
  status: {
    marginRight: theme.spacing(2)
  },
  actions: {
    justifyContent: "flex-end"
  },
}));

const statusColors = {
  completed: "success",
  processing: "info",
  shipped: "danger"
};

const TableComponent = props => {

  const { className, query, cubejsApi, ...rest } = props;

  const classes = useStyles();

  const [rowsPerPage, setRowsPerPage] = useState(10);
  const [page, setPage] = useState(0);

  const tableHeaders = [
    {
      text: "Order id",
      value: "Orders.id"
    },
    {
      text: "Orders size",
      value: "Orders.size"
    },
    {
      text: "Full Name",
      value: "Users.fullName"
    },
    {
      text: "User city",
      value: "Users.city"
    },
    {
      text: "Order price",
      value: "Orders.price"
    },
    {
      text: "Status",
      value: "Orders.status"
    },
    {
      text: "Created at",
      value: "Orders.createdAt"
    }
  ];
  const { resultSet, error, isLoading } = useCubeQuery(query, { cubejsApi });
  if (isLoading) {
    return <div style={{display: 'flex', alignItems: 'center', justifyContent: 'center'}}><CircularProgress color="secondary" /></div>;
  }
  if (error) {
    return <pre>{error.toString()}</pre>;
  }
  if (resultSet) {
    let orders = resultSet.tablePivot();

    const handlePageChange = (event, page) => {
      setPage(page);
    };
    const handleRowsPerPageChange = event => {
      setRowsPerPage(event.target.value);
    };

    return (
      <Card
        {...rest}
        padding={"0"}
        className={clsx(classes.root, className)}
      >
        <CardContent className={classes.content}>
          <PerfectScrollbar>
            <div className={classes.inner}>
              <Table>
                <TableHead className={classes.head}>
                  <TableRow>
                    {tableHeaders.map((item) => (
                      <TableCell key={item.value + Math.random()} 
                                                                 className={classes.hoverable}           
                      >
                        <span>{item.text}</span>

                      </TableCell>
                    ))}
                  </TableRow>
                </TableHead>
                <TableBody>
                  {orders.slice(page * rowsPerPage, page * rowsPerPage + rowsPerPage).map(obj => (
                    <TableRow
                      className={classes.tableRow}
                      hover
                      key={obj["Orders.id"]}
                    >
                      <TableCell>
                        {obj["Orders.id"]}
                      </TableCell>
                      <TableCell>
                        {obj["Orders.size"]}
                      </TableCell>
                      <TableCell>
                        {obj["Users.fullName"]}
                      </TableCell>
                      <TableCell>
                        {obj["Users.city"]}
                      </TableCell>
                      <TableCell>
                        {"$ " + obj["Orders.price"]}
                      </TableCell>
                      <TableCell>
                        <StatusBullet
                          className={classes.status}
                          color={statusColors[obj["Orders.status"]]}
                          size="sm"
                        />
                        {obj["Orders.status"]}
                      </TableCell>
                      <TableCell>
                        {moment(obj["Orders.createdAt"]).format("DD/MM/YYYY")}
                      </TableCell>
                    </TableRow>
                  ))}
                </TableBody>
              </Table>
            </div>
          </PerfectScrollbar>
        </CardContent>
        <CardActions className={classes.actions}>
          <TablePagination
            component="div"
            count={orders.length}
            onChangePage={handlePageChange}
            onChangeRowsPerPage={handleRowsPerPageChange}
            page={page}
            rowsPerPage={rowsPerPage}
            rowsPerPageOptions={[5, 10, 25, 50, 100]}
          />
        </CardActions>
      </Card>
    );
  } else {
    return null
  }
};

TableComponent.propTypes = {
  className: PropTypes.string,
  query: PropTypes.object.isRequired
};

export default TableComponent;

The table contains a cell with a custom <StatusBullet /> component which displays an order's status with a colorful dot. Let's create this component in the src/components/StatusBullet.js file with the following contents:

import React from 'react';
import PropTypes from 'prop-types';
import clsx from 'clsx';
import { makeStyles } from '@material-ui/styles';

const useStyles = makeStyles(theme => ({
  root: {
    display: 'inline-block',
    borderRadius: '50%',
    flexGrow: 0,
    flexShrink: 0
  },
  sm: {
    height: theme.spacing(1),
    width: theme.spacing(1)
  },
  md: {
    height: theme.spacing(2),
    width: theme.spacing(2)
  },
  lg: {
    height: theme.spacing(3),
    width: theme.spacing(3)
  },
  neutral: {
    backgroundColor: theme.palette.neutral
  },
  primary: {
    backgroundColor: theme.palette.primary.main
  },
  info: {
    backgroundColor: theme.palette.info.main
  },
  warning: {
    backgroundColor: theme.palette.warning.main
  },
  danger: {
    backgroundColor: theme.palette.error.main
  },
  success: {
    backgroundColor: theme.palette.success.main
  }
}));

const StatusBullet = props => {
  const { className, size, color, ...rest } = props;

  const classes = useStyles();

  return (
    <span
      {...rest}
      className={clsx(
        {
          [classes.root]: true,
          [classes[size]]: size,
          [classes[color]]: color
        },
        className
      )}
    />
  );
};

StatusBullet.propTypes = {
  className: PropTypes.string,
  color: PropTypes.oneOf([
    'neutral',
    'primary',
    'info',
    'success',
    'warning',
    'danger'
  ]),
  size: PropTypes.oneOf(['sm', 'md', 'lg'])
};

StatusBullet.defaultProps = {
  size: 'md',
  color: 'default'
};

export default StatusBullet;

Nice! 🎉 Now we have a table which displays information about all orders:

However, its hard to explore this orders using only the controls provided. To fix this, we'll add a comprehensive toolbar with filters and make our table interactive.

First, let's add a few dependencies. Run the command in the dashboard-app folder:

npm install --save @date-io/date-fns@1.x date-fns @date-io/moment@1.x moment @material-ui/lab/Autocomplete

Then, create the <Toolbar /> component in the src/components/Toolbar.js file with the following contents:

import "date-fns";
import React from "react";
import PropTypes from "prop-types";
import clsx from "clsx";
import { makeStyles } from "@material-ui/styles";
import Grid from "@material-ui/core/Grid";
import Typography from "@material-ui/core/Typography";
import Tab from "@material-ui/core/Tab";
import Tabs from "@material-ui/core/Tabs";
import withStyles from "@material-ui/core/styles/withStyles";
import palette from "../theme/palette";

const AntTabs = withStyles({
  root: {
    borderBottom: `1px solid ${palette.primary.main}`,
  },
  indicator: {
    backgroundColor: `${palette.primary.main}`,
  },
})(Tabs);
const AntTab = withStyles((theme) => ({
  root: {
    textTransform: 'none',
    minWidth: 25,
    fontSize: 12,
    fontWeight: theme.typography.fontWeightRegular,
    marginRight: theme.spacing(0),
    color: palette.primary.dark,
    opacity: 0.6,
    '&:hover': {
      color: `${palette.primary.main}`,
      opacity: 1,
    },
    '&$selected': {
      color: `${palette.primary.main}`,
      fontWeight: theme.typography.fontWeightMedium,
      outline: 'none',
    },
    '&:focus': {
      color: `${palette.primary.main}`,
      outline: 'none',
    },
  },
  selected: {},
}))((props) => <Tab disableRipple {...props} />);
const useStyles = makeStyles(theme => ({
  root: {},
  row: {
    marginTop: theme.spacing(1)
  },
  spacer: {
    flexGrow: 1
  },
  importButton: {
    marginRight: theme.spacing(1)
  },
  exportButton: {
    marginRight: theme.spacing(1)
  },
  searchInput: {
    marginRight: theme.spacing(1)
  },
  formControl: {
    margin: 25,
    fullWidth: true,
    display: "flex",
    wrap: "nowrap"
  },
  date: {
    marginTop: 3
  },
  range: {
    marginTop: 13
  }
}));

const Toolbar = props => {
  const { className,
    statusFilter,
    setStatusFilter,
    tabs,
    ...rest } = props;
  const [tabValue, setTabValue] = React.useState(statusFilter);

  const classes = useStyles();

  const handleChangeTab = (e, value) => {
    setTabValue(value);
    setStatusFilter(value);
  };

  return (
    <div
      {...rest}
      className={clsx(classes.root, className)}
    >
      <Grid container spacing={4}>
        <Grid
          item
          lg={3}
          sm={6}
          xl={3}
          xs={12}
          m={2}
        >
          <div className={classes}>
            <AntTabs value={tabValue} onChange={(e,value) => {handleChangeTab(e,value)}} aria-label="ant example">
              {tabs.map((item) => (<AntTab key={item} label={item} />))}
            </AntTabs>
            <Typography className={classes.padding} />
          </div>
        </Grid>
      </Grid>
    </div>
  );
};

Toolbar.propTypes = {
  className: PropTypes.string
};

export default Toolbar;

Note that we have customized the <Tab /> component with styles and and the setStatusFilter method which is passed via props. Now we can add this component, props, and filter to the parent component. Let's modify the src/pages/DataTablePage.js file:

import React from "react";
import { makeStyles } from "@material-ui/styles";

+ import Toolbar from "../components/Toolbar.js";
import Table from "../components/Table.js";

const useStyles = makeStyles(theme => ({
  root: {
    padding: theme.spacing(4)
  },
  content: {
    marginTop: 15
  },
}));

const DataTablePage = () => {
  const classes = useStyles();
+  const tabs = ['All', 'Shipped', 'Processing', 'Completed'];
+  const [statusFilter, setStatusFilter] = React.useState(0);

  const query = {
    "limit": 500,
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
        "granularity": "day"
      }
    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ],
+    "filters": [
+      {
+        "dimension": "Orders.status",
+        "operator": tabs[statusFilter] !== 'All' ? "equals" : "set",
+        "values": [
+          `${tabs[statusFilter].toLowerCase()}`
+        ]
+      }
+    ]
  };

  return (
    <div className={classes.root}>
+      <Toolbar
+        statusFilter={statusFilter}
+        setStatusFilter={setStatusFilter}
+        tabs={tabs}
+      />
      <div className={classes.content}>
        <Table
          query={query}/>
      </div>
    </div>
  );
};

export default DataTablePage;

Perfect! 🎉 Now the data table has a filter which switches between different types of orders:

However, orders have other parameters such as price and dates. Let's create filters for these parameters. To do so, modify the src/components/Toolbar.js file:

import "date-fns";
import React from "react";
import PropTypes from "prop-types";
import clsx from "clsx";
import { makeStyles } from "@material-ui/styles";
import Grid from "@material-ui/core/Grid";
import Typography from "@material-ui/core/Typography";
import Tab from "@material-ui/core/Tab";
import Tabs from "@material-ui/core/Tabs";
import withStyles from "@material-ui/core/styles/withStyles";
import palette from "../theme/palette";
+ import DateFnsUtils from "@date-io/date-fns";
+ import {
+   MuiPickersUtilsProvider,
+   KeyboardDatePicker
+ } from "@material-ui/pickers";
+ import Slider from "@material-ui/core/Slider";

// ...

const Toolbar = props => {
  const { className,
+   startDate,
+   setStartDate,
+   finishDate,
+   setFinishDate,
+   priceFilter,
+   setPriceFilter,
    statusFilter,
    setStatusFilter,
    tabs,
    ...rest } = props;
  const [tabValue, setTabValue] = React.useState(statusFilter);
+ const [rangeValue, rangeSetValue] = React.useState(priceFilter);

  const classes = useStyles();

  const handleChangeTab = (e, value) => {
    setTabValue(value);
    setStatusFilter(value);
  };
+  const handleDateChange = (date) => {
+    setStartDate(date);
+  };
+  const handleDateChangeFinish = (date) => {
+    setFinishDate(date);
+  };
+ const handleChangeRange = (event, newValue) => {
+   rangeSetValue(newValue);
+ };
+ const setRangeFilter = (event, newValue) => {
+   setPriceFilter(newValue);
+ };

  return (
    <div
      {...rest}
      className={clsx(classes.root, className)}
    >
      <Grid container spacing={4}>
        <Grid
          item
          lg={3}
          sm={6}
          xl={3}
          xs={12}
          m={2}
        >
          <div className={classes}>
            <AntTabs value={tabValue} onChange={(e,value) => {handleChangeTab(e,value)}} aria-label="ant example">
              {tabs.map((item) => (<AntTab key={item} label={item} />))}
            </AntTabs>
            <Typography className={classes.padding} />
          </div>
        </Grid>
+        <Grid
+          className={classes.date}
+          item
+          lg={3}
+          sm={6}
+          xl={3}
+          xs={12}
+          m={2}
+        >
+          <MuiPickersUtilsProvider utils={DateFnsUtils}>
+            <Grid container justify="space-around">
+              <KeyboardDatePicker
+                id="date-picker-dialog"
+               label={<span style={{opacity: 0.6}}>Start Date</span>}
+                format="MM/dd/yyyy"
+                value={startDate}
+                onChange={handleDateChange}
+                KeyboardButtonProps={{
+                  "aria-label": "change date"
+                }}
+              />
+            </Grid>
+          </MuiPickersUtilsProvider>
+        </Grid>
+        <Grid
+          className={classes.date}
+          item
+          lg={3}
+          sm={6}
+          xl={3}
+          xs={12}
+          m={2}
+        >
+          <MuiPickersUtilsProvider utils={DateFnsUtils}>
+            <Grid container justify="space-around">
+              <KeyboardDatePicker
+                id="date-picker-dialog-finish"
+                label={<span style={{opacity: 0.6}}>Finish Date</span>}
+                format="MM/dd/yyyy"
+                value={finishDate}
+                onChange={handleDateChangeFinish}
+                KeyboardButtonProps={{
+                  "aria-label": "change date"
+                }}
+              />
+            </Grid>
+          </MuiPickersUtilsProvider>
+        </Grid>
+        <Grid
+          className={classes.range}
+          item
+          lg={3}
+          sm={6}
+          xl={3}
+          xs={12}
+          m={2}
+        >
+          <Typography id="range-slider">
+            Order price range
+          </Typography>
+          <Slider
+            value={rangeValue}
+            onChange={handleChangeRange}
+            onChangeCommitted={setRangeFilter}
+            aria-labelledby="range-slider"
+            valueLabelDisplay="auto"
+            min={0}
+            max={2000}
+          />
+        </Grid>
      </Grid>
    </div>
  );
};

Toolbar.propTypes = {
  className: PropTypes.string
};

export default Toolbar;

To make these filters work, we need to connect them to the parent component: add state, modify our query, and add new props to the <Toolbar /> component. Also, we will add sorting to the data table. So, modify the src/pages/DataTablePage.js file like this:

// ...

const DataTablePage = () => {
  const classes = useStyles();
  const tabs = ['All', 'Shipped', 'Processing', 'Completed'];
  const [statusFilter, setStatusFilter] = React.useState(0);
+ const [startDate, setStartDate] = React.useState(new Date("2019-01-01T00:00:00"));
+ const [finishDate, setFinishDate] = React.useState(new Date("2022-01-01T00:00:00"));
+ const [priceFilter, setPriceFilter] = React.useState([0, 200]);
+ const [sorting, setSorting] = React.useState(['Orders.createdAt', 'desc']);

  const query = {
    "limit": 500,
+    "order": {
+      [`${sorting[0]}`]: sorting[1]
+    },
    "measures": [
      "Orders.count"
    ],
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
+               "dateRange": [startDate, finishDate],
        "granularity": "day"
      }
    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ],
    "filters": [
      {
        "dimension": "Orders.status",
        "operator": tabs[statusFilter] !== 'All' ? "equals" : "set",
        "values": [
          `${tabs[statusFilter].toLowerCase()}`
        ]
      },
+     {
+        "dimension": "Orders.price",
+        "operator": "gt",
+        "values": [
+         `${priceFilter[0]}`
+       ]
+     },
+     {
+       "dimension": "Orders.price",
+       "operator": "lt",
+       "values": [
+         `${priceFilter[1]}`
+       ]
+     },
    ]
  };

  return (
    <div className={classes.root}>
      <Toolbar
+       startDate={startDate}
+       setStartDate={setStartDate}
+       finishDate={finishDate}
+       setFinishDate={setFinishDate}
+       priceFilter={priceFilter}
+       setPriceFilter={setPriceFilter}
        statusFilter={statusFilter}
        setStatusFilter={setStatusFilter}
        tabs={tabs}
      />
      <div className={classes.content}>
        <Table
+          sorting={sorting}
+          setSorting={setSorting}
          query={query}/>
      </div>
    </div>
  );
};

export default DataTablePage;

Fantastic! 🎉 We've added some useful filters. Indeed, you can add even more filters with custom logic. See the documentation for the filter format options.

And there's one more thing. We've added sorting props to the toolbar, but we also need to pass them to the <Table /> component. To fix this, let's modify the src/components/Table.js file:

// ...

+ import KeyboardArrowUpIcon from "@material-ui/icons/KeyboardArrowUp";
+ import KeyboardArrowDownIcon from "@material-ui/icons/KeyboardArrowDown";
import { useCubeQuery } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";

// ...

const useStyles = makeStyles(theme => ({
  // ...
  actions: {
    justifyContent: "flex-end"
  },
+ tableRow: {
+   padding: '0 5px',
+   cursor: "pointer",
+   '.MuiTableRow-root.MuiTableRow-hover&:hover': {
+     backgroundColor: palette.primary.action
+   }
+ },
+ hoverable: {
+   "&:hover": {
+     color: `${palette.primary.normal}`,
+     cursor: `pointer`
+   }
+ },
+ arrow: {
+   fontSize: 10,
+   position: "absolute"
+ }
}));

const statusColors = {
  completed: "success",
  processing: "info",
  shipped: "danger"
};

const TableComponent = props => {
-  const { className, query, cubejsApi, ...rest } = props;
+  const { className, sorting, setSorting, query, cubejsApi, ...rest } = props;

// ...

  if (resultSet) {

       //...
+     const handleSetSorting = str => {
+       setSorting([str, sorting[1] === "desc" ? "asc" : "desc"]);
+     };

    return (

                                // ...
                <TableHead className={classes.head}>
                  <TableRow>
                    {tableHeaders.map((item) => (
                      <TableCell key={item.value + Math.random()} className={classes.hoverable}
+                                 onClick={() => {
+                                 handleSetSorting(`${item.value}`);
+                                 }}
                      >
                        <span>{item.text}</span>
+                        <Typography
+                          className={classes.arrow}
+                          variant="body2"
+                          component="span"
+                        >
+                          {(sorting[0] === item.value) ? (sorting[1] === "desc" ? <KeyboardArrowUpIcon/> :
+                            <KeyboardArrowDownIcon/>) : null}
+                        </Typography>
                      </TableCell>
                    ))}
                  </TableRow>
                </TableHead>
                         // ...

Wonderful! 🎉 Now we have the data table that fully supports filtering and sorting:

User Drill Down Page

The data table we've built allows to find informations about a particular order. However, our e-commerce business is quite successful and has a good return rate which means that users are highly likely to make multiple orders over time. So, let's add a drill down page to explore the complete order informations for a particular user.

As it's a new page, let's add a new route to the src/index.js file:

// ...

    <Switch>
          <Redirect exact from="/" to="/dashboard" />
          <Route key="index" exact path="/dashboard" component={DashboardPage} />
          <Route key="table" path="/orders" component={DataTablePage} />
+         <Route key="table" path="/user/:id" component={UsersPage} />
          <Redirect to="/dashboard" />
        </Switch>

// ...

For this route to work, we also need to add the src/pages/UsersPage.js file with these contents:

import React from 'react';
import { useParams } from 'react-router-dom';
import { makeStyles } from '@material-ui/styles';
import { useCubeQuery } from '@cubejs-client/react';
import { Grid } from '@material-ui/core';
import AccountProfile from '../components/AccountProfile';
import BarChart from '../components/BarChart';
import CircularProgress from '@material-ui/core/CircularProgress';
import UserSearch from '../components/UserSearch';
import KPIChart from '../components/KPIChart';

const useStyles = makeStyles((theme) => ({
  root: {
    padding: theme.spacing(4),
  },
  row: {
    display: 'flex',
    margin: '0 -15px',
  },
  info: {
    paddingLeft: theme.spacing(2),
    paddingRight: theme.spacing(2),
  },
  sales: {
    marginTop: theme.spacing(4),
  },
  loaderWrap: {
    width: '100%',
    height: '100%',
    minHeight: 'calc(100vh - 64px)',
    display: 'flex',
    alignItems: 'center',
    justifyContent: 'center',
  },
}));

const UsersPage = (props) => {
  const classes = useStyles();
  let { id } = useParams();
  const query = {
    measures: ['Users.count'],
    timeDimensions: [
      {
        dimension: 'Users.createdAt',
      },
    ],
    dimensions: [
      'Users.id',
      'Products.id',
      'Users.firstName',
      'Users.lastName',
      'Users.gender',
      'Users.age',
      'Users.city',
      'LineItems.itemPrice',
      'Orders.createdAt',
    ],
    filters: [
      {
        dimension: 'Users.id',
        operator: 'equals',
        values: [`${id}`],
      },
    ],
  };
  const barChartQuery = {
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        granularity: 'month',
        dateRange: 'This week',
      },
    ],
    dimensions: ['Orders.status'],
    filters: [
      {
        dimension: 'Users.id',
        operator: 'equals',
        values: [id],
      },
    ],
  };
  const cards = [
    {
      title: 'ORDERS',
      query: {
        measures: ['Orders.count'],
        filters: [
          {
            dimension: 'Users.id',
            operator: 'equals',
            values: [`${id}`],
          },
        ],
      },
      duration: 1.25,
    },
    {
      title: 'TOTAL SALES',
      query: {
        measures: ['LineItems.price'],
        filters: [
          {
            dimension: 'Users.id',
            operator: 'equals',
            values: [`${id}`],
          },
        ],
      },
      duration: 1.5,
    },
  ];

  const { resultSet, error, isLoading } = useCubeQuery(query);
  if (isLoading) {
    return (
      <div style={{ display: 'flex', alignItems: 'center', justifyContent: 'center' }}>
        <CircularProgress color="secondary" />
      </div>
    );
  }
  if (error) {
    return <pre>{error.toString()}</pre>;
  }
  if (!resultSet) {
    return null;
  }
  if (resultSet) {
    let data = resultSet.tablePivot();
    let userData = data[0];
    return (
      <div className={classes.root}>
        <Grid container spacing={4}>
          <Grid item lg={4} sm={6} xl={4} xs={12}>
            <UserSearch />
            <AccountProfile
              userFirstName={userData['Users.firstName']}
              userLastName={userData['Users.lastName']}
              gender={userData['Users.gender']}
              age={userData['Users.age']}
              city={userData['Users.city']}
              id={id}
            />
          </Grid>
          <Grid item lg={8} sm={6} xl={4} xs={12}>
            <div className={classes.row}>
              {cards.map((item, index) => {
                return (
                  <Grid className={classes.info} key={item.title + index} item lg={6} sm={6} xl={6} xs={12}>
                    <KPIChart {...item} />
                  </Grid>
                );
              })}
            </div>
            <div className={classes.sales}>
              <BarChart query={barChartQuery} dates={['This year', 'Last year']} />
            </div>
          </Grid>
        </Grid>
      </div>
    );
  }
};

export default UsersPage;

The last thing will be enable the data table to navigate to this page by clicking on a cell with a user's full name. Let's modify the src/components/Table.js like this:

// ...

import KeyboardArrowUpIcon from '@material-ui/icons/KeyboardArrowUp';
import KeyboardArrowDownIcon from '@material-ui/icons/KeyboardArrowDown';
+ import OpenInNewIcon from '@material-ui/icons/OpenInNew';
import { useCubeQuery } from '@cubejs-client/react';
import CircularProgress from '@material-ui/core/CircularProgress';

// ...

                      <TableCell>{obj['Orders.id']}</TableCell>
                      <TableCell>{obj['Orders.size']}</TableCell>
+                     <TableCell
+                       className={classes.hoverable}
+                       onClick={() => handleClick(`/user/${obj['Users.id']}`)}
+                     >
+                       {obj['Users.fullName']}
+                       &nbsp;
+                       <Typography className={classes.arrow} variant="body2" component="span">
+                         <OpenInNewIcon fontSize="small" />
+                       </Typography>
+                     </TableCell>
                      <TableCell>{obj['Users.city']}</TableCell>
                      <TableCell>{'$ ' + obj['Orders.price']}</TableCell>

// ...

Here's what we eventually got:

And that's all! 😇 Congratulations on completing this guide! 🎉

Also, check the live demo and the full source code available on GitHub.

Now you should be able to create comprehensive analytical dashboards powered by Cube.js and using React and Material UI to display aggregate metrics and detailed information.

Feel free to explore other examples of what can be done with Cube.js such as the Real-Time Dashboard Guide and the Open Source Web Analytics Platform Guide.

DEV Community

Material UI Dashboard 📊 with React

Analytics Backend with Cube.js

Frontend with Material UI

Interactive Dashboard with Multiple Charts

Custom Date Range

KPI Chart

Doughnut Chart

Multi-Page Dashboard with Data Table

Navigation Side Bar

Data Table for Orders

User Drill Down Page

Top comments (0)

Read next

Clip-Path Circle Reveal Animation With Mouse Movement

How to setup the favicon of a React app

Express 5 is here, what’s new?

EchoAPI vs Bruno: A Comprehensive Comparison from Design to Testing 💡