Multi-Select Lookup in SSRS Report in D365FO

 

Description:

In this post, we'll walk through the process of creating a multi-select lookup in an SSRS report for Dynamics 365 Finance and Operations (D365FO). This allows the user to select multiple values from the lookup, which can be extremely helpful in various reporting scenarios.

Step 1: Create a Contract Class

To begin, we need a contract class to handle the parameters passed to the report. This class will include a list of item IDs as a parameter for our report.

Contract Class:

x++
[DataContractAttribute, SysOperationContractProcessingAttribute(classstr(MultiSelectLookupContract))]
class MultiSelectLookupContract implements SysOperationValidatable
{
    List  itemList;

    [DataMemberAttribute("Item Ids"), AifCollectionTypeAttribute("Item Ids", Types::String),
     SysOperationLabelAttribute(literalStr("Item Ids"))]
    public List parmItemList(List _itemList = itemList)
    {
        itemList = _itemList;
        return itemList;
    }

    // Validation for the parameters
    public boolean validate()
    {
        boolean isValid = true;
        List itemListValid = this.parmItemList();
        if (!itemListValid.elements())
        {
            isValid = checkFailed("ItemId list cannot be empty.");
        }
        return isValid;
    }
}

Step 2: Create the UI Builder Class

The UI Builder class will generate the form fields and the multi-select lookup button. This class is where we handle user input for the lookup field.

UI Builder Class:

x++
class MultiSelectLookupUIBuilder extends SysOperationAutomaticUIBuilder
{
    DialogField itemLookupField;
    MultiSelectLookupContract lookupContract;

    // Method for handling the Item ID lookup
    private void lookupItemIds(FormStringControl _control)
    {
        Query query;
        container queryContainer;
        query = new Query(queryStr(ItemListQuery));
        SysLookupMultiSelectGrid::lookup(query, _control, _control, queryContainer);
    }

    public void build()
    {
        int row;
        lookupContract = this.dataContractObject() as MultiSelectLookupContract;
        itemLookupField = this.addDialogField(methodStr(MultiSelectLookupContract, parmItemList), lookupContract);
    }

    public void postBuild()
    {
        lookupContract = this.dataContractObject() as MultiSelectLookupContract;
        itemLookupField = this.bindInfo().getDialogField(lookupContract, methodStr(MultiSelectLookupContract, parmItemList));
        itemLookupField.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(MultiSelectLookupUIBuilder, lookupItemIds), this);
        
        if (itemLookupField)
        {
            itemLookupField.lookupButton(2);
        }
    }

    public void postRun()
    {
        // Any additional logic can go here after form load
    }
}

Step 3: Create the Data Provider Class

Next, we define a Data Provider class to retrieve the data based on the selected item IDs. This class will use the list of item IDs passed through the contract.

Data Provider Class:

x++
[SRSReportParameterAttribute(classStr(MultiSelectLookupContract))]
class MultiSelectLookupDataProvider extends SRSReportDataProviderBase
{
    MultiSelectLookupContract lookupContract;
    InventItemGroupItem inventoryItem;

    // Data Set for the report
    [SRSReportDataSetAttribute("InventItems")]
    public InventItemGroupItem get InventItems()
    {
        select * from InventItemGroupItem;
        return inventoryItem;
    }

    [SysEntryPointAttribute]
    public void processReport()
    {
        List selectedItemList = new List(Types::String);
        Query itemQuery;
        QueryRun itemQueryRun;
        InventItemGroupItem inventoryItem;
        QueryBuildDataSource itemDataSource;
        ListIterator itemIterator;

        // Get selected item IDs from the contract
        lookupContract = this.parmDataContract() as MultiSelectLookupContract;
        selectedItemList = lookupContract.parmItemList();

        if (selectedItemList != null)
        {
            itemIterator = new ListIterator(selectedItemList);
            itemQuery = new Query(queryStr(ItemListQuery));
            itemDataSource = itemQuery.dataSourceTable(tableNum(InventItemGroupItem));

            while (itemIterator.more())
            {
                itemDataSource.addRange(fieldNum(InventItemGroupItem, ItemId)).value(itemIterator.value());
                itemIterator.next();
            }

            itemQueryRun = new QueryRun(itemQuery);
            while (itemQueryRun.next())
            {
                inventoryItem = itemQueryRun.get(tableNum(InventItemGroupItem));
                Info(strFmt("Selected ItemId - %1", inventoryItem.ItemId));
            }
        }
    }
}

Step 4: Visual Studio Setup

After defining the necessary classes, follow these steps in Visual Studio to add the report and dataset:

1. Add a Report: Create a new report in Visual Studio and set the appropriate properties.

2. Add a Data Set: Link your data provider class to the report by defining the data set.

3. Set Parameters: In the parameters section of the report, set the ItemId parameter to allow blank values. This will enable multi-select functionality.

   • Allow Blank = True

   • Nullable = True

4. Precision Design: Define the precision design for your report output.

5. Deploy and Run the Report: Once everything is set, deploy the report and execute it to see the multi-select lookup in action.

Conclusion:

This process provides an efficient way to implement a multi-select lookup in SSRS reports in D365FO, allowing users to select multiple items from a lookup to be displayed in the report. By utilizing the contract class, UI builder, and data provider, we ensure that the lookup values are properly handled and integrated into the report.