View data with a chart and drill down
The following walkthrough shows you how to:
- Choose data to display directly on a dashboard or other view as a table visualization.
- Re-visualize the table as a bar chart.
- Drill down and drill up on the chart.
These steps create a metric set on the dashboard for analyzing the data from your data source. You can work with multiple metric sets at once when editing a view like the dashboard in this example, or you can follow similar steps when editing a single metric set full screen.
The Adventure Works SQL database is used as an example.
2.1. Create a new dashboard
From the main menu, create a new, blank dashboard.
2.2. Add a measure
In the Explore window, locate your data connector and expand it.
Find the database table [Sales].[SalesOrderDetail] and expand it as well.
Drag the OrderQty column to the canvas. By default, the column is displayed as a measure within a new table visualization. Only a single, aggregated number (the sum or total) appears in the table because no hierarchies have been added yet.
The Data Analysis Panel appears to left of the visualization with the new metric set it's based on, which you can use to determine how data is displayed.
When editing a view like this dashboard, you can drag and drop data from any data source to create new metric sets and visualizations, or drag existing metric sets created ahead of time, and display them all together.
2.3. Add a hierarchy
Under the [Sales].[SalesOrderDetail] table, locate the ModifiedDate column and expand it. You'll see a Time Dimension item, which contains date and time hierarchies you can use to group and explore through the individual dates.
Drag the Year > Month > Day hierarchy onto the area labeled Row Header over the table visualization.
The hierarchy is added under Rows in the Data Analysis Panel, and the table visualization is reconfigured to display a row for each year.
We've added the years as a column of row headers along the left, and the OrderQty values are now broken down by year.
2.4. Re-visualize as a chart
Select the table visualization on your dashboard.
From the toolbar click Re-Visualize and select Bar Chart.
The table is re-visualized as a bar chart.
2.5. Drill down and drill up
In order to interact directly with the data and drill down, click View in the toolbar to switch from the current Edit mode. In View mode, the rulers are hidden and the options available in the right-click menu for a visualization are different.
While in View mode, right-click (or long-tap) over one of the bars of the chart. From the context menu that appears, select Drill Down.
The chart now displays date members that are one hierarchy level down and which correspond to the bar that you just drilled down. You can continue drilling down further, or drill back up using the same context menu.
This is one of several options for exploring data that anyone can access from the context menu when viewing a dashboard or other view, without necessarily needing to edit a metric set directly. For more details, see Metric set analysis tools.
To go back to Edit mode, go to the toolbar and click Edit.
Now you can also Check In your dashboard for others to see it, or you can share a link to it. Everyone who views this dashboard can access the same drill down options and other View mode interactivity (unless these are restricted by application privileges or in properties).