Showing posts with label CRM. Show all posts
Showing posts with label CRM. Show all posts

Tuesday, 13 September 2011

Report Builder – Replace Empty Table Values with a zero

If you are creating a highly customised report you may come across a situation where your dataset may not return any results.

If this happens your table with render with blank.  I have come across a solution to enable these empty / null values to be replaced with zeros if the dataset query returns no results

Consider the following example report


The Total column uses the following query to sum the number of records from the dataset results where the prioritycodename = 2
 

=Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0))


If the dataset returns no results this field will be blank.  On an SLA report this is not desirable.  I would prefer to see a 0 instead.  To achieve this I used the following query
 

=IIF(ISNOTHING(sum(Fields!prioritycodename.Value)),0,Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0)))
 

In English the above query reads as,

If sum of prioritycodename equals zero, display a zero in the table, otherwise count the instances of prioritycodename where prioritycode = 2

I think you will agree the report looks a lot better with zeros than blank rows / lines

Tuesday, 23 August 2011

Dynamics CRM ActivityPointer Master / Detail Report

In the following post I am going to explain how I created a Master / Detail Report Using Report Builder 3.0 and Dynamics CRM 2011.  This report is also known as a Header and Detail Report.  I am going to use the FilteredActivityPointer (ActivityPointer) table to display all activities for each Incident (Case).

1)      Start by creating a blank report in Report Builder.  Create your Data Source and then your Data Set
I have used the SQL following for my dataset

SELECT top 100
FilteredActivityPointer.activitytypecodename,
FilteredActivityPointer.subject,
FilteredActivityPointer.description,
FilteredActivityPointer.statuscodename,
FilteredActivityPointer.createdbyname,
FilteredIncident.ticketnumber,
FilteredIncident.title,
FilteredIncident.customeridname,
FilteredIncident.responsiblecontactidname,
FilteredIncident.createdon
FROM         FilteredActivityPointer INNER JOIN
                      FilteredIncident ON FilteredActivityPointer.regardingobjectid = FilteredIncident.incidentid
where activitytypecodename != 'Case Resolution'


2)      Add a List to the report

3)      In the Tablix Properties page.. select the dataset to be used



4)      At the bottom of the Report Builder Applciation window, Right Click Details and Select Group Properties…




5)      On the Group Properties window, choose to Add a Group Expression on the general tab.

I have chosen to add ticketnumber





6)      You are now free to add your Text Fields and your corresponding Tablix.

The Name, Date, Case Number and Case Title are Text Boxes.

The table was created using a Matrix.




The final result should be similar to the following.  Here you can see the Activities for each Case where the Case details are the Master and the Activities are the Slave / Detail information.


 If anybody wants to know how to do the Activity Icons – Leave a comment or contact me on twitter and I will be more than happy to oblige.
Tony


Tuesday, 21 June 2011

Microsoft Dynamics CRM 4.0 Error Message – Font ‘Verdana’ does not support style 'Bold’

While performing both an update to Microsoft Dynamics CRM 4.0 Update Rollup 16 and also while running the Mictrosoft Dynamics CRM Configuration Wizard I received the following error message





Exception has been thrown by the target of an invocation. Font 'Verdana' does not support style 'Bold'.



I would normally use the CRM Diagnostics tool to look at the root cause but this was a remote installation that needed to be completed to a tight deadline.  Seeing this was an issue with a font I imedaiately looked towards the C:\Windows\Fonts folder.

My first thought was to take a copy of the Verdana font from another working machine and copy this to the machine having the issue. 

Copying the Verdana font to a shared location will create four files.  These will need to be copied to the C:\Windows\Fonts on the machine having the issue


Once this was completed I was able to run Rollup 16 and the Configuration Wizard.  This extra step tool an additional 10 minutes but I was still able to meet the agreed time for the installation with the user.

Friday, 10 June 2011

Dynamics CRM 4.0 for Outlook – Invalid Argument

If you use the From field in Microsoft Outlook while using Microsoft Dynamics CRM 4.0 and you Track the email, you may experience the following error when trying to send the email.





Invalid Argument Do you want to send the e-mail? If you click Yes, the e-mail will be sent out, but no corresponding activity will be created in Microsoft Dynamics CRM.


