416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Scorecard Sample

Hide navigation
RSS
Modified on Tue, 27 Jul 2010 10:59 AM Categorized as Samples

Table of Contents [Hide/Show]


Scorecard sample overview

This article walks you through the process to set up and design a data grid scorecard KPI using Dundas Dashboard.

This particular scorecard sample will explore the sales performance of an organization across the world, with data sorted by country and continent, comparing forecasted amounts to actual revenue for a given period (month), and how the current period performance compares to the previous period (month).

Image

Data and virtual tables

Depending on your desired scorecard, the data requirements can vary between implementations. One feature that should be accounted for in the data preparation is period-by-period comparison - ie: trending. In the sample, we include the Revenue for the previous period as a data column to allow us to calculate the trend.

Our sample uses data stored in a SQL Server View, with the columns corresponding to transactional data for our period, as well as the same data for the previous period. This can easily be done using a join on the transactional data table, either in your database management tool or through a manual Virtual Table in Dundas Dashboard.

With all the required data stored in a SQL Server View, the Virtual Table will just be a reflection of the entire View. To create the Virtual Table:

  1. In the Design Explorer, click the Context Menu on the Virtual Tables item and select New Virtual Table.
  2. Name the Virtual Table: "Scorecard_VT" and press . This opens up the Virtual Table wizard.
    • STEP 1: Select Standard Virtual Table and click Next.
    • STEP 2: Select the correct Data Connector and click Next.
    • STEP 3: Select the View that contains the scorecard Data by checking the checkbox in the corresponding row, and click Finish.

A preview for the Scorecard_VT will display the following schema:

Image

The KPI in the sample requires the following columns - other columns in Scorecard_VT not used can be included on the scorecard for additional functionality:
  • Continent
  • Country
  • SaleDate
  • Revenue
  • Orders
  • Estimate
  • PrevRev (Revenue for previous period)
  • RevGrowth (Revenue minus PrevRev)

Full time dimension

Setting up a full time dimension enables the grain filtering (year, month, quarter, week, day) option on KPIs that use a DateTime dimension.

To create and configure a Full Time Dimension:

  1. In the Design Explorer, click the Context Menu on the Dimensions item and select New Time Dimension.
  2. Name the full time dimension : "Scorecard_Months" and press . This opens up the Time Dimension settings.
    • Define the Start/End dates the dimension will cover.
    • Select the Time Periods to allow filtering by.
      • Year
      • Month
    • (Optional) Define the Formatting for the selected Time Periods on the Formatting tab, by typing directly into the Format column.
  3. Save and Check In the Scorecard_Months time dimension.

KPI Designer

The KPI for the scorecard will define the available the measures to include in the scorecard, what data is exposed to the Dashboard Designer, and what dimensions are used to filter the scorecard on the dashboard.

To create the KPI from the defined Virtual Table:

  1. In the Design Explorer, click the Context Menu on the KPIs item and select New KPI.
  2. Name the KPI: "Scorecard_KPI" and press . This opens up the KPI Setup wizard.
    • STEP 1: Select "Scorecard_VT" as your base Data Structure and click Next.
    • STEP 2: Select all required Measures, and click Next.
      • Revenue (Default Value)
      • Orders
      • Estimate
      • PrevRev
      • RevGrowth
    • STEP 3: Click Next to skip defining Contextual Metrics.
    • STEP 4: Select all required Dimension Fields, and click Next.
      • Country (Axis)
      • SaleDate
    • STEP 5: Select all required Annotation Fields and click Finish to complete the KPI creation.
      • Continent

To configure the KPI Dimensions to allow filtering on monthly data:

  1. On the Dimensions tab, click the Replace field with an existing Time Dimension button under the Action column. This opens up the Time Dimension Configuration wizard.
  2. Select "Scorecard_Months" as the time dimension and click Next.
  3. Click Next after previewing the hierarchy.
  4. Select the configurations for the time dimension.
    • Enable Public checkbox.
    • Set Default value (November 2009) by clicking on the Default value selection field.
  5. Click Finish to complete time dimension configuration. This will promote the SalesDate time dimension to an Existing Dimension, using Scorecard_Months.

