Archive

Archive for November, 2008

Inline Grids for Sales Orders in Microsoft Dynamics CRM

November 27, 2008 Leave a comment

In every CRM application I have ever played with there have been areas I have never been proud to show off to customers or prospects. And adding products to CRM’s Sales Orders has been one of those areas for me in CRM since the products inception.

While it works well in high dollar low transaction environments, it doesn’t work so well in high transaction, low dollar transactions. One of my most recent demos was to a company that did over 50,000 sales orders a day and so working with Daren from Microsoft Consulting Services, he build a REALLY cool too to help them VERY quickly insert items into Microsoft CRM without the normal number of clicks.

Read more at my blog BusinessNone.

Happy holidays,

Ben Vollmer

Advertisements

Microsoft Dynamics CRM 4.0 Form Reporter

November 27, 2008 Leave a comment

MSDNcodeGallery

I recently posted the Microsoft CRM 4.0 Form Reporter in MSDN Code Gallery. Form Reporter is an application that creates a Word 2007 document detailing the configuration of all customizable Microsoft CRM 4.0 forms, including tabs, sections and information about each field. This document can be used for planning form customizations or documenting the current state of form customizations. This solution requires Word 2007 and .NET Framework 3.5.

Microsoft CRM 4.0 Form Reporter uses the exported customizations.xml file. You must have a copy of this file to use the Form Reporter. For best results, the exported file should include all entities because relationships defined between any missing entities will not be included.

Why Form Reporter?

As a member of the team that prepares documentation – we occasionally get requests for a way to generate documentation about a CRM implementation. I know that this can be a big time-saver for implementers who have a deliverable to document their solution or for trainers seeking to provide materials that relate to a specific implementation.

I had noticed that Merijn van Mourik had posted a Microsoft CRM Documentation Generator for CRM 3.0 on CodePlex in January of 2007. That solution uses an add-in for Office Excel 2007 to generate a table. While I have pointed others to that solution for CRM 3.0 – I had never downloaded or installed it myself. Like the Form Reporter – it uses an exported customizations.xml file. But it doesn’t support Microsoft CRM 4.0.

How it was made

I was documenting the Form XML Reference for the SDK and I realized that most of the data needed for documenting forms was in the exported customizations.xml file. Since I do a lot of work with XSL for our content development infrastructure – I started making a transform to present the data in an ASPX page. This was something I worked on during rainy weekends.

But a web page that generates tables with information about forms would not be that useful for most of the types of documentation work implementer and trainers need to do. Probably the most useful output would be Word so that people can easily copy and paste portions that they need. At the same time the Open XML Format SDK 2.0 was released in September. This SDK makes it relatively easy to create Word 2007 documents based on XML data.

Finally I decided to create a simple Windows Forms application that would transform the customizations.xml file into a Word 2007 Document. I posted both an installation MSI and the code so non-programmers can use it right away. The code is pretty basic, but it provides an example of using the Open XML Format SDK to create a Word Document. A developer who knows XSL should be able to modify the ToWordTransform.xsl to get different results based on the data available in the customizations.xml. Developers can also modify the Styles and Theme files to get a different look and feel.

I hope this little application makes some of your documentation tasks easier.

Jim Daly

Rolling out CRM: Who should take ownership?

November 25, 2008 Leave a comment

Rolling out CRM: Who should take ownership?

umar Today we welcome Microsoft Dynamics CRM MVP Umar Khan who is a Business System Consultant for SISCOM in Saudi Arabia. You can read more from Umar at http://umarkhan.wordpress.com. Umar also spends time in the Dynamics Forums answering your questions.

It is always a tricky responsibility, who should take the ownership of not only the successful implementation of Microsoft Dynamics CRM but also making sure that its internal users are using CRM in a way which can ultimately help in achieving the expected goals of CRM strategy. Just installing and configuring a fine CRM software is not enough. Providing CRM training is also not the last step. It is most important that employees of the company are ready to use CRM even if they are reluctant. People are often reluctant to accept change. But by providing concrete examples of how this product can be used to improve an employee’s performance, the best of your staff will quickly gravitate towards using the new solution set.

