AWS Business Intelligence Blog

Build with cross-sheet filters and controls in Amazon QuickSight

Amazon QuickSight is a cloud-powered, serverless, and embeddable business intelligence (BI) service that makes it straightforward to deliver insights to everyone in your organization. As a fully managed service, QuickSight lets you create and publish interactive dashboards that can then be accessed from any device and embedded into your applications, portals, and websites.

Cross-sheet filters and controls overview

QuickSight is expanding filter and control functionality. Previously, a filter could be scoped to a single visual, some visuals, all of the visuals for a dataset on a sheet, or all applicable visuals on a sheet. With the launch of cross-sheet filters and controls, authors can now create, delete, and edit filters that apply to multiple sheets. Previously, cross-sheet filters could be created with parameters in over forty clicks. With the expansion in functionality, they can be created in just five clicks, which shows how much this new feature simplifies setting up filters and controls that impact multiple sheets.

In addition, controls, because they’re tied to a filter’s scope, were limited to a single sheet. Now, you can add or delete controls across multiple sheets at once. You can also edit common properties that apply to all of these cross-sheet controls.

If you’re navigating different sheets on a dashboard, you no longer have to remember the value you set for the filter control on the first sheet, because cross-sheet controls apply changes across all sheets in the filter’s scope.

Set up

In this post, we’re going to start building an analysis using cross-sheet controls and filters. You could also update an existing analysis. You need to pick out a dataset or existing analysis that you want to use. Ensure that the dataset has two fields that have a parent-child relationship such as State and City and filters that you want to use (or have already used) across multiple sheets.

I’m going to use a flight dataset to answer these questions:

  • What flights were delayed? And why?
  • What flights were cancelled or diverted? And why?
  • What flights were on time or even early? And why?

To tackle these questions, I’m going to create one sheet for delayed flights, one sheet for cancelled or diverted flights, and one sheet for on-time flights. Across these three sheets, I want six common filters and controls: carrier, date, origin state, origin city, destination state, and destination city. There are two filters and controls that I only want to be present on the delayed and on-time sheets: distance of the flight and air time for the flight.

If you don’t already know what filters you want to apply for your dataset and what the sheets will cover, then take some time to map that out.

Start building cross-sheet controls

Navigate to QuickSight. Pick out the dataset or analysis and map out what you want to build or update using cross-sheet filters and controls. If you’re building from scratch, create an analysis in QuickSight and add the sheets that you need. In my case: On Time, Delayed, and Cancelled or Diverted. If you have an existing analysis, then navigate to that analysis and delete the repeat filters and controls from all but one of the sheets before starting. Otherwise, you will end up with duplicate filters on each sheet and will need to clean that up at the end.

With the sheets in your analysis set up, we’re ready to create cross-sheet filters and controls.

All sheets

We’ll start with the filters and controls that are going to apply to all sheets in the QuickSight analysis.

  1. If you’re in an existing analysis then you can skip to step 2 if you already the filters you want created. If you do not have filters already created, go to the Filters pane of the analysis by selecting the Filter icon on the top left corner of the analysis and select Add then pick the field you want to create the first filter for. In my case, I will select origin_state_abr. I recommend starting with one of your parent-child fields, because we will cover cascading controls next. Alternatively, you can go to the field you want to create a filter on and choose the three-dot menu to see the option to Add a filter.

    or

  2. With the filter created, the scope defaults to Only this visual. If you have an existing filter, then the scope might be different. Choose the filter to edit.
  3. Update the scope to Cross-sheet (by default this includes all the sheets and all the visuals from the dataset). Selecting Apply cross-dataset will extend the filter to all applicable visuals across datasets.
  4. To create a control, choose the three-dot menu next to the field name. There’s a menu option for Add control. There are three options: Top of all sheets in filter scope, Top of this sheet, and Inside this sheet. Select Top of all sheets in filter scope. You can update the other controls to be on the page for specific sheets as needed later. You can also remove a control from one sheet if the filter is applicable for all sheets but the control isn’t needed on a sheet. This will not impact the location of controls if you already have a control added for an existing filter.

    Note: You can also bulk delete all the controls associated with the filter after they’ve been created.
  5. Now that the control has been added to all the sheets, I want to edit the control. In this case, I’m updating the title to a more readable name. Choose the three-dot menu on the control and select Edit.
  6. I’m going to update the Title of the control, which will update for all the controls on other sheets. You could update the other properties in the Cross-sheet properties section and the same would be true.

In the Format control pane, you can also see which sheets this control is on and where those controls are (Top or Sheet). In this case, all the controls are at on the Top. The Added to sheets modal keeps track of where these cross-sheet controls are and which sheets they’re on.

There are some options that aren’t included in cross-sheet properties. These control properties are in sections of their own, such as Relevant value.

Cascading controls

Relevant Value isn’t a cross-sheet control property. Instead, it’s a property that has to be configured for each control. In my case when I want to create a parent/child relationship between State and City, so I need to configure a cascading control by applying the relevant values on all three sheets for the City controls. Otherwise, the parent/child relationship will only be present on the one sheet I set up the relevant values on. This would create behavior that doesn’t match across sheets.

Let’s create this cascaded control next.

  1. Start by creating a cross-sheet controls following steps 1 through 5 from the previous section and adding a filter and then controls for the other value of the parent-child relationship field. In my case, I’ll do this for origin_city_name.
  2. You should now be in the Format control pane. Go to the Relevant value section.
  3. Select Show relevant values only. In the modal, select the checkbox for the only other control that has been created so far, which for me is Flight Origin State (Drop down). Then choose Update. The relevant values are now configured for the first sheet.
  4. Go to the next sheet and choose the three-dot menu on the control and select Edit. Repeat steps 2 and 3 to configure the relevant values for each of the other sheets.

You should now have cascading controls created. In my case, I have these for State and City on three sheets, so that any state I select will cascade to my City control and show only the relevant cities. As these are cross-sheet controls, if I select WA on my On Time sheet then I will see this selection is matched on the Delayed and Cancelled or Diverted sheets as well. This means the City control on all three sheets will only show the relevant Washington cities based on it being filtered once on the On Time sheet.

Custom sheets

Finally, to create filters and controls that are only applicable to some sheets, we need to use the custom sheet selection. This can be accessed in the Edit filter pane under the filter scope. I start with air_time.

  1. Create a cross-sheet filter on air_time (or your field) as you learned in steps 1- 3 in the All sheets section above. In the edit filter pane, select the cross-sheet scope to access the custom selection of sheets.
  2. Customize the sheets that you want to have this filter apply to.
  3. Now that the filter has been customized, you can add and update controls in the same manner as shown in the All sheets section steps 4-6.

Conclusion

With cross-sheet filters and controls, we removed repetitive work for authors, simplifying the creation process so you can get started building and updating analyses even faster and with less effort than before. When using cross-sheet filters and controls, neither authors nor readers need to remember changes they made to filters or controls across sheets.

To learn more about cross-sheet filters and controls, see documentation. You can also visit the QuickSight Community to ask, answer, and learn about QuickSight, and explore the newest features and resources.

About the Authors

Lillie Atkins is a Senior Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service.