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.
Dashboard v5.0

This site makes extensive use of JavaScript.

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

Accessing the Datapoints from a Secondary Data Source

Hide navigation
RSS
Modified on Thu, 19 Jun 2014 07:32 AM Categorized as Data Controls, Data Grid, Data Preparation, Datasets, KPIs, Level-Advanced, Script Library, Scripting, Virtual Tables
Dundas Dashboard Script LibraryPrevious


Overview

This article shows the steps on how to access the data points belonging to a secondary data source. This is useful when those data points need to be manipulated, such as coloring them or their values need to be returned on click etc.

Using the conventional e.GetDataPointColumnValue(columnName) method returns a null when used on a secondary data source. This is because these secondary data points cannot be accessed through script. Therefore, a workaround needs to be applied to access those values where the secondary data source data points need to be brought into the primary data source. This is done using joins at the virtual table level.

The process of creating the secondary data source column with a second KPI/Dataset on the data grid is similar to joining tables at the SQL level, but on the client side. When the join is applied on the virtual table level, it is done on the server side. The relationships are defined at the virtual table level to join the related tables and create a KPI or a Dataset based on the joined table is created. The steps below show how this is done.

Virtual tables

The following virtual tables, Department Budget and Department Expense, will be used for this example:

Department Budget

Department Budget


Department Expense

Department Expense


Steps

Original DataGrid

When the Expense column from the Department Expense table is used as a secondary data source column on the data grid created from the Department Budget table, the e.GetDataPointColumnValue(columnName) method returns null and the number of columns are returned are 2 on click:

Original DataGrid

Original DataGrid




//Click Interaction of the DataGrid

  1. TRACE "Number of columns: " + e.DataPoint.FieldValues.Count;

  1. TRACE e.GetDataPointColumnValue("Department Expense");



Trace Console

Trace Console


To get around this, join the two tables at the virtual table level and create a data grid based of the joined table as shown below.

Join Tables

Create a third virtual table that joins the first two tables using the inner join. In this example, the new table is called Department Budget vs Expense.

Department Budget vs Expense

Department Budget vs Expense


Inner Join

Inner Join


Relationship

Relationship


Department Budget vs Expense Preview

Department Budget vs Expense Preview


Dataset

Create a KPI/Dataset based on the new joined table:

Department Budget vs Expense Dataset

Department Budget vs Expense Dataset


DataGrid

Create a datagrid from the new KPI/Dataset:

Department Budget vs Expense Datagrid

Department Budget vs Expense Datagrid


Click Interaction: Option 1 - Hard-code the column name

Now apply the above script in the Click Interaction of the new data grid. You may need to change the column name passed to the method:



//Click Interaction of the DataGrid

  1. TRACE "Number of columns: " + e.DataPoint.FieldValues.Count;

  1. TRACE e.GetDataPointColumnValue("Expense");



Click Interaction: Option 2 - Click on any cell and return the clicked column header and cell value



if (e.DataPoint != null) { // Get the cell being clicked object cell = e.VisualElementArgs; // Get the row and column of cell object col = cell.DashboardDataGridDataMemberColumn; //Get Column Header Name #TRACE col.ToString(); //Get clicked cell value (works on primary or secondary dataset) #TRACE e.GetDataPointValueFromColumnId(col.DataColumnId); }



Result

The number of columns returned includes the column from the second virtual table. In this case, the number of columns are 3:

Trace Console Result

Trace Console Result


Applications


Related Topics

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.