Create a custom formula

1. Overview

This sample shows how to create a custom formula function that users can call in Dundas BI metric sets.

The result of the NetPrice formula implemented below
The result of the NetPrice formula implemented below

2. Getting started

The following prerequisites must be installed on your computer:

  • Visual Studio 2015, or higher
  • Microsoft .NET Framework 4.6.1
  • A deployed Dundas BI instance

2.1. Downloading sample solution

To download the custom function sample solution, click here.

(Sample solutions are also available for Dundas BI versions 4-5, and versions 2.5-3.)

2.2. Extracting sample to SDK folder

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

  1. Find the SDK folder for your instance. It is located at [instance root]\sdk.
  2. Extract 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 following structure:

    • [instance root]
      • sdk
        • Samples
          • CustomFunction

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

2.3. Opening solution

Find and open 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

To be able to extend Dundas BI, the assembly must contain a class that extends the ExtensionPackageInfo class. This class contains the extension package information.

        
/// <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

To create a custom formula function, extend the Dundas.BI.Data.Functions.FunctionDefinition Class. The following example demonstrates a CustomFunction class that does this:

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 a sample for 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.", 
            FunctionInputType.Standard
        ),
        new InputDescriptor(
            new Guid("b5d7aa53-a790-4fa7-9590-9695f3615903"), 
            "Discount Percentage", 
            "The discount percentage.",
            FunctionInputType.Standard
        )
    };

    settings = new Collection<ComponentSetting>();

    results = new Collection<ResultDescriptor>()
    {
        new ResultDescriptor(
            new Guid("3fe65b2e-4758-4452-9283-b04ecf9631a3"), 
            this.ComponentName, 
            this.ComponentDescription, 
            FunctionInputType.Standard,
            true,
            string.Empty
        )
    };

    return "NETPRICE";
}

Note
The string returned by the GetMetadata method represents the name of the function to be typed into the formula bar.

Where metadata is displayed in the formula bar
Where metadata is displayed in 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(new Guid("14460384-a8fa-494b-abf7-e9e67c6b8e11"), 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 pops 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