Running Microsoft CRM 1.2 Reports from a URL

Article
06/30/2006

Microsoft Corporation

March 2004

Applies to:
Microsoft® Customer Relationship Management (CRM) version 1.2

Requires:
Microsoft Business Solutions CRM version 1.2
Printing Parameter-Driven Reports Update, from Microsoft Knowledge Base article 834913

Summary: This article explains how to call reports from a URL so that you can create your own interface to Microsoft Customer Relationship Management (CRM) version 1.2 reports. For all reports, you can include pre-filter conditions in the URL. For parameterized reports, you can preload the value of any discrete-value parameters, so that the user does not need to fill in the parameter. This article also shows how to add a Favorite Reports menu to Microsoft CRM, with each menu item pointing to a useful pre-filtered report, and how to schedule reports to run automatically. (14 printed pages)

Note To pass pre-filter conditions or parameters in a report URL, you must have the Parameter-Driven Reports Update installed. For more information and to download the update, see Microsoft Knowledge Base article 834913, Printing Parameter-Driven Reports Update.

Introduction
Installing the Update
Steps for Calling a Report from a URL
Create a Favorite Reports Menu in Microsoft CRM
Scheduling a Report
Common Escape Sequences Used in URLs
More Information

Introduction

With Microsoft® Customer Relationship Management (CRM) version 1.2, you can run any standard or custom report from a URL. A Microsoft CRM update is available that makes it possible to:

In the URL, pass in a value for any attribute used in the report to pre-filter the data in the report. This filters the data before it is retrieved from Microsoft CRM.
In the URL, pass parameters to a parameterized report.

Running reports from a URL is useful in many scenarios:

You can create Web pages or Internet Explorer Favorites links for individual employees for reports they need to run regularly. This makes it easier for the employee to find exactly the reports needed for his or her job, and to control the pre-filters or parameters the employee should use.
You can significantly improve report performance by adding pre-filters and parameter values because you reduce the amount of data retrieved from the database.
When writing a customized report using Crystal Reports, you can test it using a consistent set of pre-filter or parameter values.
Users can schedule a report to run on a regular schedule using Scheduled Task in Control Panel. For example, on Friday afternoon, a sales representative might always need a report summarizing last week's activities, and report of activities due the next week; while a manager might want a summary of new invoices.

This article explains how to install the update, and how to run a report from a URL. It also provides code to add to the ISV.config file to add a Favorite Reports menu to Microsoft CRM. Each menu item points to a URL for a report. The URLs used in this code provide additional examples of calling standard reports with pre-filters, passing parameter values to a parameter-driven report, and how to do both in one URL.

The following figure shows how this menu appears in Microsoft CRM.

Figure 1. New Favorite Reports menu in Microsoft CRM, with each menu item pointing to the URL for the report (click picture to see larger image)

To use the features made available in this update, you need to understand how Microsoft CRM attributes are used in reports, and how to look up attributes in the Microsoft CRM schema.

When to Use Parameter Fields and When to Use Pre-Filters

You can only preload values for parameter fields in the URL if the report uses parameter fields. No standard Microsoft CRM version 1.2 reports use parameter fields. However, three sample parameter-driven reports are provided in Microsoft Knowledge Base article 834790, Writing Parameter-Driven Reports for Microsoft CRM Version 1.2 Using Crystal Reports 9.

Note These sample reports can be added to your Microsoft CRM installation using Microsoft CRM Report Manager, a free download available with Microsoft Knowledge Base article 834791, Adding, Deleting, and Renaming Reports using Microsoft CRM 1.2 Report Manager.

To modify existing reports or create new reports with parameter fields, you must have Crystal Reports 9 installed.

If you have Crystal Reports or are working with the sample reports, your choice between when to use parameter fields and when to use pre-filters should be based on three considerations:

If you want to limit data to records owned by the current user, you must use parameter fields, because a pre-filter cannot pass current user information in a format Microsoft CRM can use.
If you want users to provide input when they run the report, you must use parameter fields.
Only discrete-value parameter fields can be preloaded. Crystal Reports allows range parameter fields, and these cannot be preloaded from the command line.

