SQL Server Reporting Services
SQL Server Reporting Services provides a complete
server-based platform for the delivery, creation, and administration of
reports. Microsoft CRM uses SQL Server Reporting Services as its reporting
engine, and Microsoft CRM takes advantage of many built-in features of
Reporting Services, such as e-mail delivery, report scheduling, exporting
reports to multiple formats, report snapshots, and report caching.
This chapter will cover many aspects of Reporting Services and how
it pertains to Microsoft CRM. However, Reporting Services is far too complex to
cover adequately in this text, and we encourage you to review the Reporting
Services Online Help installed with the product and the following links for
Product overview: http://www.microsoft.com/sql/technologies/reporting/overview.mspx
Report Definition Language: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSRDL/htm/rsp_ref_rdl_52g5.asp
SQL Server Reporting Services manages all parts of reporting,
including report authoring, data source management, report security, output
formats, and multiple delivery mechanisms. Figure 7-5 outlines the Reporting
Services architecture and its various reporting components.
In addition to SQL Server, Reporting Services supports other data source types
such as OLE DB and ODBC. This book will focus exclusively on SQL Server data
sources because Microsoft CRM uses SQL Server to store its data.
Licensing and Installation
Important Because Reporting Services supports multiple data sources, you
can create a single report that combines Microsoft CRM data with other
non-Microsoft CRM data (assuming Reporting Services supports the data type that
you want to combine in the report).
Reporting Services report files use an .rdl file extension. RDL stands for
Report Definition Language, an open-schema XML language definition that defines
the data retrieval and display layout of a report. You can use Microsoft Visual
Studio .NET to create report .rdl files, but you can also use any other report
authoring tool that supports the RDL schema.
In addition to the report authoring flexibility created by using open-schema
.rdl files, Reporting Services offers a programming model that allows
developers to further customize and enhance the Reporting Services
functionality. Please reference the previously listed resources if you're
interested in learning more about the Reporting Services programming model and
Reporting Services is a component of SQL Server, so there is no charge for the
Reporting Services software if you have a valid SQL Server license and you
install the Reporting Services web on the same server as SQL Server.
Reporting Services Reports in the Microsoft CRM User Interface
When you install Microsoft CRM, you can choose to have it install Reporting
Services for you, or you can direct the Microsoft CRM installation program to
use an existing server running Reporting Services. Installing Reporting
Services can be tricky, but the Microsoft CRM Implementation Guide provides
excellent troubleshooting information if you experience a problem. You can also
reference the RSSetup.chm Help file located in the SRS directory of your
Microsoft CRM installation CDs for more detailed instructions on installing
More Info In high-volume Microsoft CRM deployments, we would recommend that you
use a dedicated server running Reporting Services. By splitting the reporting
load from the Microsoft CRM SQL Server, both machines will perform better for
users' requests. Microsoft published a short white paper titled "Improving
Performance of Microsoft CRM 3.0 by Using a Dedicated Report Server" that
defines how to set up a dedicated report server with Microsoft CRM
You access Reporting Services reports from the Microsoft CRM
user interface by using one of three methods:
The default Microsoft CRM installation creates a subarea
called Reports in the My Work group of the Workplace area (see Figure 7-6). In
addition to listing all of the available reports, you can use this Reports list
to administer the reports, assuming that you have the Manage Reports security
privilege. By default, the System Customizer and System Administrator security
roles have the necessary permissions to edit reports.
Tip Remember that you can modify the site map to make the Reports list
appear wherever you want in the application navigation, such as creating a new
area called Reports in the wunderbar. The URL to use in the Site Map for the
Reports list is Url="/CRMReports/ home_reports.aspx".
In addition to the reports list, you can allow users to run
reports from the toolbar of an entity's grid by clicking the Report button, as
shown in Figure 7-7 for the Account entity.
This figure shows reports listed under one of two groups: Select
Records or Run Report. If the user chooses to run one of the reports listed
under Select Records, Microsoft CRM prompts them to select which records they
want to apply to the report. The three options are:
By selecting one of these three options, users can pre-filter the records that
they want Microsoft CRM to include in the report results.
If the user selects a report listed under the Run Report group, Microsoft CRM
will run the report independent of the selected records or the records that
appear in the view.
Important We refer to reports that run for Select Records as contextual reports
because they run within the context of particular records. You must create the
report query using the correct technique to create your own custom contextual
reports. We explain this technique later in this chapter.
Similar to running reports from the entity list, you can also
run reports directly from the entity form by clicking the Reports button on the
menu bar (Figure 7-8).
And just like running reports from the entity list, you can choose
to run a contextual report or a non-contextual report. On the entity form,
Microsoft CRM lists contextual reports under the Use Current Record grouping
and the non-contextual reports under the Run Report grouping. If you choose to
run a contextual report from the entity form, Microsoft CRM will not prompt you
(like the entity list report) to further refine the record set because there is
only one record to run the report for. Figure 7-9 shows the output if you run
the contextual Account Overview report.
Without the contextual report feature, if you wanted to run the Account
Overview report for a single Account, you would have to navigate to the Reports
list, pick the report you wanted to run, and then manually specify an Account.
Instead, Microsoft CRM allows you to launch a contextual report directly from
the entity menu bar for the Account you're currently viewing to save users
approximately 10 to 15 clicks every time they run a report.
Running a Reporting Services Report
Tip Create contextual reports whenever possible for your custom reports
to save your users extra clicks in the application navigation.
We'll explain later in this chapter how to configure contextual reports and
specify where you want them to appear in the application navigation.
Regardless of where you want to access the report from,
running the report is straightforward. From the Reports list, simply
double-click the name of the report that you want to run. You can run
contextual reports by clicking the report name in the form or grid toolbar.
Now let's examine what your users see when they run Reporting
You'll need to understand these parts of the report output to
properly administer and manage your reports.
Microsoft CRM allows you to create Reporting Services reports
with a pre-filtering option. Pre-filtering gives users
the opportunity to set up and modify filter criteria before running the report.
By pre-filtering a report, users can drastically reduce the number of records
that Reporting Services must
manipulate, which will provide an increase in the report's performance. When
users run a report with pre-filtering enabled, they see the report filtering
criteria on the Report Viewer page. Figure
7-10 shows the default pre-filter page for the Account Distribution
As you can see, this report pre-filtering allows the user to enter values for
four default filters before running the report. If the user does not enter
values where prompted by the Enter Value text (Industry, Territory, and Owner),
the report will run as if that filter does not exist.
Important The report pre-filtering functionality is unique to Microsoft
CRM, so users can access pre-filtering only when they run reports within
Microsoft CRM. If they navigate directly to the Web server running Reporting
Services and run a report from there, the pre-filtering option is not
available. Likewise, you must upload reports through Microsoft CRM to include
the pre-filtering feature. You should not upload the reports directly to
In addition to the default pre-filter parameters that appear on the Report
Viewer page, users can further modify the pre-filter by clicking the Edit
Filter button. The report pre-filter uses the same user interface as the
Advanced Find feature (as shown in Figure 7-11), so users should be able to
easily manipulate the pre-filter settings.
After you set the pre-filter criteria for your reports, click the Run Report
button to execute the report. Microsoft CRM displays a status message to the
user while it creates the report
After executing the report, Microsoft CRM updates the Report Viewer page with
the completed report. Figure 7-12 shows the output for the Account Distribution
In Figure 7-12, we highlighted two areas of the report output. The report
navigation bar allows you to navigate records, change the zoom level, find text
in the report output, export the results, and refresh and print the data. The
navigation bar is common to all Reporting Services reports.
Above the report navigation bar are the report parameters unique to this
report. By using report parameters, you can further refine the results in your
report. Reporting Services supports many types of parameters, including text
fields and drop-down lists, as shown in the Account Distribution example.
Important Report parameters are not the same as the pre-filter criteria.
Microsoft CRM lets you use pre-filter criteria to reduce the number of records
returned in your report. After Reporting Services generates the report, you can
use report parameters to filter the report records in the result set. You
define report parameters in the report .rdl file; you define pre-filter
criteria in Microsoft CRM.
If you double-click any of the columns in the Account Distribution chart, a new
report appears on the Report Viewer page. Reporting Services refers to this
nested report as a sub-report. Sub-reports allow you to link reports together
so that users can examine a specific area of a report to get more detailed
information. You can configure a sub-report to dynamically accept parameters
from its parent report.
Tip Creating sub-reports and drill-throughs on your custom reports takes more
work to develop and test, but users absolutely love this feature. Consider
adding this drill-through feature on some of the most popular or important
custom reports in your deployment.
After you manipulate the report results to display the records that you want,
Reporting Services allows you to export the report data to multiple formats
easily. To export a report, simply choose a format from the Select A Format
list in the report navigation bar, and then click Export, as shown in Figure
HTML with Office Web Components
Converts the report to a Web page that can be opened with
Microsoft Office products using the Office Web Components tools.
Useful for the interactive tools available with Office Web
Requires Office Web Components version 10 (Office XP) on the
This export option was deprecated from SQL Server Reporting
Opens the report data in Excel.
Ideal for additional manipulation or analysis of the data and
for storing the data offline.
Requires Excel version 10 (Office XP) or version 11 (Office
2003) on the client computer.
Exporting to Excel can be an intensive process for the
server, especially for large or complex reports.
Acrobat (PDF) File
Renders output as a self-contained Web-based file (MHTML) and
opens in Microsoft Internet Explorer. It will also embed any images directly
into the file format.
Advantageous if you need a portable, offline format. However,
this format can be rendered accurately with Internet Explorer only.
Saves the report as a .pdf file that can be opened with Adobe
Optimal choice for paginated reports, reports that will be
printed, or reports that will be delivered to a broad range of client machines.
Saves the report as an image file that opens with an
application associated with this file type (such as Microsoft Windows Picture
and Fax Viewer).
Generally used for printing or graphics purposes.
Not recommended for reports with large amounts of data.
CSV (Comma Delimited)
XML File with Report Data
Saves the report data in a comma separated value (CSV) format
that can be opened with any application that handles .csv files, such as Excel.
Smallest file size of any export option.
Typically used when integrating the report data with other
applications. It is also useful for opening within Excel for additional
Authoring Reporting Services Reports
Saves the report as raw
Typically used when programmatically integrating reporting data with other
Microsoft CRM includes 20 Reporting Services reports in the default
installation, and these 20 reports include 23 additional sub-reports. However,
you will definitely want to create new reports (or modify the default reports)
as you customize your Microsoft CRM database with new entity attributes and
Report Authoring Tools
As we explained earlier in this chapter, Reporting Services includes the most
powerful reporting features and functionality in Microsoft CRM, but the
tradeoff is that creating or modifying Reporting Services reports typically
requires an experienced report writer. Therefore, we don't expect to tell you
everything you need to know about Reporting Services in this chapter, but we do
want to demonstrate a few simple examples and highlight some unique areas of
Microsoft CRM that relate to Reporting Services.
Although you can use any RDL-compliant report authoring tool, most Microsoft
CRM customers will use Visual Studio .NET with the Reporting Services Report
Designer add-in to author Reporting Services reports.
Installing the Report Designer Add-In for Visual Studio .NET
More Info When you install Microsoft CRM, you can choose to install SQL
Server 2000 Reporting Services, but not SQL Server 2005 Reporting Services.
However, you can point a Microsoft CRM installation to an existing SQL Server
2005 Reporting Services installation for use with Microsoft CRM. Because
Microsoft CRM installs SQL Server 2000 Reporting Services, all of our examples
will show SQL Server 2000 Reporting Services and Visual Studio .NET 2003.
The Microsoft CRM installation discs include the Reporting Services Report
Designer add-in, so you can easily install it if you have Visual Studio .NET on
Navigate to the SRS folder of the Microsoft CRM installation
disc 1 on your client computer.
Double-click Setup.exe and follow the
Complete the wizard, accepting all of the default settings.
The installation wizard might tell you that it can't install the server running
Reporting Services components.
Because you want to install only the Report Designer add-in (a
client component), you can ignore this warning and click Next.
After installation is complete, confirm that the Report
Designer installed correctly. Open Visual Studio .NET 2003.
On the File menu, point to New,
and then click Project.
Under Project Types, look for a project
type called Business Intelligence Projects. If you see this project type, the
Reporting Services Report Designer installed successfully.
M Important You should not install the Report Designer on the server
running Microsoft CRM or Reporting Services. Rather, you should always edit the
report .rdl files on a client computer and then upload the files to the server
when you're finished.
Editing a Reporting Services Report
For additional resources regarding developing reports in Reporting Services,
you can install the Reporting Services Books Online.
We will now show you how to use Visual Studio .NET 2003 and the Report Designer
to edit one of the Microsoft CRM default reports and then upload the modified
report back to Microsoft CRM. You might need to edit the default Microsoft CRM
reports if you add custom attributes and you want to modify the report layout
to include these new fields.
Tip The default Reporting Services reports in Microsoft CRM use complex
data sets and advanced reporting features. You should edit these reports only
if you're extremely comfortable authoring Reporting Services reports. Beginner
or intermediate report writers might feel more comfortable creating new reports
from scratch instead of trying to edit the default Microsoft CRM repots.
In the following example, we will show you how to modify the Account Overview
report. Let's assume we would like to add the number of employees as a field in
the Basic Profile section of the report. Figure 7-14 shows the final report
with the field added.
Almost all of the default Microsoft CRM reports use a sub-report to display the
report details, and the Account Overview report is no different. Therefore, we
need to modify the Account Overview Sub-Report to add the number of employees
field to the report layout. Warning Whenever you update a report, make sure
that you save a backup of the original. This will allow you to roll back to the
original version should you have any problems.
Modifying the Account Overview Report
Creating a New Reporting Services Report
Click the Reports subarea in the
Change the Category to Hidden
Reports, and then select the Account Overview subreport.
Click More Actions, and then click
Download Report. Save the report to your desktop, making sure that the
file you download has an .rdl file extension instead of an .xml file extension.
You can do this by changing the Save as Type drop-down from XML Document to All
In Visual Studio .NET 2003, click the File
menu, point to New, and then click Project.
In the Project Types section, select
Business Intelligence Projects, and in the Templates
section, select Report Project.
Give your Visual Studio project a name like "CRM Reports,"
and then click OK. Visual Studio creates a Reporting
Services project with two empty folders: Shared Data Sources and Reports.
Right-click the Reports folder, point to Add,
and then click Add Existing Item.
In the Look in list, click Desktop.
Select the Account Overview Sub-Report.rdl file, and then click Open.
Visual Studio adds the report to your project. Double-click
the report to open it in Layout mode.
Click the Data tab to verify your data
connection. If your data connection does not work, you will receive the
If your preview does not generate an error, you do not need
to edit your data connection, and you can skip to step 14. To edit your data
connection, click the Data tab, and then click the ellipsis
(…) button on the toolbar.
In the Dataset dialog box, click the
ellipsis (…) button next to Data
Source: CRM to open the Data Source dialog box.
Make sure that the data source and initial catalog values in
the Connection String are correct for your environment. When you download
reports, sometimes Microsoft CRM will set the data source to localhost and the
initial catalog to Adventure_Works_Cycle_MSCRM. Change these default values to
the correct values for your deployment. The data source should be the name of
your Microsoft CRM SQL Server. The initial catalog should be the name of the
Microsoft CRM database. The initial catalog name should appear as
organizationname_MSCRM, where organizationname is
the organization name used when Microsoft CRM was installed. After you edit
these values, click OK to close the Data Source
dialog box, and then click OK in the Dataset
dialog box. If you click the Preview tab, it should display
a blank Account Overview report. If you still receive an error, review your
data source settings.
Before you can add the number of employees field to the
report, you must modify the report's dataset so that the report query includes
the number of employees field in the result set. As we mentioned earlier, most
of the default Microsoft CRM reports include multiple data sets, so you'll need
to know which data set to edit. We already determined that you want to add the
number of employees field to the ds_BasicProfile data set. To edit the query,
click the Data tab and select ds_BasicProfile
from the Dataset list. The SQL query text can sometimes
look awkward in the Generic Query window.
To convert the SQL query text to a format that's easier to
read, you can click the Generic Query Designer button (outlined in the screen
shot) and then click it again. Visual Studio will format the query a little
more cleanly, but it will still have some inconsistent spacing.
To add the number of employees field to the query, you need
to know the schema name of the attribute. Remember that one method to look up
attribute schema names is to browse to http://<crmserver>/sdk/list.aspx.
You'll find that the schema name we're looking for is numberofemployees.
To add this field to the query, add the following text after the SELECT keyword
in the query:
This is a complex query that we won't explain in detail; however,
facct is an alias that the query uses to reference the FilteredAccount
database view. A snippet of the final code with the new field added would look
like the following.
DECLARE @AcctID nvarchar(100)
SET @AcctID = @CRM_CustomerID
LEFT(cast(getdate() AS nVarchar), 5) + ', ' + cast(year(getdate())
AS nVarchar) AS title, LEFT(Facct.[name], 40) AS Custname, getdate()
After you add the field to the query, click the Save
button in the Visual Studio .NET toolbar. Make sure you save before you click
on the Layout or Preview tab again; otherwise, you might receive a warning
Now that the report query results include the number of
employees data field, we can add that field to the report output in the Layout
section. Click the Layout tab, and then click the text box that contains
In the table outline, right-click the icon with the three
horizontal lines next to Ownership, and then click
Insert Row Below to insert a new row between Ownership
and Ticker Symbol.
Click the text box under Ownership and
type No. of Employees:.
Right-click the box to the right of the No. of
Employees field, and then click Properties.
In the Texbox Properties dialog box, do
In the Name box, type numberofemployees.
In the Value list, select =Fields!numberofemployees.Value.
This should be at the top of the list because you added it as the first field
in the query. If this field doesn't appear automatically, you can manually type
it into the Value box.
Click the =Fields!numberofemployees.Value
field and then select the text alignment for this field to the left to remain
consistent with the other fields in this column. You can set the text alignment
from the toolbar or by modifying the TextAlign property in the Properties
Save your report by clicking Save All on
the File menu. If you try to preview the report, you won't
see any data because the report needs an Account ID value to run correctly. You
must upload the report to Microsoft CRM to see it work.
In the Web client, navigate to the Microsoft CRM Reports
Select the Account Overview sub-report
(in the Hidden Reports category), click More
Actions, and then click Edit Report.
In the Source section, click Browse,
select the Account Overview sub-report that you just
edited, and then click Save and Close.
Run the Account Overview report, and you'll see No. of Employees
in the Basic Profile section of the report
As you saw with the Account Overview report example, the default Microsoft CRM
Reporting Services reports use complex queries, multiple data sets, and
sub-reports, so you might not feel comfortable making significant changes to
those reports. Therefore, we recommend that beginner report writers create
entirely new reports. We'll walk you through this process.
Creating a New Report
Let's walk through a quick example of creating a new report from scratch. Our
sample report will create a list of all the Activity records for an Account.
This report will help users because it will display both open and closed
Activities for an Account on a single page. We will also show you how to use
some of the special reporting fields such as the pre-filter field that
Microsoft CRM provides to include additional functionality in your report.
Tip When creating a new report, you might be able to find an existing
report to use as a template. This allows you to create a report with the same
formatting as the default reports.
Using the same CRM Reports project you created in the Account
Overview example, right-click the Reports folder, and then
click Add New Report.
If this is your first time creating a new report, you will
see the Report Wizard. Click Next.
The first step of a new report is to create a new data
In the Name box, type CRM.
In the Type list, select Microsoft
To enter the Connection String, click the
Edit button, which opens the Data Link Properties dialog
On the Connection tab, enter or select
the name of the computer running SQL Server on which you installed Microsoft
Select the Use Windows NT Integrated security
Select your database (<organizationname>_MSCRM).
If you select the Make this a shared data source
check box, you can reuse this data source for additional reports in the Visual
Studio Report Designer. However, you cannot deploy a report to Reporting
Services through Microsoft CRM with a shared data source, so you must manually
reset the data source for each report before you deploy it.
On the Design the Query page, enter the
following SQL statement and click Next.
SELECT FilteredActivityPointer.activitytypecodename, FilteredActivityPointer.
FROM FilteredAccount INNER JOIN
FilteredActivityPointer ON FilteredAccount.accountid = Filt
ORDER BY FilteredActivityPointer.modifiedonutc DESC
You can continue through the Report Wizard to adjust the
report formatting, or just click Finish to accept the
For the report name, type Account Activities,
and then click Finish. You will see the report in Layout
mode. You can adjust the report column widths by dragging the columns to the
left or right. You can also click the Preview tab to see
what your report will look like.
On the File menu, click Save
to save your new report. Now you are ready to add it to Microsoft CRM.
In the Web client, navigate to the Microsoft CRM Reports list
and click New on the grid toolbar.
In the Location field, select your new Account Activities.rdl
and give the report a name. Remember that the report name must be unique.
Click Save and Close, and then run your
new report. Here is the report output if you accept the default layout
formatting for the Adventure Works Cycle sample database.
The default Reporting Services formatting looks pretty bad, so we would never
deploy a report that looked like this to end users. However, we wanted this
example to demonstrate how quickly and easily you can create a custom report
for Microsoft CRM. Our example used the default Reporting Services formatting,
but you would obviously want to edit the report formatting (fonts, colors, and
so on) to match all of the other reports used in Microsoft CRM.
Tip The Microsoft CRM Software Development Kit (SDK) includes a Report
Style Guide document that lists all of the fonts and colors that you should use
to match the formatting of the default Microsoft CRM reports. You could build
your reports by using an existing report as a template, which will save you
from extra formatting steps.
In the example report we just finished, we created a simple
standalone report that didn't use any report parameters. Reporting Services
uses parameters to allow you to dynamically alter the
report query and output based on incoming variables. In addition to the
standard parameter functionality that Reporting Services supports, Microsoft
CRM offers a few additional special report parameters, listed in
Table 7-3: Microsoft
CRM Reporting Parameters
Adds pre-filtering to the report
Add to query expression (Data tab)
Passes any filtered values to a text box in your report
Add to report layout
Tells Microsoft CRM the path to the Web server
Important to set when using drill-through capabilities
Add to report layout
Sets the language of the report
Add to report layout
Defines the attribute to use for custom sorting within the
Add to report layout
Defines the direction of the sort
Add to report layout
Add to report layout
Add to report layout
As you can see from this table, the CRMAF_ parameter is unique
because it's the only one you use in the query of your report. You use the
other parameters in the report layout mostly to help format data. Explaining
how to use the report layout Microsoft CRM report parameters would require
detailed explanations of how to use the Reporting Services Report Designer and
is therefore beyond the scope of this book. However, we'll review using the
CRMAF_ parameter because of its power and ease of use.
Pre-Filters and Contextual Reports
To use the CRMAF_ parameter, you simply need to modify your report query by
pre-pending CRMAF_ to the name of the filtered view your report references. So
instead of using this query syntax:
Select industry, numberofemployees from FilteredAccount
you would use this syntax in your query:
Select CRMAF_FilteredAccount.industry, CRMAF_FilteredAccount.numberofemployees
from FilteredAccount as CRMAF_FilteredAccount
When you include the CRMAF_ parameter in your SQL query, you're telling Microsoft CRM that you want to display the pre-filter option to users before it runs the report. As you learned earlier, the pre-filter option allows your users to modify the filter criteria before they run the report. If you don't include this parameter in your query, Microsoft CRM will skip the pre-filter option and immediately run the report for all of the records in the query.
In addition to displaying the pre-filter option, you also use the CRMAF_ parameter in your queries to create contextual reports that users can run from the entity form or the entity list.
Note When users run a report contextually, Microsoft CRM won't display the
pre-filter criteria to the users, but it will include the pre-filter criteria
as part of the report results. Users can modify the pre-filter criteria by
clicking the Edit Filter button after they run a report. Or they can modify the
default pre-filter criteria for the report as explained later in this chapter.
The criteria to create a contextual report include:
Adding Pre-Filtering to Your Custom Activity Report
Creating a report that queries data from filtered views using
the alias CRMAF_ <filteredentityview> and then
joining your related filtered views (entities) in the report query.
Making sure you include the CRMAF_ alias name on all of the
fields in your query.
Incorporating the filtered entity and the other filtered
entities from your query in the Related Record Types when you upload the report
to Microsoft CRM.
Displaying the report using the Lists and the Forms for
related record types.
Using Reporting Services Manager>
Open the Account Activities report that we created in the
On the Data tab, change your query to add the CRMAF_ <filteredentityview>
parameters as shown.
FROM FilteredAccount AS CRMAF_FilteredAccount INNER JOIN
FilteredActivityPointer AS CRMAF_FilteredActivityPointer ON
accountid = CRMAF_FilteredActivityPointer.regardingobjectid
ORDER BY CRMAF_FilteredActivityPointer.modifiedonutc DESC
Save the report file, and then upload it to Microsoft CRM
using the Reports manager we showed earlier. Make sure that you select the
Related Record Types and the Display In areas so that users can run this report
Now when you run your report from the Reports list, you will
see the pre-filter option.
When you open an Account record, users will see this report
listed under the Use Current Record grouping on the Reports button in the
toolbar. Therefore, when they run this report from the Account form, Microsoft
CRM will run the report contextually for just the Account record that the user
is working with.
Until now, we've discussed only administering reports using
the Microsoft CRM Reports list. However, some of the report administration
functions and tasks require you to access the Reporting Services Report Manager
Web site at
use the Reporting Services Report Manager for the following tasks:
Scheduling Report Execution for Performance
Scheduling report execution for performance and snapshots
Scheduling reports for e-mail delivery
Running complex reports might drastically reduce the performance of your
reporting server. If you install Microsoft CRM and Reporting Services on the
same server, these complex reports might negatively affect performance for all
of your Microsoft CRM users. Therefore, it's ideal to install Reporting
Services on a dedicated computer separate from Microsoft CRM. If that's not
possible, you can use Reporting Services report execution settings to reduce
the impact of report execution on the performance of the server running
Microsoft CRM. This technique allows you to execute a report and cache the
results, providing a performance boost at run time when viewing the report. In
addition to caching report results, this execution setting also lets you take a
report snapshot that freezes a copy of the report results as of a specific time
(useful for quarterly progress reports, monthly quotas, and so on). To
configure report caching and snapshots, you need to browse to the report you
want to modify with the Reporting Service Report Manager (installed by default
at http:///reports), click the Properties tab, and then click the
Execution link in the left column. From this page, you can turn caching off/on,
schedule caching intervals, schedule automatic report snapshots, and set up
report timeouts. Note Just before we sent this book to the publisher, Microsoft
released a utility called the Microsoft CRM 3.0 Report Scheduling Wizard that
allows users to schedule and create report snapshots directly within the
Microsoft CRM user interface (instead of within the Reporting Services Report
Manager). After you download and install this tool, a Schedule Report option
will appear under the More Actions button in the Reports list toolbar. The
wizard walks you through all the steps you need to create, schedule, and even
share report snapshots. Because of its ease of use and power, we recommend that
every Microsoft CRM customer download and use this tool. You can download it
When you configure report caching or snapshots, you must run the report from
the context of a specific user. If you run the report in the context of a user
with higher privileges (such as a system administrator), every person who views
that report would see the same data that the system administrator would see
regardless of their individual business unit and security roles. Consequently,
a lower-level user might see data in the report that he or she would not be
able to see through the Microsoft CRM user interface. Conversely, if you choose
to cache a report or take a snapshot with a user who has lower-level
privileges, a higher-level user might miss data that he or she should be able
to view. Therefore, you must consider carefully which user credentials you want
to specify when you configure report caching and snapshots.
Scheduling Reports for E-Mail Delivery
Reporting Services allows you to schedule reports (hourly, daily, weekly, and
so on) and deliver the report results via e-mail by using a notification list.
You can send the reports to any valid e-mail address in any of the output
formats that Reporting Services supports. As with report caching and snapshots,
when you deliver reports through e-mail, you must run them from the context of
a single user.
Configuring an E-mail Server Running SQL Reporting Services 2000
Caution All e-mail recipients will see identical reports results, so
make sure that you don't accidentally send confidential information to an
We will walk through a simple example of scheduling a report that Reporting
Services will deliver via e-mail. To deliver reports via e-mail, you must first
configure an e-mail server for the server running Reporting Services. If
Microsoft CRM installed Reporting Services for you, the e-mail server value
will be blank, and you'll have to configure it manually.
Scheduling an E-mail Report
Log on to the Reporting Services server.
Click Start, and then click My
Navigate to C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer (assuming a default installation
of Reporting Services).
Open RSReportServer.config in Notepad, and then scroll to the
Under the <Delivery> node, a
child <Extension> node appears with the attribute
Name="Report Server Email".
In the <RSEmailDPConfiguration> node, a variety of
properties appears that can be set to configure your e-mail server. At a
minimum, you must enter valid values for the following nodes:
<SMTPServer> </SMTPServer> Enter a valid SMTP
(e-mail) server either by its DNS name or IP address.
<SMTPServerPort></SMTPServerPort> Enter the port
that the SMTP service uses. Most SMTP servers use port 25.
<From> </From> Enter an e-mail address that
Reporting Services will use as the From address in the e-mail reports.
Save the RSReportServer.config file and log off of the
Open Reporting Services Manager (http://<reportserver>/reportmanager).
Click the <organizationname>_MSCRM
Click the Neglected Leads report.
On the Properties tab, click Data
Sources. To schedule the report, you must specify a user under which
Reporting Services will execute the report. To do this, create a new data
source and store the credentials securely with the report.
Click A custom data source.
In the Connection Type box, select
Microsoft SQL Server.
In the Connection String box, enter
data source=<your sql server>;initial catalog=<organizationname>_MSCRM.
In the Connect Using section, select
Credentials stored securely in the report server,
enter a valid user name and password, and clear the Use as Windows
credentials when connecting to the data source check box.
To test the credentials you just entered, click the
View tab and confirm that the report renders correctly. If it does not,
you must modify the data connection settings until the report renders
On the Subscriptions tab, click the
New Subscription button to begin creating the subscription for this
Change the Delivered By option to
Report Server E-Mail. If this option does not appear, you must properly
configure Reporting Services with an e-mail server (see the preceding
Enter valid e-mail addresses in the To,
Cc, and Bcc boxes. Separate multiple e-mail
addresses with a semicolon.
Enter a subject for the e-mail.
For this example, select Web archive for
the render format (although you can pick different formats), and leave the
Include Report and Include Link options selected.
The Include Report option tells Reporting Services to
include the report as an attachment. The Include Link option
allows for a link back to the report on the server running Reporting Services
in the body of the e-mail. The render formats include the same options as
exporting a report from the report viewer.
To select a schedule, click the Select Schedule
On the schedule page, enter the day, time, and recurrence
that you would like for delivery of this report, and then click OK.
You now have the option to alter the query and report
parameters for this scheduled report. For this example, we will leave
everything as is. Click OK.
Click the Subscriptions tab to see your
new e-mail report.
When the report is sent, you will receive an e-mail resembling the one shown in