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 2017, or higher
  • Microsoft .NET Framework 4.7.2 or higher, and Microsoft .NET Core 3.1 or higher
  • A deployed instance of Dundas BI version 7 or higher

2.1. Downloading sample solution

To download the custom function sample solution, click here.

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

2.2. Extract the sample to a folder

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

2.3. Opening solution

Find and open the solution located at:

[extracted folder]\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.
  • PublishExtensionTemplate.props - Used for auto publishing the extension after the build succeeds, and defines extension properties, and files.

3.1. ExtensionPackageInfo class

To be able to extend Dundas BI, the assembly must contain a class that extends the ExtensionPackageInfo2 class. This class contains a call to the base constructor that reads extension package information from the extension manifest file.

        
/// <summary>
/// This class contains the package information about the extension package.
/// </summary>
public class CustomFunctionSamplePackage : ExtensionPackageInfo2
{
        /// <summary>Initializes a new instance of the 
        /// <see cref="CustomFunctionSamplePackage"/> class.</summary>
        /// <param name="extensionManifest">The extension manifest.</param>
        public CustomFunctionSamplePackage(ExtensionManifest extensionManifest)
                    : base(extensionManifest)
        {
        }
}
        

3.2. Publish Extension Template Props

This sample has a mechanism to automatically publish the extension when building. This mechanism is the Dundas.BI.PublishExtension NuGet package. When the Dundas.BI.PublishExtension package is added to the project it creates a PublishExtensionTemplate.props file in the project. This file contains Msbuild property and item groups, these are defined to create the extension.

When the DtFilePath property is set to the file path of the dt utility inside an instance. It will then publish the extension to that instance. It will also then touch the web.config to force the web application to reset.

If the DtFilePath property is not set it will create a zip file in the based on the ExtensionOutputFolder folder. This can then be added through the Dundas BI user interface to add the extension to the instance.

<Project>
  
  <Target Name="DefineDundasBIExtensionProperties" AfterTargets="CopyFilesToOutputDirectory">
    <!-- Properties used to publish extension -->
    <PropertyGroup>

      <!-- Extension Author -->
      <ExtensionAuthor>Dundas Data Visualization Sample Author</ExtensionAuthor>
      <!-- Extension Name -->
      <ExtensionName>$(AssemblyName)</ExtensionName>
      <!-- Extension Display Name -->
      <ExtensionDisplayName>$(AssemblyName)</ExtensionDisplayName>
      <!-- Extension Folder Name -->
      <ExtensionFolderName>$(AssemblyName)</ExtensionFolderName>
      <!-- Extension Main Assembly Name -->
      <ExtensionMainAssemblyName>$(AssemblyName).dll</ExtensionMainAssemblyName>
      <!-- Extension Id -->
      <ExtensionId>b60b426d-0766-4a60-973e-2f50c3e6abde</ExtensionId>
      <!-- Extension Copyright -->
      <ExtensionCopyright>Copyright (c)</ExtensionCopyright>
      <!-- Extension Version -->
      <ExtensionVersion>1.0.0.0</ExtensionVersion>
      <!-- The outfolder where the extension zip file will be left. -->
      <ExtensionOutputFolder>$(OutputPath)</ExtensionOutputFolder>
      <!-- DT -->
      <!-- If this is specified the extension will be installed using dt.  -->
      <DtFilePath></DtFilePath>

      <!-- Framework Folder -->
      <FrameworkFolderRelative>$(OutputPath)\..</FrameworkFolderRelative>
      <FrameworkFolder>$([System.IO.Path]::GetFullPath($(FrameworkFolderRelative)))</FrameworkFolder>
      
    </PropertyGroup>

    <!-- Define files to include -->
    <ItemGroup>
      <!-- Define the NetFramework assemblies -->
      <NetfwAssemblies Include="$(FrameworkFolder)\net472\$(AssemblyName).*"   />
      <!-- Define the NetCore assemblies -->
      <NetCoreAssemblies Include="$(FrameworkFolder)\netcoreapp3.1\$(AssemblyName).*"  />
      <!-- Define any app resources for the extension. -->
      <AppResources />
      <!-- Define any file resources for the extension. -->
      <FileResources />
      <!-- Define any localization files for extension. -->
      <Localizations />
      <!-- Define Extension Supported Runtimes -->
      <ExtensionSupportedRuntimes Include="NetFramework" />
      <ExtensionSupportedRuntimes Include="NetCore" />
    </ItemGroup>
  </Target>
</Project>

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:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri