Create a custom formula

1. Overview

This sample will shows how to create a custom formula. You will learn the following:

  • How to get started
  • How to create a Dundas BI extension
  • How to create a custom function

The result of NetPrice formula implemented below.
The result of NetPrice formula implemented below.

2. Getting started

The following prerequisites must be installed on your computer:

  • Visual Studio 2012, or higher.
  • Microsoft .NET Framework 4.5
  • Dundas BI Server

2.1. Downloading file

To download the custom function sample solution click here.

2.2. Extracting sample to SDK folder

This sample is designed to automatically publish the extension to the instance. First you must extract the CustomFunction.zip to the SDK folder within the instance. To extract the CustomFunction.zip do the following:

  1. Find the SDK folder for your instance. It is located at [instance root]\sdk.
  2. Extract the CustomFunction.zip to the SDK folder.
  3. You should see the following result:

    Samples added to SDK folder
    Samples added to SDK folder

    With the follwing structure:

    • [instance root]
      • sdk
        • Samples
          • CustomFunction

Important
If the sample is not in the exact folder, it will not work correctly.

2.3. Opening Solution

To open solution simply double click the solution located at:

[instance root]\sdk\samples\CustomFunction\CustomFunction\CustomFunction.sln

3. The project

The project is a class library.

  • CustomFunctionSamplePackage.cs - This class contains the package information about the extension package.
  • CustomFunction.cs - Class where the formula is defined.
  • PublishExtension.targets - Used for auto publishing the extension after the build succeeds.

3.1. ExtensionPackageInfo class

In order for a function to be read by Dundas BI it needs to first contain a class that extends the ExtensionPackageInfo class. This class contains the package information about the extension package.

        
/// <summary>
/// This class contains the package information about the extension package.
/// </summary>
public class CustomFunctionSamplePackage : ExtensionPackageInfo
{
    /// <summary>
    /// Gets the name of the extension package author.
    /// </summary>
    public override string Author
    {
        get { return "Dundas Data Visualization Sample Author"; }
    }
    /// <summary>
    /// Gets the copyright text associated with the extension package.
    /// </summary>
    public override string Copyright
    {
        get { return "Dundas Data Visualization, Inc."; }
    }
    /// <summary>
    /// Gets the localized display name of the extension package.
    /// </summary>
    public override string DisplayName
    {
        get { return "Custom Function Sample Package"; }
    }
    /// <summary>
    /// Gets the unique identifier of the extension package.
    /// </summary>
    public override Guid Id
    {
        get { return new Guid("b60b426d-0766-4a60-973e-2f50c3e6abde"); }
    }
    /// <summary>
    /// Gets the name of the extension package.
    /// </summary>
    public override string Name
    {
        get { return "Custom Function Sample Package"; }
    }
    /// <summary>
    /// Gets the version of the extension package.
    /// </summary>
    public override Version Version
    {
        get { return new Version(0, 0, 1); }
    }
}
        

3.2. Publish Extension Targets

This sample has a mechanism to automatically publish the extension. This mechanism is the PublishExtension.targets file which overrides the AfterBuild target. This will create the following file after successfully compiling the solution:

[instance root]\www\BIWebsite\App_Data\Extensions\CustomFunction\bin\CustomFunction.dll

It will then touch the web.config to force the web application to reset.

3.3. Defining the NetPrice custom formula

In order to create a custom formula you will need to extend a Dundas.BI.Data.Functions.FunctionDefinition. The following example demonstrates a CustomFunction class that extends the FunctionDefinition class:

using Dundas.BI.Data.Functions;
using Dundas.BI.Data.Parameters;
  ...

namespace CustomFunction
{
    /// <summary>
    /// This class represents a simple NetPrice function.
    /// </summary>
    /// <seealso cref="Dundas.BI.Data.Functions.FunctionDefinition" />
    public class CustomFunction : FunctionDefinition
    {

    }
}

3.4. Implementing the abstract FunctionDefinition class

3.4.1. Defining the component information

The FunctionDefinition requires that the following properties be defined:

