Foxvillage239

This article covers how to create dashboard widgets to filter data in SQL questions. Figure 1 shows the dashboard we’ll be building:

1. Metabase Sample Dashboard Template
2. Metabase Sample Dashboard Examples
3. Metabase Pricing
4. What Is Metabase

This dashboard features:

• two questions written in SQL,
• two filter widgets: one Date filter and one State filter.

Selecting a value in either filter (or both filters) will update the results in both questions.

Our goal, or vision quest, is to understand how to connect dashboard filter widgets to a special variable type, called Field Filters, that we insert into the SQL code of our questions. Here’s our itinerary:

• Metabase Enterprise Edition also ships with all the improvements included with the Open Source Edition, 0.39. Customize filter values for each dashboard subscription. In Metabase Enterprise Edition, when setting up a dashboard subscription you can now specify different values for each filter in the dashboard.
• In the top right of the screen, click the + icon to open the Create menu, and select New Dashboard. Give your new dashboard a name and a description, choose which collection the dashboard should go in, then click Create, and Metabase will take you to your shiny new dashboard.
• How To Use Metabase for Product ManagementOther Udemy Course: Build a.

• Adding a Field Filter variable to a SQL question.
• A detour on data types.
• Connecting a dashboard filter widget to a Field Filter variable.
• Filtering multiple SQL questions on a dashboard.
• Adding another filter widget to the dashboard.

For some background, check out our previous articles on SQL variables and (especially) Field Filters.

