SSRS Date Expressions

I've been doing a few CRM reports that used different date ranges as parameters. I was able to put the start and end dates of these ranges in SSRS parameters using the following expressions:

Start of Current Month:

=DateSerial(Year(Today), Month(Today), 1)

End of Current Month:

=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today), Month(Today) + 1, 1))

Start Of Previous Month:

=DateSerial(Year(Today), Month(Today) - 1, 1)

End Of Previous Month:

=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today), Month(Today), 1))

Start of Current Quarter:

=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today), DateSerial(1900,1,1))

End of Current Quarter:

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1) + 1, Today), DateSerial(1900,1,1)))

Start of Previous Quarter:

=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today) - 1, DateSerial(1900,1,1))

End of Previous Quarter:

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today), DateSerial(1900,1,1)))

Start of Current Year:

=DateSerial(Year(Today), 1, 1)

End of Current Year:

=DateSerial(Year(Today), 12, 1)

Start of Previous Year:

=DateSerial(Year(Today) - 1, 1, 1)

End of Previous Year:

=DateSerial(Year(Today) - 1, 12, 1)

Importing Attachments to CRM

I'd been following instructions from the Microsoft Dynamics CRM Blog about migrating notes and attachments but was running into an error during the actual import. The error I was getting was "The attachment file was not found" and it was happening for every attachment listed in my .csv file.

The problem in my case was that the files, and the .csv file, were on a network location. Even though I am running the DMM from a network drive (my environment has CRM application on one server and CRM database on another, and I just remove into them as needed, but have all files for the CRM project on a network location somewhere), it would not find files that were in the same network location.

Once I moved the .csv files and all the files it referenced (and I just used directory option A in the blog post linked above) it was able to import attachments just fine.

Removing Old Migrations from Data Migration Manager

You may get to the point where you have performed a number of migrations with the Data Migration Manager (DMM) - test and/or production.

You may have experienced that a bulk deletion or import gets longer with each migration as your total data in the MSCRM_MIGRATION database (which is either going to be in the SQL Database you specified during the install of DMM or on a local SQL Express instance) increases.

You may also have experienced a small inconvenience where all your migrations have to have a unique name.

All that is needed to get around this small problem is flushing ouu the ImportBase table in the MSCRM_MIGRATION database.

USE MSCRM_MIGRATION;

DELETE FROM ImportBase;

File Too Large for CRM Data Map

To produce a demo of a CRM system, I was trying to use some data from an Access system and suck it up into CRM using the Import Data Wizard. I'd created my queries and tables in Access and exported the data out into a .csv file using the Export Wizard in Access.

With this .csv file (it originally had 2,100 or so records) I tried to create a Data Map for my import. I selected my .csv file to use as the Sample Data for my map. Naturally CRM complained that this file was too large to be used as the sample. I deleted some of the records from my file, leaving about 50 rows, and then tried again. I was still getting the error though. My file size was only around 200kb, well below the 4mb limit that CRM has for file uploads by default (this can be changed in the web.config for CRM). I didn't have very many columns, maybe 50 at the most.

I should point out I was using Excel 2007 to edit my .csv files, and Access 2007 generated the file.

I thought that 200kb was large for what was in the file; just 50 rows of data like I mentioned.

What ultimately fixed it was this (although I don't know why).

1) I opened my original .csv file in Excel.

2) Deleted the rows I didn't want.

3) Clicked Save (I got a prompt about losing formatting if I saved it to a .csv file, and I clicked yes I wanted to continue).

4) Closed the document

5) when I got a prompt asking if I wanted to change the file, I clicked yes.

In earlier attempts I had been clicking no, because I'd already manually performed the save process after I removed my rows. I didn't think I needed to do it again.

After clicking yes to perform this second save on exit, my file size shrank considerably and CRM had no issue with the sample 50 rows of data. I am not sure if there were some artifacts in the file from Access creating it or if Excel was changing the file in the second save in a way that a manual save was not. All I know is that this worked. I can't say that it'll fix every problem relating to .csv files being too large for an import, but there's no harm in trying it.

The Given Key Was Not Present In The Dictionary

There've been a few times I've come across this error with CRM when writing small plug-ins, more as a test exercise than anything to use in production - yet.

It's always the same scenario too. You write your plugin, maybe a Create message or an Update message, you check the code and you register the DLL and a step. You come to test it, and you get a pop-up with the not so helpful text of "The given key was not present in the dictionary".

At first I found the message very confusing, but I eventually found an explantion for why it was happen (or at least had a vague grasp of the problem that was causing it).

When ever you reference an attribute of a Dynamic Entity, you are using a key/value pair. The key is the name of the attribute, the value is obviously the value of that attribute and a container which holds key/value pairs is called a dictionary.

The error is down to referencing an attribute name that is not available in the dictionary you are looking at. There may be a number of reasons for this, but I think the most common two are: 1) The key (attribute name) has been mistyped in the code; or 2) It's not available in the current message.

I've had this happen in Update Message plugins, because in an Update Message the Dynamic Entity containd in the Target property of the context's InputParameters only contains the attributes on the entity which have changed. If the user did not change the name of the account before they pressed save then "accountname" is not going to be present in an Update Message.

I hope that makes sense, but there's a chance it wont, because like I said I'm still trying to grasp the finer points of it, especially when it comes down to using the InputParameters and the Target property. They are something I've seen in many plug-in tutorials and copied down as gospel, but not really taken the time to sit down and find out what it's for.

Hopefully this post will get you past an error and make you sit down and find out what's actually going on with your context properties too.

Creating a SharePoint Document Library in CRM 4.0

After reading a few posts on SharePoint and CRM 4.0 integration, and learning of a new business requirement that someone came up with for the CRM deployment I am working on, I managed to get a plug-in created that will create a document library in SharePoint when a new custom entity is created in CRM 4.0.

I basically performed these steps:

1) Added a new attribute to my custom entity, in this case it was called new_projectspdocliburl of type nvarchar with a length of 200.

2) Added a new tab to the custom entity form, and added a new iframe called IFRAME_SharePoint, unchecking Restrict cross-frame scripting.

3) Added the new field to the tab (the field has to be on the tab for some JavaScript used later).

4) Saved and published the changes to the entity.

5) Create a new VB.Net Class Library project in Visual Studio 2008, calling it ProjectCreateSPDocLibPlugin, and renaming Class1.vb to plugin.vb

6) Added a web reference to SharePoint's Lists.asmx for the site you want the document library to be created in (http://servername[:port]/site_name/_vti_bin/Lists.asmx) and using a reference name of SharePoint.

7) Added a reference to System.Web.Services (found in the .Net tab of the add reference dialog) and a reference to micrsoft.crm.sdk.dll and microsoft.crm.sdktypeproxy.dll (which can be found in the sdk/bin folder of the CRM 4.0 SDK)

8) Used the following code in plugin.vb:

Imports Microsoft.Crm.Sdk
Imports Microsoft.Crm.SdkTypeProxy
Imports ProjectCreateSPDocLibPlugin.SharePoint 'This is the namespace of this project + the Reference name of the SharePoint web reference

Public Class plugin
 Implements IPlugin

 Public Sub Execute(ByVal context As Microsoft.Crm.Sdk.IPluginExecutionContext) Implements Microsoft.Crm.Sdk.IPlugin.Execute
     'Get the name of the target entity and its GUID
     Dim dynamicEntity As DynamicEntity = CType(context.InputParameters.Properties("target"), DynamicEntity)
     Dim entityName As String = dynamicEntity("new_projectname").ToString 'replace new_projectname with the name of your custom entity's primary attribute
     Dim entityId As String = context.OutputParameters.Properties("id").ToString

     'SharePoint Document Library is type 101
     Dim spDocLibListType As Integer = 101

     'Use the SharePoint's Lists.asmx to create a new list object
     Dim listService As New Lists()
     listService.Credentials = New System.Net.NetworkCredential("username", "password", "domain") 'Replace these with your own
     listService.Url = "http://servername[:port]/site_name/_vti_bin/Lists.asmx" 'Replace this with your own
     Dim listResult As System.Xml.XmlNode = listService.AddList(entityName, entityName + " Documents", spDocLibListType)
     Dim returnXML As String = listResult.InnerXml.ToString()

     'Update the Custom Entity to store the URL of the new document library
     Dim customEntity As New DynamicEntity()
     customEntity.Name = "new_customentityname" 'Replace this with your own entity name
     customEntity("new_customentityid") = New Key(New Guid(entityId)) 'Replace this with your entity's ID attribute name
     customEntity("new_projectspdocliburl") = "http://servername[:port]/site_name/" + entityName + "/Forms/AllItems.aspx"

     'Use the IPlugin interface to create a reference to the CrmService
     Dim crmService As ICrmService = context.CreateCrmService(True)
     crmService.Update(customEntity)
 End Sub
End Class

9) Compiled the project and used the Plug-in Registration tool to register the new plugin and then registered a new step with a Message of Create, the Primary Entity being the custom entity and making sure it's a Post Event step (it will be triggered after the core processing of the entity creation in the event pipeline).

10) Added the following JavaScript code to the OnLoad method of the custom entity's form:

var CRM_FORM_TYPE_CREATE = 2;
if (crmForm.FormType == CRM_FORM_TYPE_CREATE)
{
var spDocLibUrl = crmForm.all.new_spdocliburl.DataValue;
crmForm.all.IFRAME_SharePoint.url = spDocLibUrl;
}

11) Save the changes and once again publish the custom entity.

Now, all things going to plan, when you create a new object from your custom entity, once it is created, your plug-in will kick in and create a new document library in SharePoint using the name of the created object, and update the IFRAME on the form so the users can see the document library.

Obviously, the users need to already have permission to see the library in SharePoint. Another thing to consider is that the view we used, AllItems.aspx, is potentially going to show a full SharePoint page in the IFrame. It might look a bit off to have all the standard SP navigation inside the CRM frame. I don't know enough about SharePoint to explain how to work around that.

But this should have given you a good enough start into integrating SharePoint and CRM to give better document handling than the standard attachments to a CRM object.