So who is the right person to take the responsibility for making sure that CRM is being used as planned? Many times, the first answer you will hear will be that it is Microsoft Dynamics CRM Consultant’s job. Wrong! At least that is what I think; this should be someone within the organization, someone with the following capabilities;

  • Good skills of influencing even without power or someone with authority.
  • Someone at managerial position.
  • Someone who himself is convinced that CRM is The Solution.
  • He needs not know all the features and usage of CRM but must know from where he can get the answers.

This person needs do take practical steps like making sure that sales persons are updating the manager on the opportunities using CRM and not their previous unleveraged methods of direct emails, phone calls or verbal communications. The number of activities and notes being created in CRM is a good way to determine successful usage of CRM by the staff.

Remember to manage the expectations of the consumers of CRM. Be ready to distinguish between the two types of CRM application users. They have different requirements and objectives from CRM:

  • Ones who are most of the time going to enter data in CRM.
  • And the ones, who are going to make decision, monitor and manage by reviewing the data stream in CRM using the various visualization tools.

Don’t only use CRM as a central data repository; use CRM to help define and streamline the processes of the business related to the customers. In this manner, both your company and your customers will benefit.

A consultant should set the expectations of client and try to sell the solution instead of only the software. For example:

If you want an integrated solution, using tools and systems that are ubiquitous in the workplace, then Microsoft Dynamics CRM is the obvious solution. And don’t under-estimate the customization power of CRM 4. If you want further capabilities based on the unique needs of your company, there are numerous ways to tap into the extensibility model built in to Microsoft Dynamics CRM. Its rich built-in customization tools, flexible Software development Kit (SDK), powerful workflows, integrated reporting, seamless integration with other Office applications can turn this CRM application into a specialized vertical solution for your company.

Umar Khan

Webinar Part 6: Dynamics Mobile as a platform

November 24, 2008 Leave a comment

A quick heads up from our man in the field John O’Donnell:

Date: 11/24/2008 09:00AM PST

Trainer: Bert Berkholder / Henrik K Nielsen

Course Description

Concluding this series of webinars, join Bert Burkholder from Microsoft in a session focused on the story of Microsoft Mobile and in particular the Microsoft Dynamics Mobile product. Bert will cover the capabilities of Microsoft Dynamics Mobile and tell the story of the design decisions behind the product and its target audience and development capabilities. If your customers are demanding that you deliver mobile capabilities this is a session not to be missed!

URL: http://www.isvinnovation.com/Directory/Description.aspx?EventId=627

Cheers,

John O’Donnell

The Value of Dynamics CRM Platform for ISVs

November 24, 2008 Leave a comment

Microsoft Business Solutions offers a compelling story and platform, particularly for the ISV community.  This platform provides key business and architectural benefits for ISVs to leverage for a wide range of highly transactional line-of-business applications. These applications help you create an integrated solution for almost any aspect of a business.

In this Channel 9 video, we discuss Microsoft Dynamics CRM, the platform that it brings to our ISV community, and the opportunities that Partners have in building out a solution using Microsoft Dynamics CRM.  Some of the items discussed are: the CRM Technology Stack, the CRM Platform, Customization Models, CRM and Office, and many other exciting topics.

The video is available here:

http://channel9.msdn.com/posts/sureshs/Microsoft-Dynamics-CRM/

Take a look and let us know what you think,

Sumit Virmani

ASP.NET 2.0.50727.0 Warning Event ID: 1309 due to Request time out

November 21, 2008 6 comments

Have you ever had users complain about the CRM web application timing out on large operations? If so, you may want to check the ASP.Net timeout setting.

