Knowledge Base

How to use Crystal Reports 9 to write parameter-driven reports for Microsoft CRM 1.2

Article ID: 834790

Article Last Modified on 12/25/2006

APPLIES TO

Microsoft CRM 1.2

SUMMARY

Three sample reports that describe how to use the Professional, Developer, or Advanced versions of Crystal Reports 9 to write parameter-driven reports for Microsoft Business Solutions CRM version 1.2 are available for download. To improve the performance of reports, parameters limit the amount of data that the report includes. Therefore, parameter-driven reports help provide reports that give you only the data that you need.

MORE INFORMATION

Download the sample parameter-driven reports

To download the sample reports, visit the following Microsoft Download Center Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyID=1d08d05e-a8e1-46ef-a808-ac388be24a3e&DisplayLang=en

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

Installation information

Follow the instructions on the download page.

Removal information

To remove the sample files, delete them.

Restart requirement

You do not have to restart your computer after you install the sample reports.

File information

The English version of these sample report files have the attributes that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date         Time   Size    File name 
-------------------------------------------------------------------------------------------
20-Jan-2004  09:54  65,024  Account Activity And Notes (specify status, owner, date).rpt
20-Jan-2004  09:57  64,000  Case List (specify owner, status, date).rpt
02-Jan-2004  10:19  103,936 Opportunity List By Account (specify owner, date, accounts).rpt

Add the sample reports to Microsoft CRM

To view a report in Microsoft CRM, you must save the report in the existing Microsoft CRM report folders.

Crystal Reports 9

To use Crystal Reports 9 to save the report in Microsoft CRM, follow these steps:

Install the three sample reports from the Microsoft Download Web site as discussed in the MoreInformation Section above.
Open each file in Crystal Reports 9:
1. On the File menu, click Save As.
2. Click Enterprise.
3. Type your account information, and then click OK.
4. Expand MSCRM1.2.
5. Expand the Admin Reports, Sales Reports, or Service reports node.
6. Click the subfolder where you want to put the report.
7. Specify the file name, and then click OK.

Microsoft CRM 1.2 Report Manager

For additional information how to download Microsoft CRM 1.2 Report Manager, click the following article number to view the article in the Microsoft Knowledge Base:

834791 Adding, deleting, and renaming Microsoft CRM Reports by using Microsoft CRM 1.2 Report Manager

To save the report in the existing Microsoft CRM report folders, follow these steps:

Install the three sample reports.
Open Microsoft CRM 1.2 Report Manager.
For each file, follow these steps:
1. Locate the folder where you want to save the file.
2. Click Add Report.
3. Specify the file name and the downloaded report, and then click Add Report.

How to use Crystal Reports 9 to add parameters to a Microsoft CRM report

Introduction

To use Crystal Reports 9 to add a parameter to a Microsoft CRM report, you must follow these steps. The following sections in this article elaborate on each of these steps.

Define the parameter. To do so, specify the name of the parameter, the text to prompt the user for, the drop-down list elements, and the default value.
Put all the parameter fields on the report. The fields can be visible or hidden.
Add code to the record selection formula that compares the data that the user types in the parameter against the data from Microsoft CRM.
Save the report and test the parameter that you added.

You can use Crystal Reports to add many types of parameters. This article provides just four examples. Each example illustrates a different aspect of the use of parameters with Microsoft CRM data. This article includes the following examples:

An Owner parameter that lets the user select either just records they own, or all records. This example explains how to use the special UserID parameter to determine the Microsoft CRM GUID of the user running the report.
A Status parameter that lets the user select between all the status values available for the entity in Microsoft CRM. This example shows how to read the list of available values from a field in Microsoft CRM to use in the parameter choices.
A Date Created parameter that lets the user pick from predefined date ranges: all, within past 60 days, or year to date. This example shows how to use standard date methods in Crystal Reports 9.
A Date Range parameter that lets the user specify the start and end dates for the range. This example shows how to use a range parameter, and how to create additional formula fields based on a parameter field. In this case, the Date Range parameter is a range, and two new variables, StartDate and EndDate are created to use in the report page header.

Before you start

How Crystal Reports works with Microsoft CRM 1.2

Before you create parameterized reports, make sure that you understand how Crystal Reports works with Microsoft CRM 1.2:

Only reports in the Enterprise/MSCRM1.2 node are available to Microsoft CRM users.
You cannot create new folders under the MSCRM1.2 node.
You cannot use Crystal Reports to directly rename a report. To delete a report, you must use Microsoft CRM 1.2 Report Manager.

For additional information about Report Manager, click the following article number to view the article in the Microsoft Knowledge Base:

834791 Adding, Deleting, and Renaming Reports using Microsoft CRM 1.2 Report Manager
You cannot use Crystal Reports to directly rename a report. Use Microsoft CRM 1.2 Report Manager or Crystal Reports to save a report with a new name. These methods to rename the reports change the name in Microsoft CRM, but do not change the title that appears in the report's Page Header section. To change the title, on the File menu of Crystal Reports 9, click Summary Info, and then edit the title in the Report Title box.
For help with the creation of parameters, see Crystal Reports 9 online Help.

Create a parameter-driven report

The simplest way to create a parameter-driven report is to save an existing Microsoft CRM version 1.2 report with a new name, and then modify the copy:

On the Start menu, point to Programs, and then click Crystal Reports 9.
On the Welcome to Crystal Reports page, click Cancel.
On the File menu, click Open.
Click Enterprise, and then click OK.
Type your authentication information for your Crystal APS, and then click OK.
Expand MSCRM1.2, click the report that you want to modify, and then click Open.
On the File menu, click Save As, find the location where you want to save the file under the MSCRM1.2 node, specify the name, and then click OK.

If this is your first time using Crystal Reports 9 to modify Microsoft CRM reports, you have connection problems, or you cannot save reports, click the following article number to view the article in the Microsoft Knowledge Base:

834789 Frequently asked questions about reporting in Microsoft Business Solutions CRM version 1.2

Add an Owner parameter

With an Owner parameter, the user can select between records that the user owns and all records. Therefore, the user does not have to select from a drop-down list of all users.

To distinguish the records that the person who runs the report owns, the report must be able to identify the Microsoft CRM GUID of the person who runs the report. If the writer of the report creates a UserID parameter, Microsoft CRM automatically assigns the Microsoft CRM GUID of the person who runs the report as its value. Unlike other parameters, the user is not prompted to type the value.

Create an Owner parameter that holds the values "Mine" or "All." When the user runs the report and clicks Mine, if the UserId parameter matches the entity.owner box, the records that the user owns are selected. If the user clicks All, all the records are included.

The following example uses a report that is based on the Account entity.

Step 1: Define the UserID parameter to store the Microsoft CRM GUID of the current user

To define the UserID parameter to store the Microsoft CRM GUID of the current user, follow these steps:

On the Field Explorer pane in Crystal Reports 9, right-click Parameter Fields, and then click New.
In the Name box, type UserID, and then click OK.

Step 2: Define the Owner parameter

To define an Owner parameter that will prompt the user to click Mine or All, follow these steps:

On the Field Explorer pane, right-click Parameter Fields, and then click New.
In the Name box, type Owner.
In the Prompting text box, type the prompt that appears to the user. For example, type:

Specify which records to include
Click Set Default Values.
Type the values that you want in the drop-down list. This example uses "Mine" and "All." Use the > button to move each value into the Default Values list.
For each value, click Define Description, and then type the text that you want to appear in the drop-down list for the Owner parameter.
In the Display drop-down list, click Description, and then click OK.
In the Create Parameter Field dialog box, clear the Allow editing of default values when there is more than one value check box.
Click OK to save your new parameter.

For the Owner parameter to work, you must include it in the report. To do so, drag the parameter from the Field Explorer pane to any location on the report. It is a good idea to add any new parameters to the report's Page Header section. If the parameters are all listed on the Page Header section, a user can quickly determine what data the report includes. If you do not want the value of the Owner parameter to appear on the report, drag it to a hidden area on the report.

Step 3: Use the Owner parameter in the page header

Because Owner is the first parameter that you are adding to the page header, you must create a text box to hold the existing Report Title box. To do so, follow these steps:
1. Right-click the Report Title box on the report, and then click Delete.
2. On the Insert menu, click Text Object, and put the new text object where the Report Title field was.
3. On the Field Explorer pane, expand Special Fields.
4. Drag the Report Title special box to your new text object.
Drag the Owner parameter from the Parameter Fields section of the Field Explorer pane to your new text object.
Add any separator text or spaces to separate the title from the Owner parameter.
To make the font match the other Microsoft CRM reports, right-click the new text object, click Format Text, on the Font tab, click Verdana, click Bold, click Yellow, and then click OK.

Step 4: Add the Owner parameter to the record selection formula

The record selection formula

The record selection formula defines which records are included in the report. The formula follows the following structure:

(if {?Parameter1} = "Parameter1_Option1" and {table.field} = "value1" then true
  else if {?Parameter1} = "Parameter1_Option2" and {table.field} = "value2" then true)

For more information about record selection formulas, including templates for different parameter types, see "Record selection formulas" in the Crystal Reports 9 online Help.

In the following procedure, a record selection formula is created that verifies the Owner parameter value that the user clicks. If the user clicks Mine, only accounts that the current user owns are included in the report.

If the report that you are modifying is not based on the Account entity, replace account.ownerid with entity.ownerid.

Add the Owner parameter to the record selection formula

To add the Owner parameter to the record selection formula, follow these steps:

On the Report menu, click Selection Formulas, and then click Record.
Maximize the window.
If there is nothing listed in the bottom right pane, type the following formula without the "and" in the first line. If code is already there, add all three lines including the "and" to the end of the code list.
```
and
(if {?Owner} = "Mine" then {account.ownerid} = {?UserID} 
      else true)
```
When the report is run, the Owner prompt appears with the prompt text that you specified. When the user clicks OK, the report data appears.
Click Save, and then click Close.

Add a Status parameter

This section describes how to add a status parameter based on the value of the entity.statecode box in Microsoft CRM. The options that you give users are based on the values of this box in Microsoft CRM. This example uses the Activity entity.

Step 1: Define the Status parameter

To define the Status parameter, follow these steps:

On the Field Explorer pane, right-click Parameter Fields, and then click New.
In the Name box, type Status.
In the Prompting text box, type the prompt that you want to appear. For example, type:

Include activities with a status of
Click Set Default Values.
In the Browse Table box, click the name of the table that contains the status box. This example uses the Activity entity. Therefore, click activity.
In the Browse Field box, click statecodename.
In the Connection Info page, clear the Connect to Local Computer check box.
Load a list of all current values in Microsoft CRM. To do so, in the Server box, replace your computer name with the name of your Microsoft CRM server. For example, if this box says http://mycomputer/MSCRMServices, change it to http://Your_CRMServer/MSCRMServices, and then click Finish.

Note Crystal Reports 9 reads in all values of the entity.statecode box that are used in current data in Microsoft CRM. These values may not represent all the values that can appear in this box in Microsoft CRM. For a complete list of default values for drop-down boxes, see "Appendix A" of the Microsoft Business Solutions CRM Implementation Guide. If you have modified a drop-down box, view the box in Microsoft CRM to make sure that you include all the values in your parameter.
Click each value that you want in the drop-down list, and then click the > button to move each value into the Default Values list.
For each value, click Define Description, and then type the text that you want to appear in the drop-down list for the Owner parameter.
Order the values such that the option that you want as the default value is first on this list.
In the Display drop-down box, click Description, and then click OK.
In the Create Parameter Field dialog box, clear the Allow editing of default values when there is more than one value check box.
Click OK to save your new parameter.

For the new Status parameter to work, you must include it in the report. To do so, drag the parameter from the Field Explorer pane to any location on the report. The following procedure (Step 2) assumes that you have already created a text box in the report header to hold the Report Title box and the Owner parameter.

Step 2: Use the Status parameter in the page header

To use the Status parameter in the page header, follow these steps:

Drag the Status parameter from the Parameter Fields section of the Field Explorer pane to the text object in the Page Header section.
Add any separator text or spaces to separate the Status parameter from the Owner parameter.

In the following procedure (Step 3), a record selection formula is created that verifies the Status parameter value that the user selects. If the user clicks All, all activities are included. Otherwise, only records where the value of the field in Microsoft CRM matches the parameter that the user clicked are included.

If the report that you are modifying is not based on the Activity entity, replace activity.statecodename with entity.statecodename.

Step 3: Add the Status parameter to the record selection formula

To add the Status parameter to the record selection formula, follow these steps:

On the Report menu, click Selection Formulas, and then click Record.
Maximize the window.
If there is nothing listed in the bottom right pane, type the following formula without the "and" on the first line. If code is already there, add all three lines to the end of the code list.
```
and
     (if {?Status} = "All" then true 
     else {activity.statecodename}={?Status})
```
Click Save, and then click Close.

When the user runs the report, the Status prompt appears with the prompt text that you specified. When the user clicks OK, the report data appears.

Add Date parameters

This section describes how to use a parameter to force the user to specify a date range. The date range must be based on the values of one or more of the date boxes in Microsoft CRM. Each entity has an entity.createdon box and an entity.modifiedon box. Some entities have other date boxes. For example, Activity has dates based on scheduled and actual time.

You can create a parameter that prompts the user either to click items on a drop-down list that contains predefined date ranges such as within past 60 days and year-to-date, or to specify specific start and end dates.

The following two examples use the Incident (Case) entity.

Add a Date Created parameter

The Date Created parameter prompts the user with a list of specific predefined date ranges. To add a Date Created parameter, follow these steps:

To define the Date Created parameter, follow these steps:
1. On the Field Explorer pane, right-click Parameter Fields, and then click New.
2. In the Name box, type Date Created.
3. In the Prompting text box, type the prompt that you want to appear. For example, type:
  
  Specify records created:
4. Click Set Default Values.
5. Type the values that you want in the drop-down list. This example uses "Within Past 60 Days," "Year To Date," and "All." Use the > button to move each value into the Default Values list.
6. For each value, click Define Description, and then type the text that you want to appear in the drop-down list for the Date Created parameter.
7. In the Display drop-down list, click Description, and then click OK.
8. In the Create Parameter Field dialog box, clear the Allow editing of default values when there is more than one value check box.
9. Click OK to save your new parameter.
To use the Date Created parameter in the page header, follow these steps:
1. Drag the Date Created parameter from the Parameter Fields section of the Field Explorer pane to the text object in the Page Header section.
2. Add any separator text or spaces to separate the Date Created parameter from the Status parameter.
If the report that you are creating is not based on the Incident entity, replace incident.createdon with entity.createdon.

In step 4, a record selection formula is created that verifies the Date Created parameter value that the user selects. If the user clicks All, all the records are included. Otherwise, only records that match the criteria that are defined in the record selection formula appear. The selection formula code uses three predefined date methods from Crystal Reports, Aged0To30Days, Aged31To60Days, and YearToDate.
Add the Date Created parameter to the record selection formula. To do so, follow these steps:
1. On the Report menu, click Selection Formulas, and then click Record.
2. Maximize the window.
3. If there is nothing listed in the bottom right pane, type the following formula without the "and" on the first line. If code is already there, add all the lines to the end of the code list.
```
and
(if {?Date Created}="Within Past 60 Days" and   {incident.createdon} in Aged0To30Days or {incident.createdon} in Aged31To60Days then true
else if {?Date Created}="Year To Date" and {incident.createdon}in YearToDate then true
)
```
4. Click Save, and then click Close.
When the user runs the report, the Date Created prompt appears with the prompt text that you specified. When the user clicks OK, the report data appears.

