Overview

This article explains how you can use Functions to calculate a Streaming Dataset value.

• 1.  Sum Function
• 2.  Function Types
• 3.  Adding Filters to Functions
• 4.  Date Filters
• 5.  First / Top Function Filter

1.  Sum Function

In the DEMO Dataset, Select Row A.  Note that there is a Function Exists tick box on the row to indicate that there is an existing Function for Row A.  Choose View Function.

The following screen shows the existing function:

2.  Function Types

A function can be one of the following:

• Sum – an aggregation function for a specified numeric field.
• First / Top – a function to return the first / top value in a list
• Count – a function to count rows in a specified table
• Count Unique – a function to count the number of unique values for a specified field in a specified table.

3.  Adding Filters to Functions

When building Functions, you can select from full lists of tables and fields available in your Business Central Environment.  This should also include tables and fields, for instance from custom extensions or other extensions added to Business Central from AppSource.

You can add filters to each function as required with standard Business Central filter criteria.

In the DEMO dataset, No. of Sales Invoices & Credit Memos Current Month against Row D uses an Either/Or type filter on the field Document Type.

When building filters for Option/Enum fields, you can use the ellipsis icon in the Filter Value box to display a list of possible values to choose from.

Please refer to https://docs.microsoft.com/en-gb/dynamics365/business-central/ui-enter-criteria-filters#FilterCriteria for more information of how filter expressions are correctly entered.

4.  Date Filters

When adding date filters, the filter can be determined by a Date Formula.  This allows date filter to be self-maintaining if for instance you want to always show something filtered by the Current Month or Current Year.

In the DEMO Dataset, Sales Current Month value against Row A uses 2 date formula values which represent the start and the end of the Current Month.

There are some example Date Formula values in the following table.

Please refer to the Microsoft documentation for further information about how to Use Date Formulas in Business Central.  https://docs.microsoft.com/en-gb/dynamics365/business-central/ui-enter-date-ranges#use-date-formulas

5.  First / Top Function Filter

When using the First / Top Function, a filter value needs to be specified for a numeric field that is used to determine the First / Top value for the field specified in the function.

In the DEMO Dataset, Best Selling Item Today against Row H has a filter for the Sales (LCY) field with a * as the Filter Value.  The function will then return the value of the Description field for the Item, for the largest Sales (LCY) value.

It is possible to continue to use additional filters as required, as shown in the example below.