SQL Server Reporting Services | Working with Microsoft Dynamics(TM) CRM 3.0

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 additional information:

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

One of the most important concepts about Reporting Services and Microsoft CRM is that the reports also use integrated Windows authentication and filtered views to determine which data to display in the report to the user.

Architecture

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.

image from book
Figure 7-5: Reporting Services architecture

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.

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 extensibility options.

Licensing and Installation

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.

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 Reporting Services.

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 can download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=&DisplayLang=en.

Reporting Services Reports in the Microsoft CRM User Interface

You access Reporting Services reports from the Microsoft CRM user interface by using one of three methods:

Reports list
Entity list
Entity form

Reports List

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.

image from book
Figure 7-6: Reports list in the My Work group

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".

Entity List

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.

image from book
Figure 7-7: Accessing reports from an entity's grid toolbar

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:

All applicable records
The selected records (10 maximum)
All records on all pages in the current view

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.

Entity Form

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).

image from book
Figure 7-8: Accessing reports from the entity form

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.

image from book
Figure 7-9: Sample 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.

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.

Running a Reporting Services Report

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 Services reports:

Report pre-filtering
Results navigation
Export options

You'll need to understand these parts of the report output to properly administer and manage your reports.

Report Pre-Filtering

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 report.

image from book
Figure 7-10: Report filtering criteria for pre-filtering report results

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 Reporting Services.

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.

image from book
Figure 7-11: Modifying the default filters, using the same interface as the Advanced Find feature

Just as with the Advanced Find feature, you can create highly complex report filters to search for the exact type of results that interest you.

Results Navigation

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 report.

image from book
Figure 7-12: Reporting Services Account Distribution report output

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.

Export Options

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 7-13.

image from book
Figure 7-13: Exporting a report

You can export the report in any of the formats described in the following sections.

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 Components.
Requires Office Web Components version 10 (Office XP) on the client computer.
This export option was deprecated from SQL Server Reporting Services 2005.

Excel

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.

Web Archive

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.

Acrobat (PDF) File

Saves the report as a .pdf file that can be opened with Adobe Acrobat Reader.
Optimal choice for paginated reports, reports that will be printed, or reports that will be delivered to a broad range of client machines.

TIFF File

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)

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 analysis.

XML File with Report Data

Saves the report as raw.
Typically used when programmatically integrating reporting data with other applications.

Authoring Reporting Services Reports

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 custom entities.

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.

Report Authoring Tools

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.

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 your computer.

Installing the Report Designer Add-In for Visual Studio .NET

Navigate to the SRS folder of the Microsoft CRM installation disc 1 on your client computer.
Double-click Setup.exe and follow the installation wizard.
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.

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.

For additional resources regarding developing reports in Reporting Services, you can install the Reporting Services Books Online.

Editing a Reporting Services Report

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.

image from book
Figure 7-14: Modified Account Overview report

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

Click the Reports subarea in the Workplace area.
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 Files.
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 following error.
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:
```
             facct.numberofemployees, 
```
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   SELECT                facct.numberofemployees,                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 message.
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 Ownership.
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 the following:
1. In the Name box, type numberofemployees.
2. 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.
3. Click OK.
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 window.
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 list.
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.

Note
Do not select the file you downloaded to your desktop. You must select the updated .rdl file from the directory in which Visual Studio stores your project files.
Run the Account Overview report, and you'll see No. of Employees in the Basic Profile section of the report.

As you can see, even though the default Microsoft CRM reports are complex, an inexperienced report author can make simple modifications to add custom attributes, make minor formatting changes, and so on. You can imagine how to carry this same concept through to adding additional fields to a report or modifying where fields appear in the report layout.

Creating a New Reporting Services 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.

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.

Creating a New Report

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 source:
1. In the Name box, type CRM.
2. In the Type list, select Microsoft SQL Server.
3. To enter the Connection String, click the Edit button, which opens the Data Link Properties dialog box.
  1. On the Connection tab, enter or select the name of the computer running SQL Server on which you installed Microsoft CRM.
  2. Select the Use Windows NT Integrated security option button.
  3. Select your database (<organizationname>_MSCRM).
  4. Click OK.
4. 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.
5. Click Next.

On the Design the Query page, enter the following SQL statement and click Next.

 SELECT     FilteredActivityPointer.activitytypecodename, FilteredActivityPointer. subject, FilteredActivityPointer.modifiedonutc,                       FilteredActivityPointer.modifiedbyname, FilteredActivityPoi nter.statecodename, FilteredActivityPointer.statuscodename,                       FilteredActivityPointer.owneridname, FilteredAccount.name FROM         FilteredAccount INNER JOIN                       FilteredActivityPointer ON FilteredAccount.accountid = Filt eredActivityPointer.regardingobjectid ORDER BY FilteredActivityPointer.modifiedonutc DESC