To complete KPI setup:

  1. On the Preview tab, preview the data to verify all measures are included.
  2. Check-In KPI using the Check In button in the Toolbar.

Dashboard/DashBlock designer

A scorecard can be built using either a dashboard or a DashBlock - the sample in particular is created as a DashBlock, to leverage the ability to include the scorecard in custom Mash-Up dashboards, since this is a single data visualization. The scorecard can also be included in dashboard design using the DashBlock viewer.

To create a new DashBlock:

  1. In the Design Explorer, click the Context Menu on the Dashboards item and select New DashBlock.
  2. Name the DashBlock: "Scorecard_Monthly" and press . This opens up the DashBlock Designer.

The DashBlock Designer is essentially the same as the Dashboard Designer - the canvas starts off slightly smaller, but this is something that can be modified through the properties of the DashBlock.

The data grid

Displaying a scorecard in the DashBlock Designer involves leveraging the data grid control and managing the look and feel display properties of the data grid, as well as configuring what data items are displayed.

To add a data grid to the DashBlock Designer and connect the KPI data to it:

  1. In the Toolbox, click and drag the Data Grid control (in the Data Controls section) onto the DashBlock Designer canvas.
  2. In the Toolbox, click and drag the Scorecard_KPI KPI onto the data grid on the canvas. This displays on the data grid the default measures and dimensions defined in the KPI.

With the KPI connected to the data grid, creating the scorecard interface is just a matter of managing the different properties in the data grid control.

Columns

The Columns list property for the data grid control outlines the available data to display in the grid, and how that data is displayed.

To set up the Columns property for the scorecard:

  1. Select the data grid control by clicking on it in the DashBlock Designer.
  2. In the Properties pane on the right, expand the (List) for the Columns property, under the Data Grid section. This opens up a new Columns properties window.
    Note: The columns displayed in this list include non-axis dimensions and annotations, defined in the KPI.
  3. In the Column window, add additional columns to display using the Add button dropdown list. The additional columns required are:
    • Flag: DashboardDataGridImageColumn
    • Threshold: DashboardDataGridStateIndicatorColumn
    • Forecasted: DashboardDataGridColumnGroup
    • Revenue Trend: DashboardDataGridStateIndicatorColumn
  4. Rename these columns by modifying the Header Text property, under the Text section.
  5. Hide columns not meant to be displayed as data on the data grid control by selecting the column in the list and changing the Visibility property under the Appearance section from Visible to Collapsed.
    • RevGrowth
    • Continent
  6. Remove the columns related to forecasting by selecting the column in the list and clicking the Remove button - these will be included in the Forecasted column group.
    • Estimate
    • Quotes
  7. Arrange the order of the columns to the desired arrangement, using the up/down arrows to the right of the Columns list pane. They should be arranged as follows:
    • Country
    • Flag
    • Threshold
    • Forecasted
    • Orders
    • Revenue
    • Revenue Trend
    • RevGrowth (hidden)
    • Continent (hidden)
  8. For each column that should be displayed as a currency value ($), change the Data Format String property to "C2".

A column group (DashboardDataGridColumnGroup) like the Forecasted column group acts as a collection of columns - the process for managing this embedded list is the same as managing columns in the initial list.

  1. In the columns list, select the Forecasted column group.
  2. Expand the (List) in the Columns property, under the Data Grid section.
  3. Add the required columns to the Forecasted column group by using the Add dropdown button.
    • Estimate: DashboardDataGridTextColumn
    • Orders: DashboardDataGridTextColumn
    • Value: DashboardDataGridStateIndicatorColumn
  4. To connect new text columns to data, expose the Advanced properties by clicking the Show Advanced Properties button immediately to the left of the Search bar. Additional properties (with a darker background) will display in the Properties pane.
  5. Under the Data section in the properties, modify the Data Column and Sort Column properties to the use the appropriate KPI column for:
    • Estimate
    • Orders

