Views:

Improving Report Performance

The best way to improve report performance is by limiting the data that is used in each report. You can do this by adding parameters that users must specify when they run the report, such as date ranges or owner, or by adding additional selection criteria to a report. This section describes how to add an Owner parameter that enables users to select either only records they own, or all records.

If there is a noticeable delay (5 seconds or more) in displaying data in a Microsoft Dynamics CRM list, or when opening a Microsoft Dynamics CRM form on a local area network, this delay may be caused by configuration problems in the SQL Reporting Server (SRS). Typically, this only occurs when Microsoft Dynamics CRM is installed on a separate server from the SRS server.

Reasons for this delay can include the following:

·         Incorrect SRS locations in the SQLRSServerURL registry key in HKEY_LOCAL_MACHINE_SOFTWARE | Microsoft | MSCRM

·         DNS problems

·         Missing or invalid Service Principal Names (SPNs) for the Host name or Microsoft SQL Server (both the SQL Server that contains the Microsoft Dynamics CRM databases and the SRS Microsoft SQL Server)

·         Incorrect permissions on the computer that is running Microsoft SQL Server for SRS

Note:        Incorrect permissions can cause problems accessing Microsoft Dynamics CRM reports. This can prevent Microsoft Dynamics CRM from displaying the list of valid reports in lists and forms.

Troubleshooting SRS Problems

Verify that the issue is caused by SRS

a)    Set the registry key HKEY_LOCAL_MACHINE | Software | Microsoft | MSCRM| SQLRSServerURL to http://0.0.0.0. This will cause Microsoft CRM to fail immediately when it tries to retrieve reports.

Note:  You must back up or note the value in this registry key before changing it. Changing this value to http://0.0.0.0 breaks all Microsoft Dynamics CRM reporting functionality.

b)    Try to reproduce the slow performance by taking actions such as opening the Microsoft Dynamics CRM Web application and navigating to a list, such as the Accounts list, or opening a Microsoft Dynamics CRM form, such as an Account record. If the list page or record form opens in less than a second or two, this indicates that the problem is with the SQL SRS server or with the network connection from the server that is running Microsoft Dynamics CRM to the Microsoft SQL SRS server.

Resolving SRS Problems

If you verify that the SQL SRS server has a problem, the following items should be checked and verified:

1.    Verify that the SQLRSServerURL location can be accessed from Microsoft Internet Explorer by a Microsoft Dynamics CRM Administrator from the Microsoft Dynamics CRM Server. Type the address into a new Internet Explorer address bar. If the Microsoft SQL Reporting Server is working correctly, you should see a Web page that has a directory that is the same name as the Microsoft Dynamics CRM organization name, such as "Adventure_Works_Cycle_MSCRM". Clicking the link should display all the reports available for Microsoft Dynamics CRM. If the account you are using to access this Web page has the privileges necessary to view Microsoft Dynamics CRM reports, clicking a report name, such as User Summary, should display the corresponding report.

2.    If you cannot access the SQLRSServerURL location in Internet Explorer on the Microsoft Dynamics CRM server, the following items should be verified:

·         Verify that the SQL Reporting Services service is started on the Microsoft SQL Server specified in the SQLRSServerURL registry key. You can check the service by running Services.Msc on the SQL Server and verifying that the service named "SQL Server Reporting Services (MSSQLSERVER)" is started and set to start automatically.

·         Verify that the SQL SRS Server can be accessed over the network. Open a command prompt and type NSLOOKUP SRS_SERVERNAME where SRS_SERVERNAME is the name of the SRS Server specified in the SQLRSServerURL location. No errors should be returned and you should see the correct DNS name and IP address. If either of these is incorrect, you can use network troubleshooting tools such as NetDiag and DCDiag to analyze and find network and DNS configuration problems. You can download DCDiag from:
http://www.microsoft.com/downloads/details.aspx?familyid=23870A87-8422-408C-9375-2D9AAF939FA3&displaylang=en.

You can download NetDiag from:
http://www.microsoft.com/downloads/details.aspx?familyid=1EA70814-7E6C-46E5-8C8C-3C439A732E9F&displaylang=en.

You can also use the MPSRPT_Network.exe Microsoft Customer Support reporting tool to analyze the network configuration on the Microsoft Dynamics CRM server and on the Microsoft SQL Reporting Services server. You can download this tool from:
http://www.microsoft.com/downloads/details.aspx?familyid=cebf3c7c-7ca5-408f-88b7-f9c79b7306c0&displaylang=en.

·         If you have installed Reporting Services on a different server, verify that the additional setup tasks have been performed. For more information about these additional tasks, see "Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services is Installed on Different Server":
http://www.microsoft.com/downloads/details.aspx?FamilyID=51bf9f20-bd00-4759-8378-b38eefda7b99&DisplayLang=en.

For additional troubleshooting information about Trust for Delegation and Kerberos Double-Hop authentication problems, see the article “Troubleshooting Kerberos Delegation”:
http://go.microsoft.com/fwlink/?LinkId=57546.

·         If you receive the error "An error has occurred. For more information, contact your system administrator" or "The request failed with HTTP status 401: Unauthorized" when you try to access reports in Microsoft Dynamics CRM 3.0, see the Knowledge Base article “Error message when you try to access Reports in Microsoft CRM 3.0: "Server Error in '/' Application"”:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;916168.

·         If you receive the error “Reports.config has invalid schema, and could not be loaded.” when you access a report, see the Knowledge Base article "You receive an error message when you access Calendar or Reports in Microsoft CRM":
http://support.microsoft.com/default.aspx?scid=kb;[LN];916163.

You may see this error if you have installed Microsoft .Net Framework 2.0 on the Microsoft Dynamics CRM Server or on SQL Server 2000 with Reporting Services, and the ReportServer virtual directory and the Microsoft Dynamics CRM Web application are set to use ASP.Net version 2.0.50727 instead of 1.1.4322.

Note:  SQL Server 2005 Reporting Services can use the ASP.Net version 2.0.50727 without errors.

·         Slow performance opening Microsoft CRM lists or forms can also indicate invalid permissions for SRS reports. You can correct this by republishing the Microsoft Dynamics CRM SRS reports. Microsoft Dynamics CRM Reports can be republished by a user who has Microsoft CRM Administrative rights. To do this, follow these steps:

a)    Open the Microsoft Dynamics CRM Web application.

b)    In the Navigation Pane, under Workplace, click Reports.

c)    Select the report you want to republish, and then on the More Actions menu click Edit Report.

d)    Click Browse in the report form, navigate to the "<Microsoft CRM Installation Dir>\Reports\MSCRM" directory, and then select the report file that you opened. In other words, if you were editing the Account Distribution Report, select the Account Distribution.rdl file.

e)    Click Save and Close to publish the report again.

f)    Verify that each Microsoft Dynamics CRM user is a member of the Reporting Group in Active Directory in the Microsoft Dynamics CRM organizational unit (OU). This enables a user to access Microsoft Dynamics CRM reports.

Report Scheduling Wizard

Ad hoc reports performed during peak hours can cause decreases in performance. The suggestions described in this section may also help you avoid performance issues caused by long-running reports, custom reports, or reporting on large datasets.

Warning:       Before You Perform any of the following optimization procedures, back up your databases and Active Directory. If you do not back up these items, you risk losing the information that is contained in them.

·         For long-running reports, use the Report Scheduling Wizard.

·         As soon as it is installed, the Report Scheduling Wizard is available from the Reports area of Microsoft Dynamics CRM. It can be used by any user who has the Manage Reports privilege to schedule any Reporting Services report to run on a daily, weekly, or monthly schedule, and to create a snapshot of a report that includes data from a specific time point. You can do this to schedule the reports to run during non-business hours to offload processing to a time when most Microsoft Dynamics CRM users are not using the system.

Snapshots created by using the wizard can be made available to the user running the wizard, or can be shared with other users. For more information, see the documentation for the Report Scheduling Wizard, available as part of the following download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=a101d4d9-6463-4a45-899b-3c3ee979c4d0&DisplayLang=en

