Announcements
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.
Can't we get that value(No of participating records in actual) with any sort of configurations?
Ok. Thank you for your detailed information, Michel.
Now, I know exactly how it works^^.
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 :-)
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?
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 :-)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,807 Super User 2024 Season 2
Martin Dráb 229,135 Most Valuable Professional
nmaenpaa 101,156