Display the last Note on an Opportunity Report

Hi Guys

One of my clients recently requested that we design an opportunity report that also provided the last note added, so they could have an easy summary report to present to their board of directors.

After a few hours scratching my head and I must admit a few nearlys it got time to call on the knowledge of others, and with thanks to Joel from Customer Effective, I was able to produce the correct SQL statement and then design the report is Visual Studio...

anyways here is the SQL:

select *
from(select filteredannotation.objecttypecode, filteredannotation.notetext, filteredannotation.objectid, filteredopportunity.accountidname, filteredopportunity.customeridname,filteredannotation.isdocument, filteredannotation.createdon, filteredopportunity.stepname, filteredopportunity.estimatedvalue, filteredopportunity.estimatedclosedate, filteredopportunity.closeprobability, filteredopportunity.statecodename,ROW_NUMBER()OVER(Partition By filteredannotation.objectid Order By filteredannotation.createdon DESC) AS RowNmbrfrom filteredannotation, filteredopportunitywhere filteredannotation.objectid = filteredopportunity.opportunityid) as test
where rownmbr = 1

So once this query was added to a new dataset, the report was designed and showed only the last note added!

So special thanks to Joel and a Merry Christmas to everyone!!!

Custom Reporting in CRM 4.0

So after my relatively successful installation of the Event Management Accelerator, I have yet to test out how it actually works! But there is a reason for this... I have been thrust head first into writing Custom Reports for a particular client of ours... not one of my favorite subjects, but one that surprisingly I am finding quite enjoyable!

I have been playing around with Visual Studio and it is very good at making the reporting minefield relatively trouble free... and if I ever do run into any issues there is always the trusty geniuses on http://forums.microsoft.com/Dynamics/default.aspx?siteid=27 to help you out!

My major triumph today, however was a small piece of SQL code that enabled me to display the months to close a particular opportunity on this said report, now it may not seem much, but for me, it was like almost reaching the top of Everest!

anyways here is the code for months

=sum(DateDiff(DateInterval.Day, Fields!createdon.Value, Fields!actualclosedate.Value)/28)

However, the pièce de résistance has to be the average time that it takes to close opportunities and this code took a little while to get my head around the logic... but here it is...

=AVG(DateDiff(DateInterval.Day, Fields!createdon.Value, Fields!actualclosedate.Value)/28)

As you can probably tell, I'm rather pleased with myself so I think it is now time to go for coffee!