The reason for the warning is that the execution time required by CRM connecting with your remote SQL Server is exceeding the currently set ASP.NET request execution timeout value. By default, this value is initially set to 300 seconds. This may occur for example, when working with Large Marketing Lists of 150,000+ members.

The event log entry may look something like the following…

Event code: 3001

Event message: The request has been aborted.

Event time: 11/19/2008 3:37:51 PM

Event time (UTC): 11/19/2008 11:37:51 PM

Event ID: 13998b6749624519856e0b4f83cf6f8b

Event sequence: 302758

Event occurrence: 1

Event detail code: 0

Application information:

Application domain: /LM/W3SVC/1/ROOT-1-128716032401801167

Trust level: Full

Application Virtual Path: /

Application Path: c:\inetpub\wwwroot\

Machine name: ContosoCRMweb

Process information:

Process ID: 3656

Process name: w3wp.exe

Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:

Exception type: HttpException

Exception message: Request timed out.

How to Modify the ASP.NET request execution timeout setting for Windows Server 2003

1. On the computer where Microsoft Dynamics CRM Server application is installed, start Internet Information Services (IIS) Manager.


clip_image001

2. Expand Web Sites, right click the Microsoft Dynamics CRM Web site, and then click Properties.

clip_image003

3. On the Microsoft Dynamics CRM Web site properties page, click ASP.NET, and then click Edit Configuration.

clip_image005

4. Click the Application tab, and then in the Request execution timeout box increase the number of seconds.

5. Click OK two times and close IIS Manager.

Cheers,

Jim Schumacher

Auditing Report Execution using the ReportServer Database

November 20, 2008 Leave a comment

We welcome our guest blogger David Jennaway who is the technical director at Excitation and a CRM MVP.

Reporting Services is usually considered at most as just the engine for executing and rendering reports. However, it also has its own SQL database that contains information that can be useful. In this article I’ll look at how you can use information derived from the ExecutionLog and Catalog tables to find information about who ran which report, and how long it took.

Accessing data in the ReportServer database

The recommended approach for querying the execution log is to periodically extract the data from the ReportServer database into a separate, denormalised database, then query this database. This approach is described in the SQL Server Books Online here (http://msdn.microsoft.com/en-us/library/ms155836(SQL.90).aspx ), and there are associated samples which include an SSIS package to perform the extract, and some sample reports on the extracted data (http://msdn.microsoft.com/en-us/library/ms161561(SQL.90).aspx ).

There are several advantages to this approach:

  • The denormalisation process in the SSIS package parses the parameters used in the report execution, which will be useful for us later
  • Using a separate database allows you to have more control over any additional SQL objects you create. As I’ll discuss later, it is useful to create a SQL function to get the friendly name of the report in CRM 4.0
  • Using a separate database allows you more flexibility when managing the SQL security. By default most users will not have the rights to query the ReportServer database directly
  • If the ReportServer and CRM database(s) are on a separate server, you can place the denormalised database on the same SQL Server as the CRM database(s). This will simplify the process of getting the report name from the CRM database

However, it is also possible to query the ReportServer database directly. This will give you data that is always up to date, but you will have to ensure users have sufficient permission on the tables, and you will have to parse the report parameters yourself.

The rest of this article is based on the denormalised database created from the SQL Server Samples.

Database Structure

The scripts described above create several tables; those that are relevant to this article are:

  • Reports. This contains a record for each report in Reporting Services
  • Users. An entry for each user on Reporting Services
  • ExecutionLogs. This table has the core data we’re using here. It has a record for each execution of a report, including statistics on the time taken, and the parameters used
  • ExecutionParameters. One record for each parameter used for each report execution

The following query illustrates a simple SQL query on these tables:

select r.Name as ReportName, u.UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime

from executionlogs l
join reports r on l.reportkey = r.reportkey
join users u on l.userkey = u.userkey

This produces output like the following:

ReportName

UserName

TimeStart

TotalTime

My Report

MyDomain\My User

2008-10-06 12:51:22.123

1023

{7121cc90-d2c0-dc11-8308-0003ff562152}

NT AUTHORITY\NETWORK SERVICE

2008-10-06 12:51:51.817

551

 

 

 

The first record is from a separate report on a database that has nothing to do with Dynamics CRM, while the second record is from executing the Activity report on a Dynamics CRM 4.0 Server, in a deployment where the Dynamics CRM Data Connector for Reporting Services has been installed.

From this you can probably see that, while the above query gives immediately helpful information when running non-CRM reports, it is not so useful for the CRM report. There are 2 things we have to do; get the actual user name or the user running the report, and get a usable report name.

Getting the UserName from the ExecutionParameters

The UserName information from the above query would normally identify the user that ran the report. However, if you use the Dynamics CRM Data Connector for Reporting Services, then this field will not give you this information.

However, it is possible to get the user information from the parameters passed to the report. Dynamics CRM passes several pieces of information to a report in the form of parameters, and the CRM_FullName parameter holds the name of the user executing the report.

So, we can change the above query to the following:

select r.Name as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = ‘CRM_FullName’) as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey

This now gives us:

ReportName

UserName

TimeStart

TotalTime

{7121cc90-d2c0-dc11-8308-0003ff562152}

CRM Admin

2008-10-06 12:51:51.817

551

 

 

 

Rather than using a join to get the ExecutionParameter, I used a subquery. This is mostly a matter of preference, but it makes it easier to include several parameter values on the select list.

Getting the Report Name from the MSCRM Database

Now we need the report name. If we were using CRM 3.0, this would show us the name of the report as we see it in the Dynamics CRM user interface, but things work differently in CRM 4.0. In CRM 4.0, the reports are created with a Guid for a name, and the usable name is stored in the organisation’s MSCRM database.

To get the report name in our query we will need data from another database. This can be done in a join, but I prefer to use a SQL function to do the work:

create function fExcitationGetReportName(@RSName nvarchar(425))
returns nvarchar(425)
as
begin
declare @ret nvarchar(425)
select @ret = r.name from AdventureWorksCycle_MSCRM..FilteredReport r
where @RSName = ‘{‘ + cast(r.reportid as nvarchar(100)) + ‘}’
return @ret
end

To avoid supportability concerns, I create this function in my RSExecutionLog database, and include the specific organisation database name in the function definition. You’ll need to replace AdventureWorksCycle_MSCRM with your CRM database name. If you have multiple organisations you could either create a function per organisation, or pass the organisation name as a parameter into the function, and use dynamically generated SQL.

Another point to make about the function is that we need to cast the reportid in CRM from a Guid to a string, and add the curly braces to match the name that is stored in Reporting Services

Once we’ve create the function, we can use it as follows:

select dbo.fExcitationGetReportName(r.Name) as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = ‘CRM_FullName’) as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey

Which gives the output we want:

ReportName

UserName

TimeStart

TotalTime

Activities

CRM Admin

2008-10-06 12:51:51.817

551

 

 

 

Further Thoughts

So far I’ve concentrated on the SQL aspects of getting the data you want from the underlying tables. Once you have this, you can present this information in your own reports. The samples that create the RSExecutionLog database include some sample reports that you can use, and the SQL within these reports can be easily modified using the techniques described above to get the report and user names.

I don’t have the space in this article to go into detail about extra things you can do with the data from ReportServer, but here are some additional ideas which may make it into a subsequent article:

  • Some other ExecutionParameters may be useful. CRM_FilterText gives a text representation of the pre-filter used on a report
  • If you have multiple CRM organisations, I find the easiest way to identify the organisation is to use the first part of the Path column in the Reports table. This gives the database name, which is derived from the organisation’s unique name

Links

Report Execution Log at SQL Books Online – http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx

Code used in this article, and sample reports on the MSDN Code Gallery – http://code.msdn.microsoft.com/RSExecutionLogCRM40.

Cheers,

David Jennaway