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 / Dynamics 365 general forum / FilteredStringMap and ...
Dynamics 365 general forum

FilteredStringMap and SQL Reports

(0) ShareShare
ReportReport
Posted on by Shahis 170

Hello,

We're trying to create a SQL based report. One of our custom entity have two OptionSet fields, say Field1 and Field2. I understand that the FilteredStringMap view can be queried to get the Label Values and Numeric Values for these fields. We want to display both the fields in the report, however since all the Label Values are stored in a single field (Value), we're unsure how we would be able to display the Label Values for individual fields in their respective column. Any suggestions/Ideas would be greatly appreciated. 

Thanks,

Shahid

Categories:
Customization / Solutions / SDK and API
Integration with Other Products
  • Suggested answer
    Adrian Begovich Profile Picture
    Adrian Begovich 1,025 Super User 2025 Season 1 on at
    RE: FilteredStringMap and SQL Reports

    Hi Shahid Shaikh,

    There is a way to display all attendees of a single activity in a single row. You can achieve this by retrieving the data and displaying it in a Table or a Matrix.

  • Shahis Profile Picture
    Shahis 170 on at
    RE: FilteredStringMap and SQL Reports

    Thanks Wei and Adrian. Both your answers were perfect. Another question i have, if i may ask here is, this report is being created on a custom Activity Entity. So, we have requiredattendees field which contains one or more attendees. Is there a way to display all attendees of a single activity in a single row?

    Thanks,

    Shahid

  • Verified answer
    Alex Fun Wei Jie Profile Picture
    Alex Fun Wei Jie 33,626 on at
    RE: FilteredStringMap and SQL Reports

    Hi,

    You dont have to use stringmap in order to get the optinsetname,

    as long as you are using filteredview , you can easily to get the optionsetname via below method.

    Eg : Optionset field name - new_optionset

    select new_optionset, new_optionsetname

    from filteredaccount

  • Suggested answer
    Adrian Begovich Profile Picture
    Adrian Begovich 1,025 Super User 2025 Season 1 on at
    RE: FilteredStringMap and SQL Reports

    Hi  Shahid Shaikh,

    In your SQL Query, you will need to retrieve OptionSet and the OptionSetName for Field1 and Field2.

    Here is an example of a SQL Query:

    SELECT optionset, optionsetname FROM FilteredEntity;

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Dynamics 365 general forum

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans