Dynamics CRM Report Modification Walkthrough

There are quite some reports in the out of the box installation of Dynamics CRM 4.0, but these sometimes do not match the exact business requirements. Also the reports which can be created by using the Dynamics CRM Report Builder Wizard cannot contain complex calculations or information of more than two entities. When you do export one of these reports and try to edit this in Visual Studio, then you will end up with errors. This post will guide you through these errors and modify a default report. For this example we will change the default “User Summary” report. This of course works for reports generated with the Report Wizard as well.

Exporting a report
The first step is to download a report from Dynamics CRM. You can do this by browsing to the Workplace and open the reports are. In the grid select the report which you want to modify and press “Edit Report”.



Clicking on “Edit Report” will open the Report Detail page. On this page click “Actions” and select “Download Report”. This will let you download the *.rdl file which is the definition of the report. Save this file to some location on your hard drive.



Opening the report in Visual Studio 2005
Keep in mind that the report must be opened in an editor which supports the rdl for SQL Server 2005. For Visual Studio this is version 2005. With 2008 you cannot change report files for CRM. When you directly open the report in Visual Studio you will see that the xml viewer will be opened.



Although you can modify a report in XML, there is an easier way to modify reports in Visual Studio. You will first need to set up a project for Reports. In Visual Studio select the “Report Server Project” from the “Business Intelligence Projects” group. If this is not available, install the “Business Intelligence Development Studio Add-In for Visual Studio 2005” from the SQL Server installation CD.



From the “Solution Explorer” right click on “Reports” and add an existing Report. From the report selection screen select the report which you have saved in the previous step.



The report will now be added to the solution under the reports tree view item. When you open this report now, you will get into a mode in which you can modify the report.



Connecting to Dynamics CRM
The report does have three tabs for defining the dataset, modifying the layout and previewing the report output. The first step in changing a report is to change the dataset. Every report which is exported from Dynamics CRM does have a hardcoded data source set. This will need to be changed to your current environment before you can modify the report. To do this click on the “Data” tab. You will get the following error message.
A connection cannot be made to the database. Set and test the connection string:



This is the message specifying the situation as I have just described. Just click “OK” and let’s change the data source. To do this click on the “…” next to the dataset pick list.



This will give you the “Dataset” detail form. On this form click on “…” next to the data source picklist.



This will give you the “Data Source” detail form. On this screen click on Edit next to the connection string.



This will give you the “Connection Properties” detail form. On this page verify that the server name is correct. Also select the correct database name. This should be in the format of “organizationname_MSCRM”.



After clicking on “OK” several times you will get back to the main screen. This will be refreshed and more information will be visible now.

Changing the Query
Now that a connection is created you can change the query. Unfortunately there is not just a single query. There are multiple Datasets with each a separate query. You’ll first need to find the correct Dataset before you start changing the query. By looking at the queries you will probably be able to determine which dataset you should use. Most likely the query starts with the declaration of a dynamic query like “Declare @SQL Varchar(4000)”.
Once you have found the query you can modify the query. I do expect that the people reading this article do have knowledge of how to change these queries, if not than you might want to find somebody else to change the query for you.
Once the query is modified and does return the attributes you want to use in your report, then there is an important step you should execute. If you miss the following steps, then you’ll get this error messsage “An error occurred during the local report processing. The definition of the report '/User Summary' is invalid.”. For some reason Visual Studio removes the “Fields” from the dataset. This basically is the mapping between the attributes returned by the query and the variables used in the report. You can verify that the list of Fields is empty by opening the Dataset detail form (click on the “…” next to the dataset pick list). On the Dataset detail form click “Fields”.



You can manually enter each of the fields and values, but you can also let Visual Studio regenerate this list. To generate this list you can click the “Refresh Fields” in the Dataset toolbar.


Clicking this toolbar button will ask you to define query parameters.



This screen doesn’t automatically fill the default values, but the values are available in the report though. You can find the values which you should fill in here on the “Report Parameters”. You can access these from the “Layout“ tab. Somewhere on the layout screen right click outside of the report somewhere on the yellow piece. This will give you a context menu where you can select the “Report Parameters”.



On the “Report Parameters” form you can select the property on the left side on the screen and at the right bottom you can find the default value for the selected property.



Copy the default value and past this into the “Parameter Value” for the “Parameter Name”.



Now click “OK” and the query is changed AND you can use the selected attributes in the report layout editor.

Changing the Layout
The layout can be changed on the “Layout” tab of the report. There are many possibilities to change the layout, but I won’t dive into the Reporting Services possibilities. I will just show how to add the field which is added to the query, but for more information you should look into Reporting Services trainings.
To add a field to the result table select the table and right click on the header. You’ll now get a context menu which will allow you to add a row group. This is the value you need to select to add a column. In the “Expression” field you should select your newly added attribute in this format “=Fields!address1_fax.Value”. For the label you could add a Textbox from the toolbox and change its properties to match the existing labels, but you could also just copy an existing label and change the text value.



Previewing the Report
To review the report, just click the “Preview” tab. If necessary you can go back to the Layout or Data tab to change the report. When you are ready, then save the report to an *.rdl file.



Updating the report in CRM
In Dynamics CRM, go back to the Reports area in the Workplace module. In the grid select the report which you have just modified. In the toolbar click on “Edit Report”.



On the Report detail form select the saved report definition file by using the browse button for the file location. Now press “Save” or “Save and Close” and your report is updated with your new definition file. From CRM you can now run the updated report.

Creating an activity report which includes the related people

In an activity CRM grid, it is not possible to add attributes from the activity type (letter, phonecall etc) itself. The fields to and from on the entities phonecall, letter, fax are therefore not eligable for addition on the CRM grid. It would be very useful to see those though. The same is valid for the to, cc and bcc in email and required and optional attendees in appointments. In this post I won't be giving a solution to show the attributes in the grid, instead I will give a workaround by using reports.

The only attributes which you can select in the grid are the attributes which are belonging to the entity activitypointer. These include the activityid, startdate, statecode, but also the regardingobjectid. So the question is, how to get the to, from, cc etc. For this you can use the function which I have posted in my previous post. This function accepts an ActivityID and an ActivityPartyType. So what is this type? Look at this page: ActivityPartyType. You will find a list of values mapped to what kind of field you want to add to your report.

By using that function you can create your query for the report. An example would be:

SELECT
activityid, activitytypecode, scheduledstart, subject, owneridname, statecodename,
regardingobjectidname,
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 1)) [to],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 2)) [from],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 5)) [required]
FROM
filteredactivitypointer

This query does select some default attributes and it adds the regarding, to, from and required fields. Add this query to the generation of a report and you'll be set to go.

Note: make sure that the function gets added to your database and assign the correct rights. See the post around the function for details.

Happy reporting!

Transform a table column into a CSV field in SQL Server

Imagine that you want to use a related table in your SQL query. You then must return only a single column or otherwise you'll get this SQL error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What I like to do, is to transform the result table column into a single field separated by a ; sign. Earlier on I used to create a huge function which used cursors etc. Then a colleague of mine told me how to use C# code in SQL server which seemed to be a neater solution.

Today I have found a new approach. This uses a function again, but it is very simple. Here's the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ronald Lemmen
-- Create date: 4 July 2008
-- Description: Function to return the people in CSV format belonging to a activity based on the activity party type code
-- =============================================
CREATE FUNCTION fn_RL_GetActivityPartyList
(
@ActivityId uniqueidentifier,
@ActivityPartyType int
)
RETURNS nvarchar(3000)
AS
BEGIN
DECLARE @result nvarchar(3000)

SET @result = ''

SELECT @result = Coalesce(@result + ';', '') + partyidname from filteredactivityparty party where party.activityid = @ActivityId and party.participationtypemask = @ActivityPartyType

RETURN SUBSTRING(@result ,2, LEN(@result))
END
GO

The @result will get filled by the SELECT query with the values from the filteredactivityparty table separated by a ;. The first character is a ; as well and therefore I do remove this one in the RETURN statement.
After the + in the SELECT query you can place your own query. This example is just extremely useful in my next post :)

Make sure that you do grant access to the correct people in order to use this function. In the case of a CRM report, add the reporting group. See the below example, but make sure that you do change the guid to the guid which is valid for your system.

GRANT EXECUTE ON [dbo].[fn_PGGM_GetActivityPartyList] TO [PGGM-INTRA\ReportingGroup {05e0584a-1d94-424c-8014-c2f3b1b92ccc}]
GO

Happy reporting!

AttributeInfo.TypeName(int) != AttributeMetadata.Type.Name(float)