To begin resolving this issue I went straight to the CRM 4.0 Diagnostics Tool and ran a Trace from the Microsoft Dynamics CRM Server.  Once I had replicated the error, I Found the following error message in the trace file.

>CrmSoapExtension detected CrmException:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.Crm.CrmArgumentException: The specified sender type is not supported. ---> System.ArgumentException: The specified sender type is not supported.

From my own experience of troubleshooting the Email Router I knew that the Invalid sender type was like due to email address I was trying to send from was in the system twice.
To troubleshoot further I used the brilliant script provided by Ben Lec (http://blogs.msdn.com/b/benlec/archive/2008/12/23/incoming-e-mails-are-not-tracked-in-crm.aspx)
For the email address below I used the email I was trying to Send FROM (Not my own)


declare @emailAddress as varchar(100)

set @emailAddress = 'support@tnyCRM.com'

SELECT FullName, SystemUserId, InternalEmailAddress, PersonalEMailAddress
FROM SystemUserBase
WHERE InternalEMailAddress = @emailAddress
OR PersonalEMailAddress = @emailAddress


SELECT Name, QueueId, EMailAddress
FROM QueueBase
WHERE EMailAddress = @emailAddress

SELECT Name, AccountId, EMailAddress1, EMailAddress2, EMailAddress3
FROM AccountBase
WHERE EMailAddress1 = @emailAddress
OR EMailAddress2 = @emailAddress
OR EMailAddress3 = @emailAddress

SELECT FullName, ContactId, EMailAddress1, EMailAddress2, EMailAddress3
FROM ContactBase
WHERE EMailAddress1 = @emailAddress
OR EMailAddress2 = @emailAddress
OR EMailAddress3 = @emailAddress

SELECT Subject, LeadId, EMailAddress1, EMailAddress2, EMailAddress3
FROM LeadBase
WHERE EMailAddress1 = @emailAddress
OR EMailAddress2 = @emailAddress
OR EMailAddress3 = @emailAddress

In my results I found that a Queue and a User were sharing the same E-mail address.  This was creating the Invalid Argument error.  Removing the email address from the user and keeping the address in the queue enabled me to send the email.

This issue occurs because when CRM tries to send an email through outlook and the sender is support@tnyCRM.com, it will search in the whole database to see who has an email address equal to support@tnyCRM.com.
If other records, for example an Account, Lead, Contact, User or Queue are also using support@tnyCRM.com in the From field in their email, the system will not know which record to track the email against and the email will not send as Tracked.

Friday, 27 May 2011

Dynamics CRM 4.0 for Outlook – A SQL Error occurred

This is an error I am sure many organisations that have deployed Microsoft Dynamics CRM 4.0 for Outlook will have seen.  Typically, this happens when a user selects “Track in CRM” in Outlook and that email is quite large or contains attachments.  




A SQL Server error occurred. Try this action again. If the problem continues check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support.

To begin resolving this issue I went straight to the CRM 4.0 Diagnostics Tool and ran a Trace from the Microsoft Dynamics CRM Server.  Once I had replicated the error, I Found the following error message in the trace file.




>CrmSoapExtension detected CrmException:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I found the resolution to this issue in KB 918609 and the steps I carried out were as follows

(Steps take from http://support.microsoft.com/kb/918609 at 24/05/2011 10:39)

1.       Click Start, click Run, type regedit, and then click OK.
2.       Locate the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM


3.       Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.
4.       Rename the DWORD value to the following value:
OLEDBTimeout
5.       Right-click the DWORD value, and then click Modify.
6.       In the Edit DWORD Value dialog box, type 86400 in the Value data box, click Decimal in the Base option, and then click OK.


Note According to the requirement of the computer that is running SQL server and the number of customization files, the value can be larger than 86400. The value of 86400 is equivalent to 24 hours.
7.       Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.
8.       Rename the DWORD value to the following value:
ExtendedTimeout
9.       Right-click the DWORD value, and then click Modify.
10.   In the Edit DWORD Value dialog box, type 1000000 in the Value data box, and then click OK.

                        

Notes
o    In the Value data box, you can type a value that is larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. This is hexadecimal 0x7FFFFFFF.
o    If this key already exists, notice the current value. After you have completed the import or the upgrade for Microsoft Dynamics CRM, set the value of this key back to the original value or delete the key if it did not previously exist. The default OLEDB timeout value is 30 seconds.