Create a custom formula

Contents[Hide]

1. Overview

This sample shows how to create a custom formula function that users can call in formulas, formula visualizations, and calculated element transforms.

Once your extension is added to the application, your custom function can be used just like built-in formula functions such as SUM, CORRELATION, etc.

The result of the NETPRICE formula function implemented below

2. Getting started

The current version of the sample solution targets both .NET Framework and .NET Core/.NET 5+ so that the packaged extension will work for version 7 of the application and higher on all platforms.

The following prerequisites must be installed on your computer to build the provided sample without modifications:

Visual Studio 2017 or higher
Microsoft .NET Framework 4.7.2
Microsoft .NET Core 3.1

You can modify the sample project to target different versions of .NET if preferred depending on the version used by your application instance.

The NuGet packages referenced as dependencies are initially set to version 7.0.1. If your version of the application is newer and the APIs you are using may have changed, you can update the version of the package references.

2.1. Downloading sample solution

To download the custom function sample solution, click here.

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

2.2. Opening solution

Extract CustomFunction.zip to a folder and open the solution in Microsoft Visual Studio to build the extension.

To use the option to publish directly to your application instance, run Visual Studio as an administrator before opening the solution. For example, you can right-click Visual Studio in the start menu and find the Run as administrator option.

The solution file is 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 recognized, 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

This sample has a mechanism to automatically publish the extension when building the project, which is the Dundas.BI.PublishExtension NuGet package. When this package is added to the project, it creates a PublishExtensionTemplate.props file containing MSBuild property and item groups, which define how to create and publish the extension.

When the DtFilePath property is set to the file path of the dt tool of an application instance, it will then publish the extension directly to that instance when you build the solution. It will also touch the web.config file to force the web application to reset.

If the DtFilePath property is not set, it will create a .zip file you can add to your application instance using the Extensions page in the administration UI. After building the solution with default settings and the solution configuration set to Release, this .zip file can be found in the bin\Release\netcoreapp3.1 subfolder of your solution. It targets both .NET Framework and .NET Core.

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

      <!-- Extension Author -->
      <ExtensionAuthor>MyCompany 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>

For more details on using this package to automate publishing extensions, see Using the Dundas.BI.PublishExtension NuGet package.

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:

ComponentId - The unique identifier for the formula.
ComponentName - The name of the formula.
ComponentDescription - A description of the formula.

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

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

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.

Dundas BI, Managed Dashboards & Reports Support