Add a Date Range parameter

The Date Range parameter prompts the user for specific start and end dates.

Note When users print reports that use a range parameter, they are prompted for the range when they view the report and after they click Print.

To add a Date Range parameter, follow these steps:

Define the Date Range parameter. To do so, follow these steps:
1. On the Field Explorer pane, right-click Parameter Fields, and then click New.
2. In the Name box, type Date Range.
3. In the Prompting text box, type the prompt that you want to appear. For example, type:
  
  Include cases created between
4. Click Range Value(s), and then click OK.
For the Date Range parameter to work, you must include it in the report. You can drag the parameter from the Field Explorer pane to any location in your report.
If you want to display the start and end dates of the range in your report header, you must create two new formula fields, StartDate and EndDate. To do so, follow these steps:
1. On the Field Explorer pane, right-click Formula Fields, and then click New.
2. In the Name box, type StartDate, and then click Use Editor.
3. In Formula box, type:
  
  Minimum ({?Date Range})
4. Click Save, and then Close.
5. On the Field Explorer pane, right-click Formula Fields, and then click New.
6. In the Name box, type EndDate, and then click Use Editor.
7. In the Formula box, type:
  
  Maximum ({?Date Range})
  
  .
8. Click Save, and then click Close.
Use StartDate and EndDate in the report header. To do so, follow these steps:
1. Drag the StartDate and EndDate parameters from the Formula Fields section of the Field Explorer pane to the text object in the Page Header section.
2. Add any separator text or spaces to separate and identify the fields.
In step 4, a record selection formula is created that verifies the Date Range parameter value that the user clicks. If the user clicks All, all activities are included. Otherwise, only records where the value of the box in Microsoft CRM matches the parameter that the user clicks are included.

If the report that you are creating is not based on the Incident entity, replace incident.createdon with entity.createdon.
Add the Date Range parameter to the record selection formula. To do so, follow these steps:
1. On the Report menu, click Selection Formulas,, and then click Record.
2. Maximize the window.
3. If there is nothing listed in the bottom right pane, type the following formula without the "and" on the first line. If code is already there, add all three lines to the end of the code list.
```
and
(if {incident.createdon} in {?Date Range} then true
else false)
```
  In the second line, use the date field that you want to compare against the date range. Depending on the entity, it may be a good idea to use the modifiedon box instead of the createdon box. You can use only dates that exist in one of the entities that are included in the report.
4. Click Save, and then click Close.
When users run the report, the Date Range prompt appears with the prompt text that you specified. When the user selects the start and end dates for the range, and then clicks OK, the report data appears.

Report filters with parameter-driven reports

If you use report filters with parameter-driven reports, you must select the parameters two times. First, the users run the parameter-driven report and specify the parameters. Then, they click the icon at the right edge of the Report Filtering toolbar to open report filtering, enter filters, click Filter, retype the parameters when they are prompted, and then click OK.

REFERENCES

To download a list of frequently asked questions about Microsoft CRM reports, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyID=81886de8-a10a-473f-8125-49291e0f03d9

For additional information about how to use Microsoft CRM 1.2 Report Manager to modify reports, click the following article number to view the article in the Microsoft Knowledge Base:

834791 How to use Microsoft CRM 1.2 Reports Manager to modify reports

For additional information about how to print parameter-driven reports in Microsoft CRM 1.2, click the following article number to view the article in the Microsoft Knowledge Base:

834913 How to print parameter-driven reports in Microsoft CRM 1.2

Additional query words: CRM, reports, performance, time-out

Keywords: kbmbsreports kbmbsmigrate kbfile kbhowto KB834790

Microsoft KB Archive/834790

Contents