web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to mask the data w...
Finance | Project Operations, Human Resources, ...
Answered

How to mask the data while exporting to Excel

(0) ShareShare
ReportReport
Posted on by D365_Wibes 184

Hi Team,

I have a requirement where the customer is using Microsoft standard excel export option,

SO when they are doing that then some columns data should be masked in Excel.

Can we achieve this via x++ ? Please give some suggestion.

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Which version of AX are you using?

    What do you mean by "masked"? Shouldn't you simply exclude such columns?

  • D365_Wibes Profile Picture
    D365_Wibes 184 on at

    Hi Martin,

    Thanks for your reply.

    Version is D365 Platform 23

    Masked when I export excel some columns in excel should have value such as (*******) instead of actual value.

    For Eg.

    Table A:

       Name     - isMasked = No

       Gender   - isMasked = No

       Salary     - isMasked = YES

    so when I export the above table in a custom or extended form, then in excel expect for column Salary the data should have been masked instead of actual value such as (*************)

    Can this be achievable by any way please?

    Or if we can exclude it while exporting excel then how can we do this ?

    please let me know any suggestions.

    And this customization should apply whenever an user opens any form

  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Hmm, are you saying that users have permissions to see the data, can copy it, can print it, can open it an Excel, but if and only if they use "Export the Excel", all data (in the given column) should be replaced with ************?

    It sounds rather strange to me. What business problem are you trying to solve by this design?

  • D365_Wibes Profile Picture
    D365_Wibes 184 on at

    Actully Martin, I have developed some customization.

    Where an Admin can choose Tables and fields for which the data has to be masked.

    And it is working fine in front End ,

    For example if the Admin has chosen (LogisticsElectronicAddress as Table) then (Locator as the Field).

    Then when user opens a form which has datasource as "LogisticsElectronicAddress " and field as "Locator " then the data will be masked and its working now.

    Only thing is when user exports data to excel then the field chosen is not being masked.

    So only I am looking for a way to customize microsoft standard excel export option to mask the values if the export contains a column in selected in my customization.

    Hope this clarifies you better

  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Let me ask explicitly: are you talking about "Export to Excel" or "Open in Excel"? Or both? Or are you talking about an export through data management?

    Notice that you mentioned masking data in forms, but you didn't said anything about data entities, which suggests that your solution doesn't cover any scenario based on data entities. Even if your current question is about something else, you'll need to close this gap.

  • D365_Wibes Profile Picture
    D365_Wibes 184 on at

    No Martin, I am not talking about Data Entities here.

    Its just I have a customization where an admin can choose (Table & Fields) that needs to be masked.

    So if user any form which has the same table and field as selected in the customization then the data has to be masked and it is working all good now.

    Only whenever user click on standard Export to excel option then our masking logic is not working.

  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Yes, I know that you aren't talking about entities - but you probably should, because that's what features like "Open in Excel" are based on. If you hide data in Export to Excel but not in Open in Excel, will it be useful at all? Again, it would helped if you told us what business problem you're trying to solve.

    Anyway, back to Export to Excel. How did you implement the masking in forms? It's hard to say why it's not respected by Export to Excel without knowing what you did.

  • D365_Wibes Profile Picture
    D365_Wibes 184 on at 

     public static void setDataMasking(FormStringControl _fSCtrl,FormRealControl _fRCtrl,FormDateControl _fDCtrl,
                                      str _tname4,WTL_DataMaskingTableLineMapping _configTable)
    {
        WTL_DataMaskingTableLineMapping sBuffer,rBuffer,dBuffer;
        UserGroupId userGroupId,userGroupId1;

        if (_fSCtrl && _fSCtrl.dataFieldName() == "Locator")
        {
            if (_fSCtrl.autoDeclaration() == NoYes::No)
            {
                _fSCtrl.autoDeclaration(NoYes::Yes);
            }
        }


        if (_fSCtrl                             && 
            _fSCtrl.dataMethod() == "")
        {
            userGroupId = WTL_DataMaskingTableLineMapping::findByFieldNameGrpId(_fSCtrl.dataFieldName());
            userGroupId1 = WTL_DataMaskingSettingsTable::retUserGrpId(userGroupId);
        }
        else if (_fRCtrl                             &&
                 _fRCtrl.dataMethod() == "")
        {
            userGroupId = WTL_DataMaskingTableLineMapping::findByFieldNameGrpId(_fRCtrl.dataFieldName());
            userGroupId1 = WTL_DataMaskingSettingsTable::retUserGrpId(userGroupId);
        }
        else if (_fDCtrl                             &&
                 _fDCtrl.dataMethod() == "")
        {
            userGroupId = WTL_DataMaskingTableLineMapping::findByFieldNameGrpId(_fDCtrl.dataFieldName());
            userGroupId1 = WTL_DataMaskingSettingsTable::retUserGrpId(userGroupId);
        }

        if (userGroupId)
        {
            if (!UserInfoHelp::userInUserGroup(curUserId(),userGroupId1))
            {
                if (_fSCtrl)
                {
                    str         sTableName,fSCtrl;
                    fSCtrl      = _fSCtrl.dataFieldName();
                    sTableName  = _tname4;

                    #define.colorcode(124,252,0);

                    sBuffer = WTL_DataMaskingTableLineMapping::findByFieldName(fSCtrl);

                    if (sTableName == _configTable.TableName    &&
                        fSCtrl == sBuffer.FieldName)
                    {
                        _fSCtrl.colorScheme(FormColorScheme::RGB);
                        _fSCtrl.backgroundColor(WinAPI::RGB2int(#ColorCode));
                        _fSCtrl.enableFormRef(NoYes::No);
                        _fSCtrl.passwordStyle(NoYes::Yes);
                    }
                }
                else if (_fRCtrl)
                {
                    str         rTableName,fRCtrl;
                    fRCtrl      = _fRCtrl.dataFieldName();
                    rTableName  = _tname4;

                    #define.colorcode(124,252,0);

                    rBuffer = WTL_DataMaskingTableLineMapping::findByFieldName(fRCtrl);

                    if (rTableName == _configTable.TableName    &&
                        fRCtrl == rBuffer.FieldName)
                    {
                        _fRCtrl.colorScheme(FormColorScheme::RGB);
                        _fRCtrl.backgroundColor(WinAPI::RGB2int(#ColorCode));
                        _fRCtrl.foregroundColor(WinAPI::RGB2int(#ColorCode));
                    }
                }
                else if (_fDCtrl)
                {
                    str         dTableName,fDCtrl;
                    fDCtrl      = _fDCtrl.dataFieldName();
                    dTableName  = _tname4;

                    #define.colorcode(124,252,0);

                    dBuffer = WTL_DataMaskingTableLineMapping::findByFieldName(fDCtrl);

                    if (dTableName == _configTable.TableName    &&
                        fDCtrl == dBuffer.FieldName)
                    {
                        _fDCtrl.colorScheme(FormColorScheme::RGB);
                        _fDCtrl.backgroundColor(WinAPI::RGB2int(#ColorCode));
                        _fDCtrl.foregroundColor(WinAPI::RGB2int(#ColorCode));
                    }
                }
            }
        }
    }

    Please find my above code which I have created for masking the data in to forms

  • D365_Wibes Profile Picture
    D365_Wibes 184 on at 

    [SubscribesTo(classStr(FormRun), staticDelegateStr(FormRun, onFormRun))]
    public static void FormRun_onFormRun(FormRun _formInstance)
    {
        Query qrUserGrp = new Query();
        QueryRun qrRun;
        WTL_DataMaskingSettingsTable dataMskTable;

        qrUserGrp.addDataSource(tableNum(WTL_DataMaskingSettingsTable));
        qrRun = new QueryRun(qrUserGrp);

        while (qrRun.next())
        {
            dataMskTable = qrRun.get(tableNum(WTL_DataMaskingSettingsTable));

            if (dataMskTable.IsDataMask == NoYes::Yes)
            {
                if (WTL_DataMaskingGroupParameters::findByGroupId().DataMasking == NoYes::Yes)
                {
                    //if (!UserInfoHelp::userInUserGroup(curUserId(),WTL_DataMaskingGroupParameters::findByGroupId().Id))
                    if (!UserInfoHelp::userInUserGroup(curUserId(),dataMskTable.UserGroupID))
                    {
                        WTL_DataMaskingEngine::checkIfDataSource(_formInstance);
                    }
                }
            }
        }
    }

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    All you've changed is the visualization in the form; it has no impact on data exports, permissions or anything like that.

    Regarding Export to Excel in particular, I see three options:

    1. Don't use a field with the actual data at all. For instance, you can use a display method (or edit method if needed) that returns masked data.
    2. You'll find a way how to modify the logic for Export to Excel. But I see it unlikely, unless you create an extensibility request to Microsoft. If you wish, look at ExportToExcelController class.
    3. You may claim that exporting data from controls with PasswordStyle=Yes is a bug and ask Microsoft to fix it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 646 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 529 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 285 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans