Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Sales forum / Goal Management - Part...
Sales forum

Goal Management - Participating records

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi All,

In the ebook of our PowerObjects friends, we can access to a chapter dedicated to goal management (https://crmbook.powerobjects.com/basics/analytics/goal-management). They tell us that ...

"From within the Goal, you can see the last time the goal was calculated under the Actuals section.

To left under Participating Records you can see the records that are being used in the calculations of the goals."

My question:

If I want to query these participating records in SQL Database (for example): are they stored somewhere? If yes, in which entity?

Thank you for your help.

Categories:
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Goal Management - Participating records

    Can't we get that value(No of participating records in actual) with any sort of configurations?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Goal Management - Participating records

    Ok. Thank you for your detailed information, Michel.

    Now, I know exactly how it works^^.

  • Verified answer
    Michel van den Brink Profile Picture
    Michel van den Brink 4,697 on at
    RE: Goal Management - Participating records

    Hello,

    The records you are looking at are stored very much stored in the database, they are inthe Opportunity entity. Each participating record is represented by 1 Opportunity record for each. And when ever you open the subgrid for a specific goal, a FetchXml query is dynamically generated and run against the Opportunity entity, pulling it from the database.

      

    From a SQL perspective: this entity is represented by several tables in the database and can most easily be queried via the view called 'FilteredOpportunity', which brings all the information together.

      

    In the application itself it's best to use FetchXml because it is available in all versions of Dynamics both Online and On-Prem.

      

    Hope this helps you. If you have any other questions, please let me know!

    If you found my answer helpful, please help the community by marking it as verified :-)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Goal Management - Participating records

    Thank you for your feedback, Michel.

    Else if I know what is FetchXML, your explanations are very clear.

    So when I want to have a look at partipating records from my Goal form, does the system execute dynamically the FetchXML? Participating records are not stored anywhere, that's it?

  • Suggested answer
    Michel van den Brink Profile Picture
    Michel van den Brink 4,697 on at
    RE: Goal Management - Participating records

    Hello,

    Dymamics 365 uses a query language called FetchXML to query records. These can be translated to SQL but, in Dynamics 365 (CRM) Online you do not have direct access to the database.

    You can use the Fetch XML tool from the XrmToolBox ( https://www.xrmtoolbox.com/plugins/ ) to run FetchXml queries against your Dynamics 365 organization.

      

    The records you see under 'Participating' records are Opportunities.

    The system runs the following query for 'Actual':

    <fetch distinct="false" no-lock="true" mapping="logical" page="1" count="250" returntotalrecordcount="true">
  <entity name="opportunity">
    <attribute name="name"/>
    <attribute name="estimatedvalue"/>
    <attribute name="statuscode"/>
    <attribute name="estimatedclosedate"/>
    <attribute name="opportunityid"/>
    <attribute name="processid"/>
    <attribute name="name"/>
    <attribute name="estimatedvalue"/>
    <attribute name="statuscode"/>
    <attribute name="estimatedclosedate"/>
    <link-entity name="workflow" to="processid" from="workflowid" link-type="outer" alias="processidworkflowworkflowid">
      <attribute name="versionnumber"/>
    </link-entity>
    <attribute name="statecode"/>
    <filter type="and">
      <filter type="and">
        <condition attribute="ownerid" operator="eq" value="{00000000-0000-0000-0000-000000000000}"/>
        <condition attribute="actualclosedate" operator="between">
          <value>2017-12-31T00:00:00</value>
          <value>2018-03-31T00:00:00</value>
        </condition>
        <condition attribute="statecode" operator="eq" value="1"/>
      </filter>
    </filter>
    <order attribute="name" descending="false"/>
  </entity>
</fetch>

    Please note that you need to replace the underlined values. Replace the GUID with the record ID of the user who owns the goal, and the start and end dates with the start and end dates of your goal.

    This would be the SQL equivalent:

    SELECT
	[name],
	[estimatedvalue],
	[statuscode],
	[estimatedclosedate],
	[opportunityid],
	[processid],
	[estimatedvalue],
	[statuscode],
	[estimatedclosedate],
	[statecode],
	[processidworkflowworkflowid].[versionnumber]
FROM
	[FilteredOpportunity]
	LEFT OUTER JOIN [workflow] AS [processidworkflowworkflowid]
	ON [processidworkflowworkflowid].[workflowid] = [FilteredOpportunity].[processid]

WHERE
	[FilteredOpportunity].[ownerid] = '00000000-0000-0000-0000-000000000000'
	AND [FilteredOpportunity].[statecode] = 1
	AND [FilteredOpportunity].[actualclosedate] BETWEEN '2017-12-31' AND '2018-03-31'

    The system runs this query for 'In Progress':

    <fetch distinct="false" no-lock="true" mapping="logical" page="1" count="250" returntotalrecordcount="true">
  <entity name="opportunity">
    <attribute name="name"/>
    <attribute name="estimatedvalue"/>
    <attribute name="statuscode"/>
    <attribute name="estimatedclosedate"/>
    <attribute name="opportunityid"/>
    <attribute name="processid"/>
    <attribute name="name"/>
    <attribute name="estimatedvalue"/>
    <attribute name="statuscode"/>
    <attribute name="estimatedclosedate"/>
    <link-entity name="workflow" to="processid" from="workflowid" link-type="outer" alias="processidworkflowworkflowid">
      <attribute name="versionnumber"/>
    </link-entity>
    <attribute name="statecode"/>
    <filter type="and">
      <filter type="and">
        <condition attribute="ownerid" operator="eq" value="{00000000-0000-0000-0000-000000000000}"/>
        <condition attribute="estimatedclosedate" operator="between">
          <value>2017-12-31T00:00:00</value>
          <value>2018-03-31T00:00:00</value>
        </condition>
        <condition attribute="statecode" operator="eq" value="0"/>
      </filter>
    </filter>
    <order attribute="name" descending="false"/>
  </entity>
</fetch>

    And the SQL equivalent

    SELECT
	[name],
	[estimatedvalue],
	[statuscode],
	[estimatedclosedate],
	[opportunityid],
	[processid],
	[estimatedvalue],
	[statuscode],
	[estimatedclosedate],
	[statecode],
	[processidworkflowworkflowid].[versionnumber]
FROM
	[FilteredOpportunity]
	LEFT OUTER JOIN [workflow] AS [processidworkflowworkflowid]
	ON [processidworkflowworkflowid].[workflowid] = [FilteredOpportunity].[processid]

WHERE
	[FilteredOpportunity].[ownerid] = '00000000-0000-0000-0000-000000000000'
	AND [FilteredOpportunity].[statecode] = 0
	AND [FilteredOpportunity].[actualclosedate] BETWEEN '2017-12-31' AND '2018-03-31'

      

    Hope this helps you get started. If you have any other questions, please let me know!

    If you found my answer helpful, please help the community by marking it as verified :-)

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

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,431 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,503 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans