前言

在做 SSRS 报表分页报表时,如果需要往报表添加 “多选项” 字段列,是不允许的,因为在添加查询数据集(FetchXML 查询)时就不允许添加多选的字段,会弹出如下提示。

SSRS分页报表数据集FetchXML查询不允许添加多选项字段

虽然有点蠢,但是也没有办法,对应这个问题通常的做法是在对应实体上新添一个文本字段,通过插件或工作流的方式,根据用户选择的多选项对文本字段进行赋值,记录多选项的显示名称,然后报表使用新添的文本字段显示,至于多选项之间用什么符号进行分割(大多数使用逗号),和用户约定好即可。

例如我在 Contact 实体上有如下两个字段,需要做的是通过插件或工作流的方式,在 Multi Select 字段更新时,将 Multi Select 选项的显示名称记录到 Multi Select (Text)。

  1. Multi Select,字段名:gdh_multi_select
  2. Multi Select (Text),字段名:gdh_multi_select_text

Contact实体新添的多选字段和多选字段对应的文本字段

将多选项的显示名称存储到文本字段的方式

方式一 使用插件

插件代码如下,将其注册到 Contact 实体,然后添加 Create、Update 步骤即可。

ContactPostUpdatePlugin.cs
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.IdentityModel.Metadata;
using System.Linq;
using System.Runtime.Remoting.Services;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Blog.D365.Plugins.Contact
{
    public class ContactPostUpdatePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            ITracingService tracer = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            IPluginExecutionContext context =
                (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            try
            {
                if (context.InputParameters.Contains("Target") && 
                context.InputParameters["Target"] is Entity)
                {
                    IOrganizationServiceFactory factory =
                        (IOrganizationServiceFactory)serviceProvider.
                        GetService(typeof(IOrganizationServiceFactory));
                    IOrganizationService service = factory.CreateOrganizationService(context.UserId);
                    IOrganizationService serviceAdmin = factory.CreateOrganizationService(null);
                    Entity targetEntityRecord = (Entity)context.InputParameters["Target"];

                    // "context.Stage == 40" -> PostOperation
                    if (context.Stage == 40 && targetEntityRecord.Attributes.Contains("gdh_multi_select") &&
                        (context.MessageName == "Create" || context.MessageName == "Update"))
                    {
                        SetMultiSelectText(serviceAdmin, targetEntityRecord);
                    }
                }
            }
            catch (Exception ex)
            {
                tracer.Trace($"ContactPostUpdatePlugin unexpected exception:\n{ex.Message}");
                throw;
            }
        }

        private void SetMultiSelectText(IOrganizationService organization, Entity contactEn)
        {
            if (contactEn.Attributes.Contains("gdh_multi_select"))
            {
                OptionSetValueCollection optionSetValues =
                    contactEn.GetAttributeValue<OptionSetValueCollection>("gdh_multi_select");
                string roleText =
                    GetMultiSelectOptionSetLabels(
                      organization, 
                      "contact", 
                      "gdh_multi_select", 
                      optionSetValues);
                if (!string.IsNullOrEmpty(roleText))
                {
                    Entity updateContact = new Entity(contactEn.LogicalName, contactEn.Id);
                    updateContact["gdh_multi_select_text"] = roleText;
                    organization.Update(updateContact);
                }
            }
        }

        public static string GetMultiSelectOptionSetLabels(
            IOrganizationService service,
            string entityLogicalName,
            string attributeLogicalName,
            OptionSetValueCollection values,
            int? languageCode = null)
        {
            if (values == null || !values.Any())
                return string.Empty;

            // Retrieve the attribute metadata
            RetrieveAttributeRequest retrieveAttributeRequest = new RetrieveAttributeRequest
            {
                EntityLogicalName = entityLogicalName,
                LogicalName = attributeLogicalName,
                RetrieveAsIfPublished = true
            };

            RetrieveAttributeResponse retrieveAttributeResponse = 
            (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);
            MultiSelectPicklistAttributeMetadata attributeMetadata = 
            retrieveAttributeResponse.AttributeMetadata as MultiSelectPicklistAttributeMetadata;

            if (attributeMetadata == null)
                throw new InvalidPluginExecutionException("Attribute is not a Metadata.");

            // Prepare a map from option value to label
            Dictionary<int, string> optionLabels = attributeMetadata.OptionSet.Options.ToDictionary(
                o => o.Value.GetValueOrDefault(),
                o => GetLocalizedLabel(o, languageCode)
            );

            // Map selected values to labels
            var selectedLabels = values
                .Select(v => optionLabels.ContainsKey(v.Value) ? 
                optionLabels[v.Value] : $"(Unknown {v.Value})")
                .ToList();

            return string.Join(", ", selectedLabels);
        }

        private static string GetLocalizedLabel(OptionMetadata option, int? languageCode = null)
        {
            if (languageCode.HasValue)
            {
                var label = option.Label.LocalizedLabels
                    .FirstOrDefault(l => l.LanguageCode == languageCode.Value);
                return label?.Label ?? $"(No label for {option.Value})";
            }
            else
            {
                return option.Label.UserLocalizedLabel?.Label ?? $"(No label for {option.Value})";
            }
        }

        public static int GetCurrentUserLanguageCode(IOrganizationService service)
        {
            WhoAmIResponse whoAmI = (WhoAmIResponse)service.Execute(new WhoAmIRequest());
            Guid userId = whoAmI.UserId;
            QueryExpression query = new QueryExpression("usersettings");
            query.Criteria.AddCondition(
              new ConditionExpression("systemuserid", ConditionOperator.Equal, userId));
            query.ColumnSet.AddColumns("uilanguageid");
            Entity result = service.RetrieveMultiple(query).Entities.FirstOrDefault();
            return result != null && result.Attributes.Contains("uilanguageid")
                ? (int)result["uilanguageid"]
                : 1033;
        }
    }
}

方式二 使用工作流

这种工作流方式,我借助的是第三方工作流工具: Dynamics-365-Workflow-Tools ,它的 Github 仓库地址如下

Dynamics 365 Workflow Tools is a Community solution that expands Microsoft Dynamics 365 (CRM) Workflow features with lots of new posibilities. This helps you to build very advanced Codeless solutions in CRM.
C#

安装 Dynamics-365-Workflow-Tools

  1. 打开 Dynamics-365-Workflow-Tools Github 仓库
  2. 打开 Releases (右下方),下载 Dynamics 365 Workflow Tools Solution(建议下载 Latest 版)
  3. 将下载的解决方案导入系统,按照向导步骤操作

新建工作流

新建 Workflow

新建工作流-01

填写一个有意义的工作流名称 –> 点击 Create

新建工作流-02

选择 Workflow-Tools 选择 GetMultiSelectOptionSet

新建工作流-03

点击 Set Properties

新建工作流-04

填写/选择 参数 –> 保存并关闭

  • Source Record URL
  • Attribute Name
  • Retrieve Options Names

新建工作流-05

添加更新步骤 –> 点击 Set Properties

新建工作流-06

为字段赋值 –> 保存并关闭

新建工作流-07

最后激活工作流即可

新建工作流-08