View data with a chart and drill down

Contents[Hide]

1. Overview

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 as a bar chart.
  • Drill down and drill up on the chart.

The Adventure Works SQL database is used as an example.

These steps use the dashboard editor, but you can follow similar steps when editing a metric set full-screen.

2. Walkthrough

2.1. Create a new dashboard

From the main menu, create a new, blank dashboard.

Create a new blank dashboard
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 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.

Drag measure to canvas
Drag measure to canvas

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 and time hierarchies.

Drag the Year > Month > Day hierarchy onto the area labeled Row Header over the table visualization.

Drag Year to Row Header
Drag Year to Row Header

The hierarchy is added under Rows in the Data Analysis Panel, and the table visualization is reconfigured to display a row for each year.

Table showing rows for each year
Table showing rows 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. 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 window, right-click (or long-tap) the Time Dimension item, and select Edit from the context menu that appears. 

Edit time dimension
Edit time dimension

(You can also open the time dimension from the main menu.)

In the Time Dimension dialog, change the Date Range to start from 2012. You can also select additional time periods such as Half Year and Quarter.

Change start date and time periods
Change start date and time periods

Click the submit (checkmark) button at the bottom of the dialog 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.

Refresh data
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.

Table is updated
Table is updated

2.5. Re-visualize as a chart

Select the table visualization on your dashboard.

From the toolbar click Re-Visualize and select Bar Chart.

Re-visualize table as bar chart
Re-visualize table as bar chart

The table is re-visualized as a bar chart.

Note
The Unknown members are not included along a Date/Time Axis in a chart, but you can switch to a Category Axis in the toolbar.

2.6. Drill down and drill up

In order to perform drill downs, 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 control are different. In particular, the menu will have options for drill down and drill up.

Switch to View mode
Switch to View mode

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.

Perform drill down
Perform 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.

Perform drill up
Perform drill up

To go back to Edit mode, go to the toolbar and click Edit.

3. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri