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

CRM Client 4.0, Vista Business & Hosted CRM

Hi Guys

I have had a very trying day involving the CRM client, Vista Business and a number of firewall applications!

I have seen that a lot of people out there have had some issues with this setup, and there a number of registry hacks etc out there that people have tried.

However upon installing a fresh copy of Vista and Office on a test machine and enabling full blocking and tracking in Mcaffe firewall I found that there are three components of the CRM Client install that require external access, they are:

C:\Program Files\Microsoft Dynamics CRM\Client\ConfigWizard\Microsoft.Crm.Client.Config.exe

C:\Program Files\Microsoft Dynamics CRM\Client\ConfigWizard\Microsoft.Crm.Client.AutoUpdate.exe

C:\Program Files\Microsoft Dynamics CRM\Client\res\web\bin\ Microsoft.Crm.Application.Hoster.exe

I found once I had gone back through my clients personal firewall settings (which there were several) and enabled these three programs full access to the net, the install completed and they were able to use CRM, which meant that I could go home, Happy Days!

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!

Installing The Event Management Accelerator in a Hosted CRM environment

Hi Guys

I have been having a play around with the newly released accelerator on our hosted CRM platform, and I have to say so far so good.

I thought I would just drop down a few thoughts about the install process (which is very easy) and a few shots of what it should look like.

The very first thing that I found was that after the download had extracted itself, when I went to upload the customisations, CRM returned the error that the file path was too long, so my first tip is to extract the files to your own folder path rather than the default path and thus saving you a few minutes!

Once all customisations had been uploaded, it was then turn to publish the workflows, which as the instructions state cannot be published until you edit the three workflows that send emails, so you have to go in and change the sender from "Microsoft Web User" to a user within your organisation.

Once these are all published you should see within your Campaigns the added links on the left hand navigation.

The final part of the install is to add the eventmanagement plugin, by running the msa_eventmanagement.install.exe on your CRM server. You must remeber to state which organisation you are adding the plugin to (must be the same as the org you uploaded the customisations to)

Once this has successfully run, you will need to go into customisations and you will see the entity event management configuration, edit this entity and set it to show up in the settings area of CRM.

Finally go the settings area and you will see that the entity now appears as an admin area. Go into this area and you will need to set the first configuration settings

The settings should be as follows:

The Event status code values need to be read from the Status Code attribute of the campaign entity. We enter these manually just in case the import was not able to use the default values for Status Code. The following status codes should be used:
Event is Open for registration = 2 (Launched)
Event is Sold Out = 200001 (Sold Out)
Event is Waitlisting registrations = 200000 (Waitlisting)
These codes are used so that the portal can adjust the display of specific events and in the case of sold out events ensure that the web customer can’t register for the event.

The following Campaign Response Response Codes are used:
Registered = 200000 (Registered)
Registration Canceled = 200001 (Registration Canceled)
Waitlist = 200002 (Place this customer on the waitlist)

Additionally, we need to store the reference number for the Campaign Response Channel type which by default is set to 200000 for the web channel.

Once that is saved you are ready to start using Event Management!

Welcome

Welcome, one and all to my very first blog page, ever!

Over the next few weeks, months and years you will find helpful tips and tricks to help you get more out of Microsoft CRM and hopefully make your lives easier!

I have read many many helpful blogs while trawling for the answers to the many, many questions I have had about CRM and I will do my best to put all of the answers here and hopefully over time, this blog will become one of the first that you look for when you need help!

Please do comment, both good and bad are welcome (hopefully more good than bad ;P) and as I say I hope that this blog becomes an invaluable source of info!

Thanks

Al