Showing posts with label export. Show all posts
Showing posts with label export. Show all posts


Interesting issues with the CRMDateTime continuum.

This is a guest post by a friend and colleague of mine: Leon Krancher. He has been digging into an issue regarding CRMDateTime and integration. Make sure you read this before you start your own integration!

Well I guess I’d better start off with a little introduction. My name is Leon Krancher and I’m a colleague of Ronald at Avanade. As we both work with CRM we occasionally find time to get together, have a beer and share experiences. Recently I told him about a problem I encountered while trying to create a service that would synchronize my customers Data warehouse with CRM. Ronald thought it was interesting and asked me to write about it for his blog. So here we go.

Let’s start out with a more detailed description of what had to be created.

The customer’s data warehouse contained information about its customers. This information is updated frequently by the customer’s backoffice systems as well as a number connections to third party systems. Whenever the data changed a database trigger would update a datatime field inside the database to indicate the exact moment the customers data was changed.
Because the data warehouse data was also used in CRM I was tasked with creating an application that would update the data stored in CRM whenever the data warehouse was updated. Unfortunately I only had a very limited timeslot in which this application would have to run so I had to find a way to update only those few records that had changed. To solve this problem I decided to do the following:

Ø First I would add an attribute to the entity in CRM that would be used to store the DateTime value used to store the date and time on which the data was originally created in the data warehouse.

Ø Second I wrote a cross-database join that would join the data warehouse on the CRM database, compare both datetime values and return those rows for which the entity in CRM was outdated.

Ø The last step was to use the CRM webservice to update the CRM entities returned by the previous query[1].

Unfortunately whenever I tried to run my tool it would always update all records even though the data warehouse had not changed.
After some frantic debugging[2] I finally figured out what the true problem was.

When storing my data in CRM I used the following code taken from the CRM SDK.


providedContact.new_nawsyncdate.Value = syncDateAccordingToDatabase.ToUniversalTime().ToString("u");

This converts the syncDateAccordingToDatabase DateTime object to a string in the universal time format so it can be stored in CRM. Unfortunately the universal time format omits any milliseconds.

So whenever SQL Server would compare the dateTime stored in the data warehouse to the dateTime stored in CRM the data warehouse DateTime that did contain any milliseconds would be seen as ‘larger’, hence the continuous cycle of updates.

After figuring this out the problem was easily solved by adjusting the database query so it would always remove any milliseconds before making the comparison.

I hope this provides you guys with both a funny story and some help if you ever run into a similar problem.

-Leon

[1] I couldn’t update the CRM database directly because that would prevent the execution of a callout

[2] In my defense, the matters were complicated because the data warehouse and the CRM databases were set to use different time zones making the problem appear like a conversion problem



Maximum amount of records in Excel export

I'm very busy at the moment (just like you all probably), so just a small post. The default for the Excel export is a maximum of 10.000 records. If you need to export more, then you'd need to modify a registery setting. Follow the next steps to achieve this:

- Start Regedit
- Navigate to HKLMachine\Software\Microsoft\MSCRM
- Create a new DWORD value named 'maxrecordsforexporttoexcel'
- Give the new value any value (minimum is 500)

Ronald



Quick create export functionality

Serveral of my customers are asking for an export functionality to Excel or CSV. Have you ever experienced that? If so, how did you create that?

Let me tell you my solution. MS CRM 3.0 does offer SQL Reporting Services for Reporting. The reports which are generated can be exported to Excel and CSV. With a little bit of tuning, you can even render a report into one of these formats, without requiring the user to press the export button. In this post I'll show you how this can be done.

Let's say you're using the Adventure Works Cycle demo. Then create a report in SRS (there's enough info on the web on how to do this) and upload this report to CRM. You can upload such a report to crm by going to the Workplace area, select Reports and click "New". Now you can access this report via CRM, but also directly. To open this report directly, go to this url (where you enter your own servername, report folder directory and report name):
http://[your server]/ReportServer/?/[your report folder]/[your report]&rs:Command=Render

Now with sending parameters, you can export this to CSV or Excel. Modify the attributes of the report to contain:
&rs:Command=Render&rs:Format=CSV
&rs:Command=Render&rs:Format=Excel

The problem with the CSV, is that default the separator is a comma. You might want to change this to semicolon (;) or tab. To change this to tab, use the format hereunder:
&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09

Since we know now how to export data, we can also make this link available for users. Unfortunately the isv.config does not allow us to enter an url as describe above, but we can make a simple aspx, or even html, page which does redirect the user to that url. Just add the redirection page to the webserver and add a link in the isv.config. You have now made a very easy to modify export without coding!

Update:
Placing a code like that in the ISV.config.xml is not allowed due to the : in the query string. Instead you will need to direct your code to a intermediate page which will redirect you to the report. You can do this by using a simple html page with a meta refresh:

<html>
<head>
<meta http-equiv="refresh" content="0;URL=http://localhost/ReportServer/?/Adventure+Works+Cycle+Demo_MSCRM/Export&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09" />
</head>
</html>