You can continue through the Report Wizard to adjust the report formatting, or just click Finish to accept the default formatting.
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.

Reporting Parameters

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.

Table 7-3: Microsoft CRM Reporting Parameters
Parameter	Description	Usage
CRMAF_<filteredentityview>	Adds pre-filtering to the report	Add to query expression (Data tab)
CRM_FilterText	Passes any filtered values to a text box in your report	Add to report layout
CRM_URL	Tells Microsoft CRM the path to the Web server Important to set when using drill-through capabilities	Add to report layout
CRM_Locale	Sets the language of the report	Add to report layout
CRM_SortField	Defines the attribute to use for custom sorting within the report	Add to report layout
CRM_SortDirection	Defines the direction of the sort	Add to report layout
CRM_FormatDate	Formats date	Add to report layout
CRM_FormatTime	Formats time	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_<filteredentityview> 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:

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.

The following procedure shows the steps for creating a custom report that uses the CRMAF_ parameter to create contextual reports and reports that use the pre-filter.

Adding Pre-Filtering to Your Custom Activity Report

Open the Account Activities report that we created in the previous example.

On the Data tab, change your query to add the CRMAF_ <filteredentityview> parameters as shown.

 SELECT     CRMAF_FilteredActivityPointer.activitytypecodename, CRMAF_FilteredActivityPointer.subject, CRMAF_FilteredActivityPointer.modifiedonutc, CRMAF_FilteredActivityPointer.modifiedbyname, CRMAF_FilteredActivityPointer.statecodename, CRMAF_FilteredActivityPointer.statuscodename, CRMAF_FilteredActivityPointer.owner idname, CRMAF_FilteredAccount.name 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 contextually.
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.

Note

If your custom report appears under the Run Report grouping, you did not configure the report or the query to run correctly contextually. Double check your query and report configuration.

By default, Microsoft CRM creates a pre-filter of Modified in the Last 30 Days for each entity in your report with the CRMAF_prefix. We'll explain later in this chapter how you can modify the default pre-filter options to include additional variables and change the default values.

Best Practices

Using contextual reports will save your users time and provide powerful reports and analysis options as they work with various records in Microsoft CRM. By using the CRMAF_ parameter, you can easily create custom reports to take advantage of this feature. Therefore, as a best practice, you should try to make your custom reports available to run contextually from their related entities.

Using Reporting Services Manager

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 /reportmanager">http://<reportserver>/reportmanager. You use the Reporting Services Report Manager for the following tasks:

Scheduling report execution for performance and snapshots
Scheduling reports for e-mail delivery

Scheduling Report Execution for Performance

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://<crmserver>/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 here: http://www.microsoft.com/downloads/details.aspx?FamilyID=&DisplayLang=en.

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.

Caution

All e-mail recipients will see identical reports results, so make sure that you don't accidentally send confidential information to an inappropriate user.

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.

Configuring an E-mail Server Running SQL Reporting Services 2000

Log on to the Reporting Services server.
Click Start, and then click My Computer.
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 <Extensions> node.
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:
1. <SMTPServer> </SMTPServer> Enter a valid SMTP (e-mail) server either by its DNS name or IP address.
2. <SMTPServerPort></SMTPServerPort> Enter the port that the SMTP service uses. Most SMTP servers use port 25.
3. <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 server.

Now that you have configured the e-mail server for the Reporting Services server, you can schedule a report for e-mail delivery. Let's walk through an example of configuring the Neglected Leads report for scheduled e-mail delivery.

More Info

Refer to the Reporting Services Help or the following article for more information about configuration of e-mail services: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp

Scheduling an E-mail Report

Open Reporting Services Manager (http://<reportserver>/reportmanager).
Click the <organizationname>_MSCRM folder.
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.
1. In the Connection Type box, select Microsoft SQL Server.
2. In the Connection String box, enter data source=<your sql server>;initial catalog=<organizationname>_MSCRM.
3. 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.
Click Apply.
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 correctly.
On the Subscriptions tab, click the New Subscription button to begin creating the subscription for this report.

Note
You can create subscriptions only for reports in which the data source is set to use stored credentials or no credentials.
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 procedure).
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.

Tip

@ReportName and @ExecutionTime are special tokens that Reporting Services will replace with the report name and the time the report was generated before sending the e-mail. We recommend that you leave these tokens in the Subject field.

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 button.
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 Figure 7-15.

image from book
Figure 7-15: E-mailed report

Note

The Web archive option for rendering will display the report within the context of the e-mail for HTML-capable browsers. Other rendering options, such as Acrobat (PDF) and Excel, will arrive as e-mail attachments.