Filtering Custom CRM reports

Hi Guys

I have had much fun over recent days producing custom reports for our clients and as I am learning and making the reports flashier, I have finally learnt what you need to do to add a filter to your reports.

Firstly, you must alias the tables that you are retrieving data from with CRMAF_, for example

select *
from filteredaccount as CRMAF_filteredaccount

Secondly, if you want to specify which columns you wish to select you include the CRMAF reference in your select query

select CRMAF_FilteredAccount.name
from filteredaccount as CRMAF_filteredaccount

It is very basic I know, but if like me you have spent time creating reports in CRM then editing them in Visual Studio, just to have the filter, this saves a lot of time!

Happy Reporting!

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!