Help your team make effective business decisions based on data from Microsoft Dynamics CRM 3.0. With an understanding of the business reasons for the report and how a database works, you can create a Microsoft SQL Server Reporting Services report. After you follow the steps in this tutorial and install the required tools, it only takes a few minutes to write a useful report and to add it to Microsoft Dynamics CRM. You need no prior knowledge of the required tools.
If you choose, you can use the tutorial in conjunction with three reporting videos, which are available on the MSDN Code Gallery.
•
Chapter 1, “Using Prefiltering and Context Sensitive Reports,” a 2-minute introduction to features of Microsoft Dynamics CRM reports that you must understand before you create a report.
•
Chapter 2, “Microsoft Dynamics CRM Report Creation,” a 6-minute video that corresponds with steps 1-3 in this tutorial.
•
Chapter 3, “Publishing a Report,” a 5-minute video that corresponds with steps 4-7 in this tutorial.
To write a report and add it to Microsoft Dynamics CRM, you must have either:
•
Microsoft SQL Server 2003 Reporting Services Report Designer, and Microsoft Visual Studio 2003, which is included with any product in the Visual Studio .NET development system
•
Microsoft SQL Server 2005 Reporting Services Report Designer, and Microsoft Visual Studio 2005 or 2005 shell
You must also have the Manage Reports security role in Microsoft Dynamics CRM and a computer that is on the same domain as your Microsoft Dynamics CRM server.
For information on installing Report Designer, see Chapter 24 of the Microsoft Dynamics CRM 3.0 Implementation Guide.
When you select the data source for the report, use the filtered views in the Microsoft Dynamics CRM database, rather than the database tables. Filtered views provide security and efficiency. Each user’s Microsoft Dynamics CRM security roles determine which records are returned by a query to a filtered view. So reports built on filtered views show users only the records that they have permission to view.
Microsoft Dynamics CRM has one filtered view for each Microsoft Dynamics CRM entity. Because each filtered view includes joins on several base SQL tables, creating the dataset for the report simplifies the SQL query.
For more information about filtered views, see the Filtered Views section of the Report Writer’s Guide.
When you create a report, you can configure it to have a default filter that each user can edit before running the report. This is called making the report prefilterable. There are two advantages to making a report prefilterable:
•
The default filter prevents users from inadvertently running the report on all records. By default, this filter selects active records that were modified in the last 30 days. Because you have Report Manager privilege, you can define specific default criteria for the default filter for each report.
•
Users can edit the filter to find exactly the data they need on the first try.
For Microsoft Dynamics CRM to make a report prefilterable, you must specify the CRMAF_ prefix in your SQL query when you create your report in Report Designer. When you add this prefix to at least one filtered view in the query, Microsoft Dynamics CRM adds a default filter to the report. For each filtered view that has this prefix in the query, users can edit filter criteria. For example, if your query includes the FilteredAccount and FilteredContact views, and your SQL query uses CRMAF_FilteredAccount and FilteredContact, the report will have a default filter. Users will be able to edit criteria related to accounts, but will not be able to edit criteria related to contacts.
For more information about prefiltering reports, see the Using Filters in a Report section of the Report Writer’s Guide.
Users can run reports from three areas in Microsoft Dynamics CRM:
•
From the Reports area, as part of the list of all reports. From this area, a user must use the filter criteria to select records for the report.
•
From an entity list page, such as the Contacts page, or from any Advanced Find view of the Contacts entity. Users have three choices for selecting which records the report includes: all applicable records, selected records, or all records in the current view. After the report is run, the user can change the filter criteria.
•
From an individual record. This runs the report on the current record.
You determine where the report is available by editing the report properties after you add the report to Microsoft Dynamics CRM. Because most users prefer using reports in the context of other work they are doing, make reports context-sensitive to reduce the number of clicks required to filter and run the report.
For more information about publishing the report to Microsoft Dynamics CRM, see the Publishing to Microsoft Dynamics CRM section of the Report Writer’s Guide.
Tutorial: Create a new, context-sensitive, prefilterable report
This tutorial includes all the steps necessary to create an Account List Demo report and add it to Microsoft Dynamics CRM. To do this tutorial, you must have the Visual Studio 2003 shell and Microsoft SQL Server 2000 Reporting Services Report Designer installed.
Step 1: Create a report project
1.
On the Programs menu, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003.
2.
On the Start page, click New Project.
3.
In the Project Types list, select Business Intelligence Projects, in the Templates section, click Report Project, type a name for the folder in which you will store your Microsoft Dynamics CRM reports, such as CRM Report Demo, and then click OK.
4.
In the Solution Explorer pane, right-click Reports, point to Add, and then click Add New Item.
5.
Type a name for the new report, such as Account List Demo in the Templates pane, select Report, and then click Open.
Step 2: Create the dataset
1.
In the Dataset box, select <New Dataset…>.
2.
In Select or enter a server name, type the name of the Microsoft SQL Server that stores the Microsoft Dynamics CRM database. In Select the database on the server, select the database named Organization_MSCRM. Click Test Connection, and then click OK twice.
3.
Click the ellipses (…) next to the dataset name to open the Dataset dialog box. Rename the dataset from Organization_MSCRM to something specific for the report, such as DSAccountListDemo, and then click OK. Because each report needs a unique dataset, having a descriptive name makes it easier when you start adding more reports.
4.
On the toolbar, click the Generic Query Designer icon. This displays the Generic Query Designer view, which displays all fields and tables in the report and lets you drag fields to create the SQL statement.
5.
On the toolbar, click the Add Table icon. This is where Tip 1 applies. To use filtered views, rather than tables, click the Views tab, select FilteredAccount, click Add, and then click Close. This creates the SQL FROM statement.
6.
In the FilteredAccount field list, select address1_stateorprovince, creditlimit, name, and owneridname. This creates the SQL SELECT statement.
7.
On the toolbar, click the Refresh Fields icon. This populates the fields in the Fields pane.
Step 3: Lay out the report
1.
Click the Layout tab.
2.
If the Toolbox isn’t visible, on the View menu, select Toolbox. In the Toolbox, point to Table, and then drag it onto the layout grid in the Body area. Close the Toolbox.
3.
Drag name from the Fields list into the Detail section of the first column.
4.
Drag address1_stateorprovince from the Fields list into the Detail section of the second column.
5.
Right-click the top row of the third column in the table, and then click Insert Column to the Left.
6.
Drag owneridname from the Fields list into the Detail section of the third column.
7.
Drag creditlimit from the Fields list into the Detail section of the fourth column.
8.
Drag creditlimit from the Fields list into the Table Footer section of the fourth column.
9.
For each value in the Table Header row, select the attribute name, and then replace it with a friendly name, such as Account Name, State, Owner, and Credit Limit.
10.
On the File menu, click Save All to save all the files associated with this project.
11.
Click the Preview tab. Your first draft of your new report is done. Leave Visual Studio .NET 2003 open because you will use it later in the tutorial to make the report prefilterable and modify its formatting.
Step 4: Add the report to Microsoft Dynamics CRM
1.
Open Microsoft Dynamics CRM, and in theWorkplace pane, under My Work, click Reports.
2.
Click New, click Browse, and then browse to the CRM Reports project you created in Visual Studio .NET 2003. By default, Visual Studio creates projects in the Visual Studio Projects subfolder of My Documents. Select the Account List Demo.rdl file.
3.
Click Save and Close.
Your new report appears on the list of reports. To run it, click the report name. You’ll notice that it runs on all the accounts on your system, and that there is no Edit Filter button. Because you have not yet specified where Microsoft Dynamics CRM should display the report, it is only available from the Reports area, and not from the Accounts list or an account form. The next sections of this tutorial help you implement Tips 2 and 3 and make minor formatting changes to your report.
Step 5: Set up the report to be prefilterable
1.
In your report project in Visual Studio .NET 2003, click the Data tab. In the Sort Type column for the name field, select Ascending.
2.
Click the Layout tab, select the Table Header row, and on the Format menu, point to Font, and then click Bold.
3.
Click the Data tab, and then look at the FROM statement in the SQL section. Right-click the title bar of the FilteredAccount table, and then click Properties.
4.
In the Alias box, type CRMAF_FilteredAccount, and then click Close. Notice that the FROM statement changed to include the aliased name. The prefix CRMAF_ tells Microsoft Dynamics CRM to treat the report as prefilterable.
5.
On the File menu, click Save All.
Step 6: Update the report in Microsoft Dynamics CRM and add it to the Account list page
1.
In the Microsoft Dynamics CRM Reports page, click the Account List Demo report, and then click Edit Report on the More Actions menu.
2.
Click Browse, and then browse to the .rdl file for your report.
3.
Click Open.
4.
In the Categorization section, next to Categories, click the ellipsis (…). Select Sales Reports, click the Select icon (>>), and then click OK.
5.
In the Categorization section, next to Related Record Types, click the ellipsis (…). Select Accounts, click the Select icon (>>), and then click OK.
6.
In the Categorization section, next to Display In, click the ellipsis (…). Select Lists for related record types, click the Select Icon (>>), and then click OK. This adds the report to the list pages for the entity you defined in step 5.
7.
Click Save and Close.
Step 7: Preview your new report
1.
In the Microsoft Dynamics CRM Reports area, double-click the report name. You’ll see that it now has a default filter. After you run the report, it also has an Edit Filter button.
2.
To temporarily modify the filter for this preview, click Edit Filter, and then modify the filter criteria.
3.
To modify the default filter for the report, on the More Actions menu, click Edit Default Filter, and then modify the filter criteria.
4.
In the Workplace pane, in the Customers section, click Accounts.
5.
Click the Report icon, and then select Account List Demo. Choose the records on which to run the report, and then preview it.
You are now ready to create your own prefilterable, context-sensitive reports. If you want to create more complex Microsoft Dynamics CRM reports, see the Report Writers Guide section of the Microsoft Dynamics CRM SDK. It includes information on passing parameters to reports, working with complex queries, formatting content to look like other Microsoft Dynamics CRM reports, report navigation, and more.