The simplest, fastest way to get business intelligence and analytics to everyone in your company:yum: - metabase/metabase. Fix failing Cypress test in `dashboard-drill` spec file. Assert that the page content rendered (as per @dacort 's suggestion).

Adding a Field Filter variable to a SQL question

Let’s start with a simple question written in SQL that shows the orders per month from the Sample Dataset included with Metabase. From any Metabase page, in the upper right on the navigation bar, click on the Write SQL icon to visit the native query editor.

Here’s the SQL code:

Now, we’re talking about filtering data, and astute readers might already recognize that we don’t have a filter statement in our SQL code. There is no WHERE clause. Meaning: even if we were to add this question to a dashboard, and add a filter widget to that dashboard, that filter would have no effect on our SQL question, because there would be no designated place in our code for the widget to insert its value.

Let’s fix that by adding a special variable to our SQL question known as a Field Filter.

Field Filters are a special type of variable that map the variable to a field (column) in a table.

Here’s the SQL code we’ll add:

You may notice the lack of an = assignment in the WHERE clause. The reason for this abbreviated syntax is somewhat involved. The short answer is because Field Filters handle some SQL code for you under the hood. For the long answer: refer to our article on Field Filters.

With the WHERE clause in place, our code should now look like this:

Now that we have our variable situated in our SQL code, we need to tell Metabase how to use the variable. When we add a variable to our code, Metabase will slide out the Variables sidebar. We’ll set the Variable type to Field Filter, then map that variable to a field in our database so Metabase can know what kind of filter widget it should add to the question. In this case, we’ll map the variable to the created_at field of the orders table.

Note that we can call the SQL variable whatever we want, but we must map the variable to the appropriate field in order for the dashboard filter to work.

For now, we’ll leave the Filter widget label as “Created at”, and leave the Required? toggle alone. This way, if the variable’s filter widget does not supply a value, the question will run as if the WHERE clause did not exist.

We can also select a Filter widget type, though this widget will only apply to our question. Let’s select the Date Filter type, which is the most expressive of the widgets.

Let’s Save our question. We’ll call it Number of orders per month - SQL.

Next, we’ll need to:

• create a dashboard
• add our question to a dashboard,
• add a different filter widget to that dashboard,
• then connect that dashboard widget to the Field Filter variable in our SQL question.

But first, a detour.

A detour on data types

When we select the created_at field, Metabase knows that the Field type is a Creation timestamp (note the calendar icon). You can learn about the types of fields (columns) each table has by browsing your data.

Administrators can edit the field type, as well as other metadata settings, in the Data Model tab of the Admin Panel. To learn more, check out our documentation on metadata editing.

Connecting a dashboard filter widget to a Field Filter variable

So we have our SQL question (figure 6) with a Field Filter variable in a WHERE clause, and it’s time to add that question to a dashboard.

Let’s create a dashboard (we’ll give our dashboard the wildly unimaginative name Dashboard with filter widgets).

Then we’ll add our SQL question to the dashboard.

Next, we’re going to add a filter widget to our dashboard. In Dashboard edit mode (click on the pencil icon to enter edit mode):

• Click on the filter icon to add a new filter widget to the dashboard.
• Under What do we want to filter, we’ll select Time.
• For What kind of filter?, we’ll select All options, which will add a Date filter to our dashboard.
• Next, we’ll need to connect our widget to the Field Filter variable in our question. Click on the dropdown menu in the center of our question, and select our Created At Field Filter variable (figure 7).
• Click the Done button at the top of the screen.
• Then Save the dashboard.

Now we’re all wired up, and we’re ready to test out our new Date filter. This particular widget type gives us an abundance of options. Let’s choose the Between option to select a date range. In this case, we’ll select the orders for 2019 (between January 1, 2019, and December 31, 2019).

With our dashboard filter set, our SQL question updates to filter for orders where the created_at column falls in the date range specified by the filter widget on the dashboard.

Filtering multiple SQL questions on a dashboard

Let’s add an additional SQL question to our dashboard. To keep it simple, let’s create a question that returns the new accounts created per month. The SQL for this question is similar to our first question:

Once again, for our {{created_at}} variable, we’ll:

• Set the Variable type to Field Filter.
• Set the Field to map to option to the Created_At field of the People table (not the Orders table).
• Leave the Filter widget type as is, or change it to Date Filter (just note that this setting only applies to the filter widget on the question, not the dashboard.)
• Leave the Required? toggle off (as in: do not require the variable).

Save your question, title it Number of new accounts - SQL, and add your question to the dashboard.

Now, the existing Date filter does not automatically connect to the newly added question, Number of new accounts - SQL. If we update the value(s) in the Date filter widget, only the data in the connected question, Number of orders per month - SQL, will update to show the filtered data.

So let’s wire up the new question to the dashboard filter widget:

• Click on the pencil icon to enter dashboard edit mode.
• Select Edit on the Date filter widget.
• Then select the Created At option (the only option) from the dropdown menu at the center of the Number of new accounts - SQL card (See figure 10).
• Save the dashboard.

With both Field Filter variables connected to our dashboard filter widget, when we select a value in the widget, both questions will update their charts with the filtered data.

Adding another filter widget to the dashboard

We now have two questions on our dashboard connected to a single filter. Let’s add another filter. And since we’ve already conquered Time, let’s control Space. Let’s add a widget to the dashboard that allows us to filter by state in the United States.

To start, we’ll need to add a {{state}} variable to the WHERE clause of our Number of orders per month - SQL question.

Next, we’ll specify that our new {{state}} variable is a Field Filter, of type State (see figure 11).

Note: if a widget type is unavailable, and the type of data is compatible with a Field Filter, an administrator may need to explicitly set the field type in the Data Model section. Learn more about editing metadata in our documentation.

Now that our question has access to date from the state column in the people table, we can connect the question’s Field Filter variable, {{state}}, to the dashboard filter widget.

And behold: our dashboard with two SQL questions and two filter widgets: one Date filter and one State filter.

All that’s left to do in order to match the dashboard in figure 1 is to add a text box.

Further reading

v0.39.0.1 / Users Guide / 07 Dashboards

Dashboards

What is a dashboard?

Dashboards group questions and present them on a single page. You can think of dashboards as shareable reports that feature a set of related questions.

A dashboard comprises a set of cards arranged on a grid. These cards can be questions - such as tables, charts, or maps - or cards can be text boxes.

You can add filter widgets to dashboards that filter data across multiple questions, and customize what happens when people click on a chart or a table.

You can make as many dashboards as you want. Go nuts.

How to create a dashboard

In the top right of the screen, click the + icon to open the Create menu, and select New Dashboard. Give your new dashboard a name and a description, choose which collection the dashboard should go in, then click Create, and Metabase will take you to your shiny new dashboard.

If you don’t want to build a dashboard from scratch, or want to experiment by making changes to an existing dashboard without affecting the original, you can duplicate an existing dashboard. From an existing dashboard, click on the … menu in the upper right, and select Duplicate.

Adding saved questions to a dashboard

There are two ways to add questions to a dashboard: from the dashboard, or from the question you want to add.

From a question: you can add a newly saved question to a dashboard directly from the window that pops up after you save the question for the first time. You can also add a question to a dashboard by clicking on the pencil icon next to the name of the question, and selecting Add to dashboard.

From a dashboard: Click on the pencil icon to edit the dashboard. Then click the + icon in the top right of the dashboard editing interface (not the + in the main navigation bar) to add any of your saved questions to the dashboard, regardless of which collection the questions are in.

Metabase Sample Dashboard Template

Once you add a question to your dashboard, it’ll look something like this:

Adding headings or descriptions with text cards

Another neat thing you can do is add text cards to your dashboards. Text cards allow you to include descriptions, explanations, notes, or even images and GIFs to your dashboards. You can also use text cards to create separations between sections of charts in your dashboards, or include links to other dashboards, questions, or websites.

To add a new text card, create a new dashboard (or edit an existing one) and click on the text card button, Aa, in the top-right:

Your new, empty text card will appear. It has two modes: writing and previewing. Toggle between the modes by clicking the eye to preview the card, or the document with pencil icon to edit the card.

You can use Markdown to format the text in your text card, create inline tables or code snippets, or even embed linked images (easy on the GIFs, friends).

Click the eye icon to see what your formatted Markdown will look like when you save the card:

To learn more, see Fun with Markdown in your dashboards.

Arranging cards

Each question on a dashboard is in its own card that you can move around or resize as you see fit. Just click the pencil icon in the top right of a dashboard to enter the dashboard’s editing interface.

Once you’re in edit mode, you’ll see a grid appear. You can move and resize the cards in the dashboard to your liking and they’ll snap to the grid.

• To move a card, just click and drag the card.
• To resize a card, click the handle at the bottom right corner of the card, and drag to resize.
• To remove a card, hover over the card, and click the X icon in the top right corner.

Metabase will automatically update a question’s display to make sure your data looks great at any size you choose.

Changing a question’s visualization settings

Metabase Sample Dashboard Examples

You can change a question’s visualization settings on a dashboard (to add a goal line, for example,) without affecting the original question. Click on the pencil icon to enter dashboard edit mode, hover over the question you want to edit, and click on the palette icon to edit the question’s visualization’s settings.

.

Metabase Pricing

Finding dashboards

You can search for any dashboard (or question, collection, or pulse) by its title in the big search box at the top of Metabase.

After a while, your team might accumulate a lot of dashboards. To make it easier to find dashboards that your team looks at often, you can pin them to the top of a collection. From the collection screen, click and drag a dashboard to the top, or click on the … menu and select Pin this item.

Fullscreen dashboards

After you’ve made your ideal dashboard, you may want to put the dashboard on a TV to help keep your team up to date throughout the day.

To enter fullscreen mode, click the fullscreen icon in the top right of the dashboard (the icon with the arrows pointing in opposite directions).

Once you’ve entered fullscreen mode, you can also switch the dashboard into “Night mode” for higher contrast.

Auto refresh

If your data updates frequently, you can set up your dashboard to refresh automatically by clicking on the clock icon.

You can set your dashboard to update in 1, 5, 10, 15, 30, and 60 minute intervals, depending on how fresh you need the data to be.

Enabling auto refresh will re-run all the queries on the dashboard at the interval you choose, so keep the size of the dashboard and the complexity of the questions in mind when setting up auto refresh.

Combining fullscreen mode and auto refresh is a great way to keep your team in sync with your data throughout the day.

Sharing dashboards with public links

If your Metabase administrator has enabled public sharing on a saved question or dashboard, you can go to that question or dashboard and click on the sharing icon to find its public links.

Public links can be viewed by anyone, even if they don’t have access to Metabase. You can also use the public embedding code to embed your question or dashboard in a simple web page or blog post. Check out examples of simple apps with embedded dashboards in our embedding-reference-apps repository. To learn more about embedding, check out our article on How to use Metabase to deliver analytics to your customers, as well as an article on how to combine branding, Single Sign-On, full app embedding, and data sandboxing to deliver multi-tenant, self-service analytics.

Configuring a dashboard through its URL

You can amend the URL of a dashboard to automatically enter fullscreen, enable night mode, or auto-refresh the dashboard. Customizing the dashboard’s URL allows you to configure the dashboard - even when you do not have any input access to the device where the dashboard will be displayed, like scripted screens, for example.

To configure a dashboard using its URL, you can add the following optional keywords: fullscreen, night, or refresh. Here’s an example:

What Is Metabase

https://metabase.mydomain.com/dash/2#refresh=60&fullscreen&night

The part that says refresh=60 sets the dashboard to automatically refresh every 60 seconds, fullscreen sets it to fullscreen mode, and night sets it to night mode (night mode only works when using fullscreen). Use an ampersand, &, in between keywords, and make sure there’s a hash, #, after the dashboard’s ID number.

There is one important limitation with the fullscreen option: for security reasons, many browsers require user interaction to initiate fullscreen. In those browsers, using the fullscreen option will enable the fullscreen UI in Metabase, but it won’t expand the browser content to fill the screen. To ensure the dashboard occupies the entire screen, either activate fullscreen by clicking the button in the UI, or use the fullscreen URL option and launch the browser in fullscreen or kiosk mode.

Archiving a dashboard

Archiving a dashboard removes the dashboard from searches and collections. Archiving a dashboard does not archive the individual saved questions on it — it just archives the dashboard.

To archive a dashboard, click the pencil icon to enter edit mode, then click the … menu, and select Archive.

To view all archived items, click the menu icon in the top-right of any collection page. You can unarchive a dashboard by clicking the icon of the box with the upward arrow next to that dashboard.

Tips on creating helpful dashboards

To make a great dashboard, you first need to decide what you want the dashboard to tell about your data. What questions will give you insight into what you want to know? It helps to think of a topic or theme for your dashboard — something like “customer satisfaction,” or “second quarter sales goals”.

Some tips:

• Emphasize the most important questions. To draw people’s attention to what matters most, place the most important saved question cards near the top of the dashboard, and/or make them bigger than the other cards,
• Keep dashboards focused. If you have more than 10 cards on a dashboard, think about breaking the dashboard into two separate ones. You don’t want to overwhelm people with too much information, and each dashboard should revolve around one theme or topic. Remember — you can make as many dashboards as you want, so you don’t have to cram everything into just one.
• Add filters to your dashboard. Adding filters to dashboards makes them more useful. For example, instead of your dashboard being full of questions that are restricted to a specific time span, you can make more general questions and use dashboard filters to change the time span you’re looking at.
• Make your dashboards interactive.Customize what happens when users click on a chart or table in your dashboard.

See Making dashboards faster for tips on improving dashboard performance.

Next: Adding dashboard filters

Make your dashboards more flexible and powerful by adding dashboard filters.