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

The Adventure Works for SQL Server 2012 database is used as an example.

2. Walkthrough

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 window. 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.

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 basically a container for two date-time hierarchies.

Default time dimension with two hierarchies
Default time dimension with two hierarchies

Expand the first hierarchy to see all the hierarchy levels such as Year and Month. Drag the Year level and hover over the table visualization on the canvas. A Data Binding (Metric Set) panel appears automatically beside the table visualization. Drop the Year item over the ROWS field of the panel.

Drag Year to ROWS
Drag Year to ROWS

The table visualization is immediately reconfigured to display year hierarchy members along the rows axis, and the OrderQty values are now broken down by year.

Table showing years on rows axis
Table showing years on rows axis

A metric set in Dundas BI describes how you want to visualize your data. You choose the measures and hierarchies you are interested in and decide on which axis to place them on, all using drag-and-drop operations.

In this example, the metric set is automatically generated by Dundas BI. Go to the Dashboards folder in the EXPLORE window, expand your dashboard, and look for an Auto-Generated Item which contains the metric set.

Auto-generated metric set
Auto-generated metric set

2.4. Modify the default time dimension

The default time dimension in Dundas BI is configured with members going back a fixed number of years. If your data goes back further than that, you can modify the start date of the time dimension as follows.

Go to the EXPLORE window, expand the Time Dimensions folder, and then right-click (or long-tap) over 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.

Check out time dimension
Check out time dimension

Right-click (or long-tap) over the time dimension again and select Edit

Edit time dimension
Edit time dimension

In the Time Dimension dialog, change the Date Range to start from 2004. Additionally, select other time periods including Half Year and Quarter.

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

Click Submit (checkmark) to save and check in your changes.

Next, select the table visualization on the canvas, go to the toolbar, click Data Tools, and then click Refresh Data.

Refresh data
Refresh data

The table visualization on your dashboard is automatically updated to reflect the change you made to the default time dimension. In particular, you will see data for the year 2005 now, and the Unknown member no longer appears.

Table is updated
Table is updated

2.5. Re-visualize as a chart

Select the table visualization on your dashboard.

Go to the toolbar and click Re-Visualize. From the dropdown menu, click Bar. The table is re-visualized as a bar chart.

Re-visualize table as bar chart
Re-visualize table as 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.

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 menu, 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 upon. You can continue drilling down further, or drill back up from the right-click 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