Both offer performance improvements, and the ability to create a subset of data based on values of fields in any tables included in the report.

Avoiding Confusion in Pre-Filtered Reports

In standard Microsoft CRM reports, the report title and headings on the report do not display the value of pre-filter conditions. This can cause confusion for users, because the title and page header for a full report and a pre-filtered report are identical.

You may want to make reports that you call from a URL display the pre-filter criteria in the title or page header. To modify existing reports, or to create new reports, you need Crystal Reports 9. All new or modified reports must be stored in existing report folders in Microsoft CRM.

Installing the Update

To pass pre-filter conditions or parameters in a report URL, you must have the Parameter-Driven Reports Update installed. For instructions on downloading and installing the update, see Microsoft Knowledge Base Article 834913, Printing Parameter-Driven Reports Update.

This update replaces two files in the Microsoft CRM website: viewreport.csp and toolbar.aspx.

Steps for Calling a Report from a URL

To call a report from the command line, there are two steps:

Determine the ID of the report. Because the ID of a report may differ on different installations of Microsoft CRM, you should determine the ID based on the Microsoft CRM installation on which you plan to run the report from a URL.
Define the URL. If you are pre-filtering a report or passing in parameter values, you must build the query string for the URL.

Note When using a report from a URL, the user will be prompted for logon credentials.

Step 1: Determine the ID of the Report

In Microsoft CRM, click Reports, click the report category containing the report you want to pass a parameter to, and double-click the report.
Right-click the Microsoft CRM Report Viewer window, and then click View Source.
Look at the second line of the source code to get the value of the id parameter from the query string, shown in bold in the following sample:

<form name="CrystalViewerCrystalForm" id="CrystalViewerCrystalForm" 
method="post" action="/Reports/viewreport.csp?id=184&apstoken=… >

Step 2: Build the URL for a Standard or Custom Microsoft CRM Report

This feature works on any Microsoft CRM version 1.2 implementation.

Specify the URL, using the following syntax:

https://Microsoft_CRM_Server/Reports/viewer/html/viewer.aspx?
id=Report_ID

Microsoft_CRM_Server
Microsoft CRM server that will run the report
Report_ID
ID for the report, defined by Crystal Enterprise when the report is installed.

Step 3. Add a Pre-Filter to a Microsoft CRM Report URL

This feature works only if the parameter-driven report update has been installed.

Specify the URL, using the following syntax. For display in this article, a line-break has been added before id. This line-break must be removed.

https://Microsoft_CRM_Server/Reports/viewer/html/viewer.aspx?
id=Report_ID&cf_custom=pre-filter

Microsoft_CRM_Server
Microsoft CRM server that will run the report
Report_ID
ID for the report, defined by Crystal Enterprise when the report is installed.
Pre-filter
An expression specifying a condition for one or more attributes from the primary table
used in the report. Because this expression is passed as a query string to the URL, spaces should be represented as plus signs (+).

Crystal Reports has the following date range functions which can be used in pre-filters:

Aged0To30Days, Aged31To60Days, Aged61To90Days
AllDatesFromToday, AllDatesFromTomorrow, AllDatesToToday, AllDatesToYesterday
Calendar1stHalf, Calendar1stQtr, Calendar2ndHalf, Calendar2ndQtr, Calendar3rdHalf, Calendar3rdQtr, Calendar4thHalf, Calendar4thQtr
Last4WeeksToSun, Last7Days, LastFullMonth, LastFullWeek, LastYearMTD, LastYearYTD
MonthToDate, YearToDate
Next30Days, Next31To60Days, Next61To90Days, Next91To365Days
Over90Days
WeekToDateFromSun

In addition, you can use Microsoft Visual Basic® functions such as IsNull, and type conversion functions such as Cdate(year, month, day).

Examples

The following URL can be used to run the standard Account Activity and Notes report, pre-filtering for accounts located in Seattle with a category containing the word "Preferred". To try this on your Microsoft CRM server, be sure to use the server name and Report_ID from your Microsoft CRM installation.

For display in this article, line-breaks have been added to the sample code. These line-breaks must be removed.

https://MyServerName/Reports/viewer/html/viewer.aspx?
id=170&sf_custom=(({account.address1_city}%20like%20'Seattle')%20AND%20
({account.accountcategorycodename}%20like%20'*Preferred*'))

The following URL can be used to run the Opportunity List By Sales Rep report, pre-filtering for opportunities occurring during the past seven days. For display in this article, a line-break has been added to the sample code. This line-break must be removed.

https://MyServerName/Reports/viewer/html/viewer.aspx?id=224
&sf_custom=({opportunity.createdon}+in+last7days)

Determine attribute names and filter syntax

To determine attribute names and syntax used in filters, you can view the code generated by the Report Filtering feature of Microsoft CRM:

Run the report in Microsoft CRM.
On the Report Filtering toolbar, click the icon to open the filtering pane.
Enter criteria, and then click Filter.
Right-click the resulting report page, and then click View Source.

In the second line, beginning with <form, find the sf= parameter in the query string. For the first example above, this would be:

sf=%28%7Baccount%2Eaddress1%5Fcity%7D+like+%27%2ASeattle%2A%27+and+
CDbl%28%7Baccount%2Eaccountcategorycode%7D%29+%3D+1%29.

The value of sf in the source code uses more escape characters than are actually required when you pass values in the URL for reports. The following line shows the same string with the escape characters replaced with their equivalent ASCII characters. A table providing common escape sequences is provided at the end of this article. The CDbl function is used to ensure the value of the field is a number.

sf=({account.address1_city}+like+'*Seattle*'+and+
CDbl({account.accountcategorycode})+=+1).

Note that the variables shown are not exactly what you need to use. In this example, the integer format of the drop-down list Account Category is used, whereas when you build a URL, you probably will want to use the string value. For each drop-down list (picklist), Microsoft CRM uses two attributes: one for the integer value, called attributecode, and one for the string value, called attributecodename. When a pre-filter is created by Microsoft CRM, it always uses the integer value. When you create a pre-filter, you can use either the string or the integer, as long as the value you pass matches the attribute you use.

In addition, if your report filtering criteria includes a date expression like MonthToDate, the expression is resolved to specific dates based on the today's date. In your URLs, you can use expressions.

Step 4. Preload an Existing Parameter Field Value in a Microsoft CRM Report URL

This feature only works if the parameter-driven report update has been installed.

Specify the URL, using the following syntax. For display in this article, a line-break has been added before id. This line-break must be removed.

https://Microsoft_CRM_Server/Reports/viewer/html/viewer.aspx?
id=Report_ID&promptex-Parameter_field=value […]

Microsoft_CRM_Server
Microsoft CRM server that will run the report

Report_ID

ID for the report, defined by Crystal Enterprise when the report is installed

Parameter_field
One existing parameter field defined in the report. Because this field is passed in the URL query string, spaces in the field name should be represented as plus signs (+), and double-quotes should not be used. For each additional parameter field, use a new &promptex term.

Value

The value for the Parameter Field. Because this value gets passed in the URL query string, spaces in the field name should be represented as plus signs (+).

Comments

If your report has multiple parameter fields, and if you preload values for all of them in the URL, the user will not see the parameter prompt page. If any parameter fields are not preloaded in the URL, the parameter prompt page will be displayed, showing only those parameter fields that were not preloaded.

For example, the following figure shows a report called from a URL that doesn't preload the Status parameter field.

Figure 2. Parameter prompt page showing preloading of some, but not all parameter fields

Example

This example is based on the sample parameterized report Opportunity List By Account (specify owner, date, accounts) provided in Microsoft Knowledge Base article 834790, Writing Parameter-Driven Reports for Microsoft CRM Version 1.2 Using Crystal Reports 9.

This report has three parameter fields defined: Account Owner, Opportunity Created, and Account Name. The example preloads two of the fields respectively with a valid parameter value for each parameter. In this example, the user would be prompted only for the third parameter: Account Name.

To try this URL on your Microsoft CRM server, be sure to use the server name and Report_ID from your Microsoft CRM installation, and to remove any spaces in the URL.

https://crmsrv01/Reports/viewer/html/viewer.aspx?id=716
  &promptex-Account+Owner=My+Accounts
  &promptex-Opportunity+Created=Within+Past+30+Days

Determine parameter field names and valid values

To determine valid values for Parameter_Field and Value, if you have Crystal Reports, open the report, and in the Field Explorer pane, expand Parameter Fields.

If you do not have Crystal Reports, you can view the information as specified by the parameter-driven report:

Run the parameter-driven report in Microsoft CRM.
Specify the parameters the way you want them to appear in the URL, and then click OK.
Right-click the resulting report page, and then click View Source.
In the last line, you can see the promptex data. Note that extra line breaks have been added in the following sample code.

<input type="hidden" name=promptex 
value='&promptex-UserID=%22%7B09AAD3AF-21B3-4C67-B2B7-90C9CF87396A%7D%22
&promptex-Owner=%22My%20Accounts%22
&promptex-Status=%22Active%22&promptex-Date Completed=%22Within%20Past%2030%20Days%22
&promptex-Date Scheduled=%22All%22'/>

The following line shows the same code with the escape characters replaced by equivalent ASCII characters:

<input type="hidden" name=promptex 
value='&promptex-UserID="{09AAD3AF-21B3-4C67-B2B7-90C9CF87396A}"
&promptex-Owner="My+Accounts"
&promptex-Status="Active"&promptex-Date+
Completed="Within+Past+30+Days"
&promptex-Date Scheduled="All"'/>

Cut and paste the values of all promptex portions from this line except for UserID. UserID is used within Crystal Reports to get the Microsoft CRM GUID of the current user.

To create a custom menu in Microsoft CRM, you must modify two configuration files:

Modify the Web configuration file to enable use of the ISV configuration file.
Modify the ISV configuration file to add custom menu items.

For more information about how to customize these files, see Customizing the Microsoft CRM Application in the Microsoft CRM SDK.

Modify the Web Configuration File

On your Microsoft CRM server, edit the file <installation drive and folder>\inetpub\wwwroot\web.config.

In the section "ISV Integration," change the setting of ISVIntegration to On.

   <add key="ISVIntegration" value="On"/>

Modify the ISV Configuration File

The ISV configuration file is located on your Microsoft CRM server at <installation drive and folder>\inetpub\wwwroot\_Resources\isv.config. The sample code is added to the <CustomMenus> section at the beginning of the file. Note that the <CustomMenus> section by default includes a sample menu called ISV. Delete the following lines from the ISV.config file:

<Menu Title="ISV">
   <MenuItem Title="New Window" Url="https://www.msn.com"/>
   <MenuSpacer/>
   <SubMenu Title="ISV Tests">
      <MenuItem Title="Modeless Dialog" Url="https://www.msn.com" WinMode="2"/>
      <MenuSpacer/>
      <MenuItem Title="Modal Dialog" Url="https://investor.msn.com" WinMode="1"/>
   </SubMenu>
</Menu>

For additional information about the ISV configuration file, see the Microsoft CRM SDK Application Integration and Customizing the Microsoft CRM Application topics.

The following list shows the reports that are put on the Favorite Reports menu by the sample code, explains which report is used for each menu item, lists the pre-filters and parameters, and shows the actual URL used. A menuspacer item is used to separate success reports from potential problem reports.

You can copy these URLs into the Address box of Internet Explorer to try them by following this procedure:

Change the server name to the name of your Microsoft CRM server
Look up the Report_ID for the report on your Microsoft CRM installation.
Delete any spaces when you cut and paste the URL. In order to format the article properly for MSDN, line lengths are restricted. The added spaces will prevent the URL from working.

Management Reports

Menu item: New Opportunities This Week

Report: Opportunity List By Sales Rep report

Pre-filter: Opportunity.createdon in Last7Days

Parameter values: none

https://MyServerName/Reports/viewer/html/viewer.aspx?id=224
  &sf_custom=({opportunity.createdon}+in+last7days)

Menu item: Orders Over $1,000 This Month

Report: Order List By Sales Rep