Image column

Pre-requisite: Images must be hosted through an IIS site or available online in order to display in the data grid.

The DashboardDataGridImageColumn-type columns allow users to embed hosted images into the data grid control, using column data as a parameter in the image URL address.

In the sample, the flag images loaded in the grid are saved with the Country column value as the file name.
Note: The flag image file for South Korea is named "South Korea.png".

To manage the image column in the data grid to display images from your hosted location:

  1. Select the DashboardDataGridImageColumn "Flag" from the columns list window.
  2. Expose the Advanced Properties by clicking on the Show Advanced Properties button immediately to the left of the Search bar.
  3. In the Data Column and Sort Columns, select the KPI measure column that will provide the parameter for your image name or image address location.
    • This is the Country column for the sample scorecard.
  4. In the Data Format String property under the Data section, put the URL location of the folder where the flag images are being hosted, using {0} as the .
    Note: In v2.0 Beta, you will need to use GetImage.aspx?imageUrl= parameter from the same domain in order to bypass Silverlight's cross-domain access policy.
    • In the sample, the connection format is: http://joeh7:2001/GetImage.aspx?imageUrl=http://joeh7:2001/images/Countries/{0}.png.

The image loaded in each row will correspond to the value of the Country column in that row.

State indicators

State indicators in the data grid provide an at-a-glance indication of the performance metric being measured. Defining states for measures on the data grid can be done at the dashboard level, by configuring the states and their properties within the data grid.

The three state indicator columns in the scorecard {Threshold, Value, Revenue Trend} are configured differently to display their data.

Threshold state

The Threshold state represents what revenue tier a particular country belongs to: bronze, silver, or gold.

To manage the Threshold state:

  1. In the data grid Columns List, select the Threshold column.
  2. Expose the Advanced properties by clicking the Show Advanced Properties button immediately to the left of the Search bar. Additional properties (with a darker background) will display in the Properties pane.
  3. Under the Data section in the properties, modify the Data Column and Sort Column properties to the use the appropriate KPI column for the Threshold state.
    • Revenue Trend
  4. Expand the State Indicator property tree, under the State Indicator section.
  5. Expand the (List) for the Indicators property. This opens up a new Indicators window.
  6. Expand the (List) for the States property, under the State Indicator section. This opens up a new States window.
  7. Create a new state by clicking on the Add button for each of the desired states, and modify the properties for each state as described.
    • Bronze
      • Shape Fill: #FF907601
      • Skinned Shape: Square4
      • Start Value: 0
      • End Value: 1000
      • Legend Text: Bronze
    • Silver
      • Shape Fill: #FFCECECE
      • Skinned Shape: Square4
      • Blink Color: #FFECECEC
      • Enable Blinking: True
      • Start Value: 1000
      • End Value: 2500
      • Start Value Exclusive: True
      • Legend Text: Silver
    • Gold
      • Shape Fill: #FFEFD803
      • Skinned Shape: Square4
      • Blink Color: #FFECE59B
      • Enable Blinking: True
      • Start Value: 2500
      • End Value: 2147483647
      • Start Value Exclusive: True
      • Legend Text: Gold

Value state

The Value state looks at the Estimate value and sets above target and below target indicators.

To manage the Value state:

  1. In the Forecasted Columns List, select the Value column.
  2. Expose the Advanced properties by clicking the Show Advanced Properties button immediately to the left of the Search bar. Additional properties (with a darker background) will display in the Properties pane.
  3. Under the Data section in the properties, modify the Data Column and Sort Column properties to the use the appropriate KPI column for the Value state.
    • Estimate
  4. Expand the State Indicator property tree, under the State Indicator section.
  5. Expand the (List) for the Indicators property. This opens up a new Indicators window.
  6. Expand the (List) for the States property, under the State Indicator section. This opens up a new States window.
  7. Create a new state by clicking on the Add button for each of the desired states, and modify the properties for each state as described.
    • Meets Target
      • Shape Fill: Green
      • Skinned Shape: Diamond
      • Start Value: 1500
      • End Value: 2147483647
      • Start Value Exclusive: True
      • Legend Text: Meets Target
    • Below Target
      • Shape Fill: #FFFE2300
      • Skinned Shape: Diamond
      • Blink Color: #FFFED724
      • Enable Blinking: True
      • Start Value: 0
      • End Value: 1500
      • Start Value Exclusive: True
      • Legend Text: Below Target