/// <summary>
/// Gets the standard component description.
/// </summary>
public override string ComponentDescription
{
    get 
    { 
       return "This is a NETPRICE function designed as sample for a samples.dundas.com."; 
    }
}
/// <summary>
/// Gets the component ID.
/// </summary>
public override Guid ComponentId
{
    get { return new Guid("1c77c9a2-070e-447b-8f11-9c379fa129b1"); }
}
/// <summary>
/// Gets the standard component name.
/// </summary>
public override string ComponentName
{
    get { return "NetPrice"; }
}

The component description will be visible in the formula bar when the formula is typed in.
The component description will be visible in the formula bar when the formula is typed in.

3.4.2. Defining the alignment category and the category ID

The CategoryId property should be set to a member of the Category Enumeration. Also, the FunctionDefinition requires that an AlignmentCategory be specified.

/// <summary>
/// Gets the alignment option for the current function.
/// </summary>
public override AlignmentAxis AlignmentCategory
{
    get { return AlignmentAxis.Hierarchy; }
}
/// <summary>
/// Gets the function category ID.
/// </summary>
public override Category CategoryId
{
    get { return Category.Standard; }
}

3.4.3. Defining inputs, outputs, and settings

To define the inputs, outputs, and settings within our formula we override the GetMetadata method on the FunctionDefinition.

/// <summary>
/// Populates the function metadata by describing the data inputs, 
/// the function parameters and results.
/// </summary>
/// <param name="dataInputs">The data inputs.</param>
/// <param name="settings">The function settings.</param>
/// <param name="results">The results.</param>
/// <returns>
/// The formula symbol to be used in scripts.
/// </returns>
protected override string GetMetadata(
    out IList<InputDescriptor> dataInputs, 
    out IList<Dundas.BI.Data.Parameters.ComponentSetting> 
    settings, out IList<ResultDescriptor> results)
{
    dataInputs =
        new Collection<InputDescriptor>()
        {
            new InputDescriptor(
                new Guid("1c77c9a2-070e-447b-8f11-9c379fa129b1"), 
                "List Price", 
                "The displayed price of the item.", 
                FunctionInputFormat.Standard
            ),
            new InputDescriptor(
                new Guid("b5d7aa53-a790-4fa7-9590-9695f3615903"), 
                "Discount Percentage", 
                "The discount percentage.",
                FunctionInputFormat.Standard),
        };
    settings = new Collection<ComponentSetting>();
    results =
        new Collection<ResultDescriptor>()
        {
            new ResultDescriptor(
                new Guid("3fe65b2e-4758-4452-9283-b04ecf9631a3"), 
                this.ComponentName, 
                this.ComponentDescription, 
                FunctionResultFormat.Standard, true
                )
        };
    return "NETPRICE";
}

Note
The string returned by the GetMetadata method represents the name to access the formula, in the formula bar.

The mapping of the GetMetadata method to the appearance on the formula bar.
The mapping of the GetMetadata method to the appearance on the formula bar.

3.4.4. Defining the implementation of the formula

To define the implementation of the formula we override the Execute method on the FunctionDefinition. This method returns a list of FunctionResults.

/// <summary>
/// Executes the function and calculates the results.
/// </summary>
/// <param name="dataInputs">The data input values.</param>
/// <param name="settingValues">The function setting values.</param>
/// <returns>
/// The collection of function results.
/// </returns>
public override IList<FunctionResult> Execute(
   IEnumerable<FunctionInput> dataInputs, 
   IEnumerable<Dundas.BI.Data.Parameters.ParameterValue> settingValues)
{
    double[] listPrice = 
       dataInputs.First(p => p.InputId == this.DataInputs[0].Id).Values;
    double[] discountPercentage = 
       dataInputs.First(p => p.InputId == this.DataInputs[1].Id).Values;
    Collection<FunctionResult> results = 
       new Collection<FunctionResult>();
            
    double[] result = new double[listPrice.Length];
    for (int index = 0; index < result.Length; index++)
    { 
        result[index] = 
            listPrice[index] * ((100.0 - discountPercentage[index]) / 100);
    }
    ArrayResult arrayResult = new ArrayResult(result);
    results.Add(arrayResult);
    return results;
}

3.5. Debugging

In order to debug the transform, you can use the following:

System.Diagnostics.Debugger.Launch();

This pop up a window that will allow you to attach the debugger.

Debugging popup
Debugging popup

 

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