·         When you create custom SRS reports, review the “Performance Issues” section in the Report Writers Guide section of the Microsoft CRM 3.0 SDK, available on Microsoft.com:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&DisplayLang=en

Dedicated Report Server

If your organization has a large data set, you may benefit by setting up a dedicated server for Microsoft Dynamics CRM reports. For more information, see the “Improving Performance of Microsoft Dynamics CRM 3.0 by Using a Dedicated Report Server” document, available to download on Microsoft.com:

·         http://www.microsoft.com/downloads/details.aspx?FamilyID=c82dfbe2-db8f-4a78-92b2-7c866057cde6&DisplayLang=en

If you decide to set up a dedicated report server, there are additional tasks you must perform. For more information about these tasks, see the “Additional Setup Tasks Required if Reporting Services Is Installed on Different Server” document, available to download on Microsoft.com:

·         http://www.microsoft.com/downloads/details.aspx?FamilyID=51bf9f20-bd00-4759-8378-b38eefda7b99&DisplayLang=en

Preparing to Add Parameters to Your Reports

For basic information about how to create reports for Microsoft Dynamics CRM, refer to the following article on Microsoft.com

·         http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx

Tip: Make the report pre-filterable

When you create a report, you can configure it to have a default filter that each user can edit before they run the report. This process is referred to as making the report pre-filterable. There are two advantages to making a report pre-filterable:

·         The default filter prevents users from unintentionally running the report on all records. By default, this filter selects active records that were modified in the last 30 days. If you have the Manage Reports privilege, you can define specific default criteria for the default filter for each report.

·         Users can edit the filter to find exactly the data that they need on the first try.

For Microsoft Dynamics CRM to make a report pre-filterable, you must specify the CRMAF_ prefix in your SQL query when you create your report in Report Designer. When you add this prefix to at least one filtered view in the query, Microsoft Dynamics CRM adds a default filter to the report. For each filtered view that has this prefix in the query, users can edit filter criteria. For example, if your query includes the FilteredAccount and FilteredContact views, and your SQL query uses CRMAF_FilteredAccount and FilteredContact, the report will have a default filter. Users will be able to edit criteria related to accounts, but will be unable to edit criteria related to contacts.

For more information about pre-filtering reports, see the “Using Filters in a Report” section of the Microsoft Dynamics CRM 3.0 Report Writer’s Guide, available in the MSDN Library:

·         http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0usingfiltersinareport.asp

Dynamic Excel or Filtered View queries

The article “2 ways to see combined account and order data” on Microsoft.com describes how to create a query to combine data from two related record types, display that data in Microsoft Office Excel, and make it available as a report in Microsoft Dynamics CRM:

·         http://www.microsoft.com/dynamics/crm/using/reporting/multentity.mspx

To limit the number of records a report returns if you are using a dynamic Excel worksheet or using a Filtered View query (this includes FilteredView queries in custom Microsoft Dynamics CRM SQL Reporting Services reports), consider making it more restrictive. If a field in the WHERE clause is used frequently, verify that a non-clustered index exists on that field.

SELECT   a.name,


 

         a.accountcategorycodename,


 

         a.industrycodename,


 

         a.customersizecodename,


 

         o.totalamount,


 

         o.submitdate


 

FROM     FilteredAccount AS a INNER JOIN


 

         FilteredSalesOrder AS o


 

ON       a.accountid = o.accountid


 

WHERE    (a.statecode = 0)


 

ORDER BY a.name


 

--more restrictive by adding condition to WHERE Clause with a.City = ‘Fargo’

SELECT   a.name,


 

         a.accountcategorycodename,


 

         a.industrycodename,


 

         a.customersizecodename,


 

         o.totalamount,


 

         o.submitdate


 

FROM     FilteredAccount AS a INNER JOIN


 

         FilteredSalesOrder AS o


 

ON       a.accountid = o.accountid


 

WHERE    (a.statecode = 0)

AND      (a.City = ‘Fargo’)


 

ORDER BY a.name

Find a remedy.