You might bump into an error message while importing an entity from a customizations file. The error show could be:

Error: lead: AttributeInfo.TypeName(int) != AttributeMetadata.Type.Name(float)

The cause of this error is a mismatch in attribute type. Usually a field has been removed in the other environment and has been recreated with another attribute type. The type in your destination CRM system of an attribute is the type displayed in the right side of the != statement. In the example above float. The type in the import file is the type displayed on the left side of the != statement. This is int in the example above. In order to get rid of this error message and to import the entity, you will need to remove the attribute from the destination system.

In the list of attributes, search for a custom attribute which has the same type as displayed on the right side of the equation. Determine if there is any of these which has been changed. Delete this attribute and import the entity again. Keep in mind that the attribute must be removed from the form first (also the published form) before it can be removed from the attribute list. Another thing to keep in mind, is that when you perform this action on a production system, that the data in the attribute will be removed. Since this is not recoverable, you should first perform the removal of the attribute by following the next steps:
- create a new custom temporary attribute
- copy the data from the old attribute to the temporary attribute for each record
- remove the attribute
- import the entity (and with that the new attribute)
- copy the data from the temporary attribute to the new attribute for each record
- remove the temporary attribute

If there are many custom attributes, it might be hard to locate which of these is causing the error to appear. Unfortunately the name is not in the error description and also the event viewer and tracing does not mention the exact attribute name. To find out which of the attributes is causing the error, you should start the database profiler. Start a new trace and run the import. As soon as the error shows up, stop the tracing. In the trace search for the last occurence of:

exec sp_executesql N'SELECT T1.AttributeId AS attributeid

When you have found this record, copy the whe query. When you run this query in the SQL Server Management Studio, then only a single record should be returned. One of the attributes in the result is the name attribute. This is the attribute which is changed and will need to be removed.

Disclaimer: Only perform actions on the database if you are comfortable with the technologies. Incorrect SQL statements can corrupt your database and cause mayor dataloss. Always make a backup before performing database queries against your CRM database.

Improve performance of Microsoft Dynamics CRM

The performance of your CRM implementation is one of the key factors for customer acceptance. There are multiple options available for optimizing your system. Here's a list to get started:

Microsoft - Optimize Microsoft Dynamics CRM 3.0

Microsoft - Optimize Microsoft Dynamics CRM 1.2

Aaron Elder - Optimize the CRM Webservice Calls

Brein Reid - Reduce the amount of useless calls to IIS 6.0 Applications for optimal Performance

Some more tips:
- Do NOT virtualize your SQL Server.
- Run CRM from a computer with a physical network connection instead of wireless.
- In your code use as least webservice calls as posssible.
- Look in the implementation guide on how to cluster your SQL Server and/or load balance your webserver.
- If requirements allow you to do so, run the callout code asynchronous. In the callout just create an MSMQ message and create a windows service which listens to this MSMQ and execute the code.
- In imports and migrations, try to disable as many workflows and callouts as possible.

Reducing the transaction log file size in MS SQL 2005

In the past, when using MS SQL 2000, it was fairly easy to reduce the file size used by the transaction log. Just backup the transaction log, perform a shrink command and then the file size has dropped to a minimum.

In MS SQL 2005 this won't work. If you do so, then the file might be shrinked a bit, but it is not very small yet. To do this you would need to do the shrink command (including reorganizing the pages to the beginning of the file) before doing the backup and once again after the backup. If you do so, then the file has been really minimalized.

To do this automatically, you would set up a maintenance plan. In that plan it would be sufficient to do a shrink and then the backup.

The reason that you have to shrink the file first has to do with the active log pages and the order of it. For more information on why you need to shrink the file first, take a look at this page: http://support.microsoft.com/kb/907511

Reporting services on another machine

The database server doesn't have to be running on the same server as the CRM application itself. If you follow the installation guide, then everything should work fine. But... Avanade has done this for several clients and sometimes it appears that the reporting might not work. The error message will be:

“An error has occurred. For more information, contact your system administrator”
or
"error 401 – Unauthorized access"

If this is the case in your situation as well, then take a look at the following steps.

First you have to enable Kerberos Authentication for IIS on the CRM Server.
- Open a DOS box and browse to c:\inetpub\adminscripts
- Run the command: cscript adsutil.vbs set w3svc/1/NTAuthenticationProviders "Negotiate,NTLM" (If only one website is running you can use 1 in the commandline. Otherwise you have to check the logfile directory to see what number you should use)
- IISRESET