Revenue trend state

The Revenue trend state looks at the RevGrowth value and sets uptrend and downtrend indicators.

To manage the Revenue trend state:

  1. In data grid Columns List, select the Revenue trend column.
  2. Expose the Advanced properties by clicking the Show Advanced Properties button immediately to the left of the Search bar. Additional properties (with a darker background) will display in the Properties pane.
  3. Under the Data section in the properties, modify the Data Column and Sort Column properties to the use the appropriate KPI column for the Value state.
    • RevGrowth
  4. Expand the State Indicator property tree, under the State Indicator section.
  5. Expand the (List) for the Indicators property. This opens up a new Indicators window.
  6. Expand the (List) for the States property, under the State Indicator section. This opens up a new States window.
  7. Create a new state by clicking on the Add button for each of the desired states, and modify the properties for each state as described.
    • Uptrend
      • Shape Fill: Green
      • Skinned Shape: ArrowUp
      • Start Value: 0
      • End Value: 2147483647
      • Start Value Exclusive: True
      • Legend Text: Uptrend
    • Unchanged
      • Shape Fill: Green
      • Skinned Shape: ArrowRight
      • Start Value: 0
      • End Value: 0
      • Legend Text: Unchanged
    • Downtrend
      • Shape Fill: #FFFB1002
      • Skinned Shape: ArrowDown
      • Start Value: -2147483648
      • End Value: 0
      • End Value Exclusive: True
      • Legend Text: Downtrend

Row grouping

Row grouping can be configured very easily using the Properties pane for the data grid control on the Dashboard Designer.

  1. Select the data grid control by clicking on it in the DashBlock Designer.
  2. Click on the Show Advanced Properties button immediately to the left of the Search bar in the Properties pane, to display all properties for the data grid.
  3. Expand the (List) in the Group Descriptions property. This will open a new Group Descriptions window.
  4. Add a column to use as a grouping column by clicking on the Add button.
  5. Change the value of the Data Column under the Data section to the desired grouping column by selecting the KPI column in the dropdown list.
    • In the sample, this is the "Continent" column.
  6. Modify the Text property to change the display label of the Group Description.
  7. Click the Close button on the Group Descriptions window.
  8. Modify the Are Groups Expanded property to True to have grouped rows display by default.

Parameters

The data in this sample is available at the month level. Introducing a time parameter will allow filtering of the data by month, to display the snapshot-in-time scorecard for that period.

To create a new time parameter filter:

  1. Click the Parameters section in the Sidebar, below the Toolbox.
  2. Click Add New Parameter... to launch the Parameter Set Up wizard.
  3. Name the parameter: "MonthFilter" and click Next.
  4. Select the Scorecard_KPI by checking the checkbox and click Next.
  5. Select the SaleDate Filter by checking the checkbox and click Next.
  6. Select the first time parameter selection type and click Next.
  7. Select Month in the default grain dropdown and click Finish.
  8. Drag and drop the MonthFilter parameter onto the DashBlock Designer canvas. This displays a pop-up to define the range of selectable dates.
  9. Click Finish to keep default settings.

The MonthFilter parameter filters the data displayed in the data grid control by month.
Note: Not all countries will have sales data for all months.

Look and feel

Some styling elements can be included to enhance the visual aspect of the scorecard sample - these include, but are not limited to:
  • Enlarging the DashBlock canvas size in the DashBlock properties.
  • Pinning the data grid edges to the edges of the canvas to allow for proportional resizing.
  • Introducing background imaging on the DashBlock.
  • Modifying the Header styles for the columns in the data grid control.

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2012 Dundas Data Visualization, Inc.