Pre-filters: order.totalamount>1000 and order.createdon in MonthToDate

Parameter values: none

https://MyServerName/Reports/viewer/html/viewer.aspx?id=253
  &sf_custom=(({salesorder.totalamount}+>=1000.00)+and+
  ({salesorder.createdon}+in+MonthToDate))

Menu Item: Past Due Activities

Report: Activity List

Pre-filters: activity.duedate in AllDatesToYesterday, activity.statecodename=Open

Parameter values: none

https://MyServerName/Reports/viewer/html/viewer.aspx?id=189
   &sf_custom=({activity.statecodename}+LIKE+'*Open*')+AND+
   ({activity.scheduledstart}+in+AllDatesToYesterday)

Weekly Sales Representative Reports

Menu Item: My Activities Completed This Week

Report: Sample Account Activity and Notes (specify status, owner, date)

Pre-filters: activity.actualend in WeekToDateFromSun

Parameter values: owner=My Accounts, Status=All, Date Completed=All, and Date Scheduled=Within Past 30 Days

https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
  &promptex-Owner=My+Accounts
  &promptex-Status=All
  &promptex-Date Completed=Within+Past+30+Days
  &promptex-Date Scheduled=All
  &sf_custom=({activity.actualend}+in+WeekToDateFromSun)

Menu Item: My Activities Due Next Month

Report: Sample Account Activity and Notes (specify status, owner, date)

Pre-filters: activities scheduled (activity.scheduledstart values) in Next30Days

Parameter values: Owner=My Accounts, Status=All, Date Completed=All and Date Scheduled=In The Future

https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
  &promptex-Owner=My+Accounts&promptex-Date+Scheduled=In+The+Future
  &promptex-Status=All&promptex-Date+Completed=All
  &sf_custom=({activity.scheduledstart}+in+Next30Days)

Menu Item: My Activities with No Due Date

Report: Sample Account Activity and Notes (specify status, owner, date)

Pre-filter: blank due date (null value in Activity.scheduledstart) and no actual end date (non-null value in activity.actualend)

Parameter values: owner=My Accounts, status=Open, Date Completed=All and Date Scheduled=All

https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
  &promptex-Owner=My+Accounts&promptex-Date+Scheduled=All
  &promptex-Satus=Open
  &promptex-Date+Completed=All
  &sf_custom=(IsNull({Activity.scheduledstart}))+AND+
  ({activity.statecodename}+LIKE+'*Open*')

You will need to modify the following sample code before putting it in your ISV.config file.

Caution Back up your ISV.config file before adding this section. Microsoft CRM will not start properly if there are errors in the ISV.config file.

In each URL, change the server name from MyServerName to the name of your server.
For each report, check the ID. If it is different from what's listed below, change it to the value from your Microsoft CRM installation.
Remove all spaces in the URL. The sample code below uses + when a space is required: any other spaces are an artifact of cutting and pasting the text from the MSDN article.

Because of the way the URL is defined and used in the ISV.config file, the following escape sequences should be used for any URLs used in ISV.config. No escape sequences are required for parentheses or curly brackets.

Character	Escape sequence
(space)	+
'	\'
&	&

The following code should be inserted in the <CustomMenus> section of ISV.config. You must make the required changes to server name and report ID in each URL. In addition, to format the code for this article, extra line-breaks have been added and need to be removed. All MenuItem values must be on one line.

<Menu Title="Favorite Reports">
    <SubMenu Title="Management Reports">
       <MenuItem Title="New Opportunities This Week" 
Url="https://MyServerName/Reports/viewer/html/viewer.aspx?id=224
&amp;sf_custom=({opportunity.createdon}+in+last7days)" />
       <MenuItem Title="Orders Over $1,000 This Month" 
Url="https://MyServerName/Reports/viewer/html/viewer.aspx?id=253
&amp;sf_custom=(({salesorder.totalamount}+>=1000.00)+and+
({salesorder.createdon}+in+MonthToDate))" />
       <MenuSpacer/>
       <MenuItem Title="Past Due Activities"
