View data with a chart and drill down
Learn how to re-visualize data as a bar chart and perform drill down and drill up operations.
The following walkthrough shows you how to:
- Set up a table visualization with one measure and one hierarchy.
- Modify the default (built-in) time dimension.
- Re-visualize the table (data grid) as a bar chart.
- Drill down and drill up on the chart.
The Adventure Works 2014 SQL database is used as an example.
2.1. Create a new dashboard
Use the main menu to create a new, blank dashboard as shown in the article, Connect to data and view it on a dashboard.
2.2. Add a measure
Click the EXPLORE panel. Locate your data connector and expand it.
Find the native table [Sales].[SalesOrderDetail] and expand it as well.
Drag the OrderQty column to your dashboard. By default, the column is displayed as a measure within a table visualization. Only a single, aggregated value appears in the table because no hierarchies have been specified yet.
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 is container for date-time hierarchies.
Expand the Year > Month > Day hierarchy to see all the hierarchy levels. Drag the Year level to the ROWS field of the Data Analysis Panel.
The table visualization is reconfigured to display year hierarchy members along the rows axis, and the OrderQty values are now broken down by year.
2.4. Modify the default time dimension
The default time dimension in Dundas BI is configured with members going back eight years. Data that falls outside of the time dimension will be marked as Unknown. You can modify the start date of the time dimension as follows.
In the EXPLORE panel, expand the Time Dimensions folder, and then right-click the Time Dimension item.
From the menu, select Check Out.
A small, green checkmark appears beside the time dimension, indicating it is now reserved for you to edit.
Right-click the time dimension again and select Edit.
In the Time Dimension dialog, change the Date Range to start from 2012. Additionally, select the time periods Half Year and Quarter.
Click Submit (checkmark) to save and check in your changes.
Next, select the table visualization on the canvas. On the toolbar, click Data Tools and then click Refresh Data.
The table visualization on your dashboard is updated to reflect the change you made to the default time dimension. In particular, you will see that the data for the year 2011 now appears as Unknown.
2.5. Re-visualize as a chart
Select the table visualization on your dashboard.
From the toolbar click Re-Visualize and select Bar.
The table is re-visualized as a bar chart.
2.6. Drill down and drill up
In order to perform drill downs you need to switch to View mode from the current Edit mode. In View mode the rulers are hidden and the options available in the right-click menu for a control are different. In particular, the menu will have options for drill down and drill up. To switch to View mode, go to the toolbar and click View.
While in View mode, right-click (or long-tap) over one of the bars of the chart. From the menu, 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 upon. You can continue drilling down further, or drill back up using the context (right-click) menu.
To go back to Edit mode, go to the toolbar and click Edit.
3. See also
- Connect to data and view it on a dashboard
- Automatic joins and hierarchies
- Create a time dimension
- Video: Creating and Viewing Dashboards
- Video: Drill Down and Custom Dashboard Actions