Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
Hi Friends ,
I need a report to fetch the opportunity status changed by , changed date and some column of opportunity field using MS SQL query.
SELECT O.AccountIdName as Account, O.modifiedbyname , A.ChangeData,
O.createdon , O.modifiedon, O.createdbyname, O.modifiedbyname ,O.StatusCode , A.Action,
WHEN O.StatusCode = 1 THEN 'Progress' WHEN O.StatusCode =2 THEN 'Hold' WHEN O.StatusCode =3 THEN 'Won' WHEN O.StatusCode =4 THEN 'Cancelled' WHEN O.StatusCode =5 THEN 'Out Sold' WHEN O.StatusCode = 240220000 THEN 'Completed Won' WHEN O.StatusCode = 240220001 THEN 'Completed Won' WHEN O.StatusCode = 240220002 THEN 'COMPLTED LOST' WHEN O.StatusCode =240220002 THEN 'COMPLETED WON DEFEND'END
AS [Status Reason]
FROM AUDIT AINNER JOIN Opportunity O ON A.objectid = O.opportunityid WHERE A.Action IN (2) AND o.StatusCode IN (240220002,240220003,240220000,240220001,1,2,3,4) GROUP BY AccountIdName as Account, O.modifiedbyname , A.ChangeData, O.createdon , O.modifiedon, O.createdbyname, O.modifiedbyname ,O.StatusCode , A.Action, O.StatusCode
ORDER BY ModifiedOn DESC
In this query it return all the data which are updated and have status code .
I dont know how i get only thoes data whoes status has been changed.
I''m busy with the same requirement just with leads instead of accounts/opportunities...
In your where clause replace the "AND o.StatusCode IN (240220002,240220003,240220000,240220001,1,2,3,4)" with "AND A.AttributeMask LIKE '%54%'"
*remove the "" (double quotation marks)
AttributeMask column 54 is the StatusReason column within the Audit entity so you exclusively telling SQL to only return these updates
I hope this helps?
In my case the users want to see the status reason update per month on all active leads - so I would probably have to write 2 queries, 1 to pull all the lead records and 2 to only explicitly call the status reason updates in order to update the monthly columns
Business Applications communities