Url="https://MyServerName/Reports/viewer/html/viewer.aspx?id=189
&amp;sf_custom=({activity.statecodename}+LIKE+\'*Open*\')
+AND+({activity.scheduledstart}+in+AllDatesToYesterday)/>
    </SubMenu>
    <SubMenu Title="Weekly Reports for Sales Representatives">
       <MenuItem Title="My Activities Completed This Week" 
Url="https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
&amp;promptex-Owner=My+Accounts&amp;promptex-Status=All
&amp;promptex-Date Completed=Within+Past+30+Days
&amp;promptex-Date Scheduled=All
&amp;sf_custom=({activity.actualend}+in+WeekToDateFromSun)" />
       <MenuItem Title="My Activities Due Next Month" 
Url=" https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
&amp;promptex-Owner=My+Accounts&amp;promptex-Status=All
&amp;promptex-Date+Scheduled=In+The+Future
&amp;promptex-Date+Completed=All
&amp;sf_custom=({activity.scheduledstart}+in+Next30Days)"/>
       <MenuSpacer/>
       <MenuItem Title="My Activities With No Due Date" 
Url=" https://MyServerName/Reports/viewer/html/viewer.aspx?id=546
&amp;promptex-Owner=My+Accounts
&amp;promptex-Status=All
&amp;promptex-Date+Scheduled=All
&amp;promptex-Date+Completed=All
&amp;sf_custom=(IsNull({Activity.scheduledstart}))+AND+
({activity.statecodename}+LIKE+\'*Open*\')"/>
    </SubMenu>
  </Menu>

Scheduling a Report

A user can schedule a report to run at a regular time using Scheduled Task in Control Panel. The following steps are for Microsoft Windows® XP.

In Control Panel, double-click Scheduled Tasks, and then double-click Add Scheduled Task.
Click Next, select Internet Explorer, and then click Next.
Type a name for this task, select how often to run it, and then click Next.
Select the time and day you want the task to start, and then click Next.
Enter the name and password of the user with permission to run the report.
Click Next, and then click Finish.
Right-click the task, and then click Properties.
On the Task tab, in the Run box, paste the URL for the report after IEXPLORE.exe. Click OK.

Common Escape Sequences Used in URLs

Escape sequences are formed by a percent sign (%) followed by the HEX equivalent of the ASCII character. The following table shows common characters used in creating query strings. For a complete list, see any ASCII to HEX conversion table.

Character	Escape sequence
(space)	%20 (+ can also be used)
*	%2A
&	%26
{	%7B
}	%7D
]	%5D
(	%28
)	%29
[	%5B
'	%27
_	%5F
"	%22
/	%2F
\	%5C
#	%23
%	%25
=	%3D
>	%3E
<	%3C
?	%3F

More Information

The following articles provide useful information:

For information about creating reports for use with Microsoft CRM, see Microsoft Knowledge Base article 834789, Frequently Asked Questions About Reporting in Microsoft CRM Version 1.2 and the online Help in Crystal Reports.
For information about creating parameter-driven reports and sample parameter-driven reports including the one used in the sample Favorite Reports menu, see Microsoft Knowledge Base article 834790, Writing Parameter-Driven Reports for Microsoft CRM Version 1.2 Using Crystal Reports 9.
To view the Microsoft CRM schema, see Object Schemas in the Microsoft CRM SDK.

Running Microsoft CRM 1.2 Reports from a URL

Contents

Introduction

When to Use Parameter Fields and When to Use Pre-Filters

Avoiding Confusion in Pre-Filtered Reports

Installing the Update

Steps for Calling a Report from a URL

Step 1: Determine the ID of the Report

Step 2: Build the URL for a Standard or Custom Microsoft CRM Report

Step 3. Add a Pre-Filter to a Microsoft CRM Report URL

Step 4. Preload an Existing Parameter Field Value in a Microsoft CRM Report URL

Create a Favorite Reports Menu in Microsoft CRM

Modify the Web Configuration File

Modify the ISV Configuration File

Management Reports

Weekly Sales Representative Reports

Scheduling a Report

Common Escape Sequences Used in URLs

More Information

Additional resources