416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.5

This site makes extensive use of JavaScript.

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

Adding a Custom Column

Hide navigation
RSS
Modified on Tue, 15 Oct 2013 11:47 AM Categorized as Datasets, Formulas, KPIs, Virtual Tables
Click to return to: Documentation | Virtual Tables | Datasets | KPIs


Overview

This article shows how to add custom columns to a virtual table, dataset, or KPI in &dw;. You may want to add a custom column in order to:

  • Create a constant value column or constant value contextual metric for comparison purposes.
  • Add a counter column to keep track of the number of records for each grouping.
  • Derive a calculated column, measure, or contextual metric using a database expression, DundasScript expression, or formula-based expression.
  • Create a contextual metric by joining with an external virtual table.

Note: The text appearing in the example wizard screens may be different depending on whether you are adding a custom column to a virtual table, a calculated column to a dataset, or a calculated measure/contextual metric to a KPI. However, the usage is essentially the same in each case.

Tip: Watch the how-to video: Custom Column

Constant value column

A constant value column or contextual metric has the same value for each data row. This is useful for displaying a horizontal line on a chart for comparison purposes.

To add a constant value column to a virtual table, or add a constant value contextual metric:

  1. Select the Constant value option.
    Constant value column.
  2. Enter a numeric value in the box provided.
  3. Click Finish.

Counter column

A counter column keeps track of the number of records corresponding to every grouped set of values. For example, if your virtual table groups order-related records by region, a counter column will keep track of the number of orders per region.

To add a counter column to a virtual table:

  1. Select the Counter function option.
    Counter column.
  2. Click Finish.

Calculated column

A calculated column/measure/contextual metric is defined using a mathematical expression involving existing column/measure/contextual metric values, or existing calculated columns. Dundas Dashboard supports the following calculated column types:



Using a database expression

Database expressions are evaluated on the server, alongside with any applied aggregators. They offer fast performance and you can use any mathematical expression syntax that is supported by your database. Most databases can handle expressions involving standard math operators or scale factors applied to column/measure values. You can also use parentheses to control the order of the math operations within your expression.

To add a calculated column/measure/contextual metric using a database expression:

  1. Select the Database expression option, then click Next.
    Choose database expression type.
  2. Choose a column (or measure/contextual metric) value to insert into the database expression by using the Available metric values dropdown list. The expression for the selected column value is inserted automatically into the Expression box. Or, if the column value you want is already selected in the Available metric values dropdown list, click the plus sign button to add the expression for the selected column value to the Expression box.
    Entering a database expression for a calculated value contextual metric.
  3. Enter the rest of the database expression in the Expression box.
  4. To check the syntax of the database expression, click Parse Expression.
  5. Click Finish.

Note: An expression such as {Revenue} cannot have any blank spaces surrounding the metric/column name. For example, { Revenue } will result in a parsing error.

Tip: You can insert column values of type string into your database expression as well. For example, the database expression below creates a new string-type column containing full name values.

{FirstName} + ' ' + {LastName}

Aggregation

The use of aggregate functions within a database expression is not allowed. However, in general, you can set an aggregator for a column in the KPI/dataset designer and if this column is used in a database expression, the associated aggregator will be included implicitly. The specific details are as follows:

For a KPI: If there is no aggregator on the calculated measure (or contextual metric), and if any of the columns used in the database expression have aggregators, the aggregators will be included implicitly in the calculation.

For a dataset: If Group By is unchecked for the calculated column, and if any of the columns used in the database expression have aggregators, the aggregators will be included implicitly in the calculation.

Using a DundasScript expression

A DundasScript expression is evaluated locally on the client, after data retrieval has been executed and after existing aggregators have been applied on the server. While the performance is significantly lower than that of a database expression, you have much greater flexibility with respect to the expression syntax. Use standard C# math operators in your expression, such as: +, -, *, /, (, and ). Or, apply C# math functions, such as Math.Max or Math.Log, to your column values. See Scripting in Dundas Dashboard for more details on the DundasScript syntax but note that only mathematical expressions are supported in this particular usage. Programmatic constructs are not permitted although there is support for a special kind of if then statement. Below is an example of a DundasScript expression which computes a calculated value contextual metric based on an existing measure column, Revenue:

1.5 * {Revenue}

Tip: A DundasScript expression can also be based on database expression columns which you've previously defined.

Note: An expression such as {Revenue} cannot have any blank spaces surrounding the metric/column name. For example, using { Revenue } will not result in a parsing error but it will result in blank values returned when you preview the results.

Adding a script column