If your services run with the "network service" account you have to open ADSIEDIT and open the propeties of the CRM server. Go to SPN settings and add the next lines:
(Computername is your CRM server and FQDN is your Fully Qualified Domain Name)

HOST/computername
HOST/computername.FQDN
HTTP/computername
HTTP/computername.FQDN

You have to do a similar action on the SQL Server, the default instance uses port 1433
MSSQLSvc/computername:1433
MSSQLSvc/computername.FQDN:1433

Finally, on the client computer check if in Internet Explorer "Integrated Windows Authentication" is activated (under the advanced settings of the internet options)

Hope this helps you to get reporting to work as well!

Error: Deletion Service failed to clean up some tables

Some people have been experiencing the following error message in the event log:

Event Type: Error
Event Source: MSCRMDeletionService
Event Category: None
Event ID: 16387
Date: Date
Time: Time
AM User: N/A
Computer: Computer_Name
Description:
Error: Deletion Service failed to clean up some tables.

Microsoft has a solution for this message. See http://support.microsoft.com/kb/921391/EN-US/ for their solution.

Personally I don't feel comfortable with the solution they give. They are removing the enforced relationships for the tables which cause the issue. This will leave you with orphaned records in your database and probably the same issue when you upgrade to the upcoming titan release. Here are my steps to correctly fix this issue (partly copied from the MS solution) :

To identify the table where the Deletion Service is failing, follow these steps:
1. On the Microsoft Dynamics CRM server, click Start, click Run, type cmd, and then click OK.
2. At the command prompt, locate the system drive, locate the program files, locate Microsoft Dynamics CRM, locate the server, locate the bin, type crmdeletionservice.exe –runonce, and then press ENTER. You then receive a message that resembles the following message:
Can't clean up the following tables: Campaign

Note The table that is returned in the message is the table for which the Deletion Service failed.

The following example demonstrates how to resolve this problem for the Campaign table.

To resolve the problem for the Campaign table, follow these steps:
1. Run a statement in Microsoft SQL Query Analyzer. To do this, follow these steps:
a. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
b. Run the following query against the Organization Name_MSCRM database.
delete from Campaign where DeletionStateCode = 2
This query returns a message that resembles the following message:

DELETE statement conflicted with COLUMN REFERENCE constraint 'campaign_leads'.

2. Run two queries agains the Organization Name_MSCRM database.
delete from [table name in message] where [column name in message] in (select campaignid from Campaign where DeletionStateCode = 2)

delete from Campaign where DeletionStateCode = 2

By doing this, you do delete the associated records as well. Keep in mind that once deleted, you cannot get the data back. Therefore always make a backup first!

Modifying the fullname

The fullname of a contact is always "firstname lastname". Right? Well, did you know that you can modify this? Go to Settings, System Settings. There you can modify this setting to some other options!

Great :) So lets modify the fullname to "lastname, firstname". When saving this setting, CRM warns you that this only works for the new entities and not for the existing entities. If this really is a problem, then you can go into SQL and update the fullname manually. Here's the code to update the contacttable:

UPDATE contactbase SET fullname = ISNULL(lastname, '') + ', ' + ISNULL(firstname, '')

Of course you can update the contactbase with other values for the fullname as well.

Keep in mind that you NEVER update the Contact view, always the contactbase. Updating the contact view will cause problems.

Anyway, you should always make a backup of your database before making any modification to the database.

Full-Text Search is not enabled for the current database

After using the redeployment tools it is sometimes not possible to search for KB articles in CRM. The error which shows up (either on the screen or in the event viewer) is:

Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database.

Here is the resolution:

Ensure that the Microsoft search service is running and set to start automatically. This can be verified under the Control Panel > Administrative Tools > Services.
Enable (or re-enable) full text search on the databases. Open Query Analyzer and connect to the production databases. Run the following query, where is the database that ends with “_MSCRM”:

use <database>
go
exec sp_fulltext_database 'enable'
go

Repopulate the catalogs. This is done by going into the _MSCRM database, looking at the Full Text Catalogs under the database, and right clicking on the ix_* file and clicking on “Start Full Population”. Populating the index might take some time. However, there should be no impact on the users during this time.