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:
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!!!