To add a calculated column/measure/contextual metric using a DundasScript expression:

  1. Select the DundasScript expression option, then click Next.
    Choose DundasScript expression type.
  2. Choose a column (or measure/contextual metric) value to insert into the script expression by using the Available metric values dropdown list. The expression for the selected column value is inserted automatically into the Expression box. Or, if the column value you want is already selected in the Available metric values dropdown list, click the plus sign button to add the expression for the selected column value to the Expression box.
  3. If you want to insert a math function into the script expression, select an entry in the Available math functions dropdown list. Or, if the math function you want is already selected in the Available math functions list, click the plus sign button to insert the selected function into the Expression box.
    Entering a DundasScript expression for a calculated value contextual metric.
  4. Enter the rest of the script expression in the Expression box.
  5. To check the syntax of the script expression, click Parse Expression. If you encounter a parsing error, check the data type of each measure value that you are passing as an argument of a math function. For example, the function Math.Sqrt accepts an argument of type Double (or of a type that can be implicitly converted to a Double). If you try to pass a column value of type Currency, which cannot be implicitly converted to a Double, the result will be a parsing error. In this case, specify a conversion function explicitly as shown in the example below:

    Math.Sqrt( Convert.ToDouble( {UnitPrice} ) )
  6. Click Finish.

Note: Dundas Dashboard treats the value returned by your script expression differently depending on the data type of the value. For example, if your script happens to return a .NET TimeSpan object, it will be treated as a string. The general rule is as follows:

  • If the result type is numeric, it will be treated as a double.
  • If the result type is DateTime, it will be treated as a DateTime.
  • If the result type is anything other than the above, it will be treated as a string.


Operations on a column

Script expressions can use the following operations which are applied to a metric/column as a whole:

  • SUM
  • AVG
  • MIN
  • MAX
  • FIRST
  • LAST
  • COUNT
  • INDEX

Example:

SUM({Order Amount})

Using the SUM function.

Using the SUM function.


If condition

A script expression can be a special kind of if statement that has the following syntax:

DundasScript.If(Convert.ToBoolean(condition), Convert.ToDouble(valueIfTrue), Convert.ToDouble(valueIfFalse))

Here's an example that checks if Order Amount is greater than or equal to 100. If so, it outputs 1, if not it outputs 0:

DundasScript.If(Convert.ToBoolean({Order Amount} >= 100), Convert.ToDouble(1), Convert.ToDouble(0))

Using an If condition.

Using an If condition.


Previous and next record values

Use the following script expression syntax to specify previous, current, or next record values:

  • {Order Amount:-1} - Previous record value
  • {Order Amount} - Current record value
  • {Order Amount:0} - Current record value
  • {Order Amount:1} - Next record value


Using previous, current and next record values.

Using previous, current and next record values.


Using a formula expression

A formula expression involves the application of a Dundas Dashboard formula to existing columns/measures/metrics. Formula expressions are evaluated on the client.

To add a calculated column/measure/contextual metric using a formula expression:

  1. Select the Formula expression option, then click Next.
    Choose Formula expression type.
  2. Choose a formula category from the Available formula categories dropdown list (⪚ Standard).
  3. Choose a formula from the Available formulas dropdown list (⪚ Moving Average).
    Defining a formula expression.
  4. A set of controls appears, which lets you configure the chosen formula in a manner similar to the Formula Setup dialog:
    • If the formula requires data alignment, choose the alignment method or column. For example, if the input is a time series, choose a time dimension for alignment.
    • Select the column that will provide the input data values for the formula, then choose an aggregator function in the case of duplicate values (which is applicable if the formula requires alignment).
    • Repeat the above step if the formula requires multiple input series.
    • Adjust formula parameter values as desired.
    • Select the formula output to be included in the result.
  5. Click Finish.

Contextual metric based on an external virtual table

You can create a contextual metric that is based on a data column from another virtual table, which provides the contextual values. To create this type of contextual metric, you must define the relationship (&ie; do a join) between your KPI's base virtual table and the virtual table providing the context.

To add this type of contextual metric to your KPI:

  1. In the first step of the Calculated Metric Definition Wizard, select the External virtual table data option.
    Choose Database dataset option.
  2. In the second step of the wizard:
    • Select the virtual table from the grid that will provide the contextual data.
      Selecting the context virtual table.
    • Click Next.
  3. In the third step of the wizard:
    • Choose the column from the virtual table that will be providing the contextual data by selecting the appropriate check box in the Contextual Metric grid column. Note that only columns from the virtual table that are compatible (&ie; have the same data type) with your KPI's measure will be listed in the grid.
    • Choose the relationship type using the dropdown list provided.
    • Select a column from the virtual table that your KPI is based upon.
    • Select a matching (primary key) column from the context virtual table.
      Joining with the context virtual table.
    • Click Finish.

Related topics



Click to return to: Documentation | Virtual Tables | Datasets | KPIs

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

Copyright © 2009-2016 Dundas Data Visualization, Inc.