Are you neglecting any customers? Run a report to find out
Published: May 19, 2006
To build lasting relationships, you must check in with leads, opportunities, and customers regularly. You can use the Neglected Accounts, Neglected Leads, and Neglected Cases default reports in Microsoft Dynamics CRM 3.0 to identify contacts, or you can create your own report using Microsoft Office Excel 2003.
Although Microsoft CRM doesn't include a default report that shows neglected opportunities, you can create an exception report in Excel to identify opportunities that are not associated with any activities. An exception report identifies all records from one record type that don't have a matching value in a related record type. This article discusses how to use the three default reports and create an exception report.
Identify neglected accounts, leads, and cases using default reports
Default reports for viewing neglected accounts, leads, and cases all work basically the same. You specify a parameter to define how long a record has been neglected and how to group the data. For Neglected Accounts, you can also select whether to include subcontacts and subaccounts.
To run the Neglected Accounts report
1.
In Workplace, under My Work, click Reports, and then double-click Neglected Accounts. The default filter appears. You can modify the filter as needed.
2.
Click Run Report.
3.
On the next screen, select the number of days that you define as neglected, select the grouping, and then click View Report. You’ll see a chart grouped the way you specified.
Identify neglected opportunities by writing your own exception report in Excel
These are the general steps that you follow to write an exception report that shows opportunities without associated activities:
•
Create a data source from Excel that points to the Microsoft CRM database.
•
Use the Microsoft Query Wizard, a component of Excel, to specify the filtered views to include. You’ll use data from the FilteredOpportunity and FilteredActivityPointer filtered views. The FilteredOpportunity view shows all data from Opportunity records. The FilteredActivityPointer view shows general information about all activities. For more information about how activities are stored in the Microsoft CRM database, see “Tailor activities to meet your user’s tasks” in Related Links.
•
Use the Query Wizard to specify the relationship between the two filtered views. In this step, you specify that you only want to see opportunities that do not have associated activities.
After you have your report in Excel, you can format it and add it to Microsoft CRM so that other users can use it.
To create the data source
1.
In an empty worksheet in Excel, on the Data menu, point to Import External Data, and then click New Database Query.
2.
Click <New Data Source>, and then click OK.
3.
In the Create New Data Source dialog box, type a name for the data source, and then in the driver list, select SQL Server.
4.
Click Connect.
5.
In the SQL Server Login dialog box, in the Server box, type the name of the computer that is running Microsoft SQL Server where the Microsoft CRM database is stored, and then click Options.
6.
In the Database box, select Organization_MSCRM, and then click OK three times.
To select the filtered views to include
1.
Open the Query Wizard.
2.
In the Query Wizard Available tables and columns list, expand FilteredOpportunity view, and then select the name field. Because the name field is a mandatory field in the Opportunity area, there is data for every record. You can select the other fields that appear in your report in a later step.
3.
Click Next three times. In the Query Wizard – Finish dialog box, select View data or edit query in Microsoft Query, and then click Finish. Microsoft Query opens.
4.
On the Table menu, click Add Tables, click FilteredActivityPointer, click Add, and then click Close.
To define the connection between the filtered views
1.
Adjust the width of the FilteredOpportunity view so that the field names are visible, and adjust the vertical scroll bar so that the opportunityid field is visible.
2.
Adjust the width of the FilteredActivityPointer view so that the field names are visible, and adjust the vertical scroll bar so that the regardingobjectid field is visible. The regardingobjectid field stores the identification information for the record to which the activity relates. For activities regarding opportunities, the data in this field is an opportunityid from the Opportunity record type.
3.
Drag opportunityid to regardingobjectid. Microsoft Query draws a line between the two fields to indicate that it has created a database join of the two filtered views based on data in these fields.
4.
Double-click the line between the two fields, and then select 2: ALL values from ‘FilteredOpportunity’ and ONLY records from ‘FilteredActivityPointer’ where FilteredOpportunity.opportunityid=. Click Add, and then click Close.
You have created a left outer join. For more information about joins, see “Retrieve related records from multiple tables with joins” in Microsoft Query online Help. In steps 5-7, you use the left outer join to create a subtract join, which retrieves records that two tables don’t have in common.
5.
Drag the subject field from FilteredActivityPointer to the column section of the query. Before you can turn the join into a subtract join, a required field from FilteredActivityPointer must be included in the displayed columns.
6.
On the View menu, click Criteria.
7.
In the first criteria column, expand the width to about three inches. Then, in the first Criteria Field field, select the FilteredActivityPointer.subject field, and in the first Value field, type Is Null. This specifies that opportunities that have an associated activity with a subject are not included.
8.
Drag any columns that you want in your report, such as owneridname and createdonutc, from the FilteredOpportunity view to the columns section, and delete the subject column because you no longer need it.
9.
To see the neglected opportunities, on the Records menu, click Query Now.
To view the data in Excel
1.
On the File menu, click Return Data to Microsoft Excel, and then click OK.
2.
To save your custom exception report, on the File menu in Excel, click Save As, and then specify a file name.
3.
To update the data from Microsoft CRM, on the Data menu, click Refresh Data.
4.
If you prefer seeing your data in a PivotTable, on the Data menu, click PivotTable and PivotChart, and use the Query Wizard to create your PivotTable or PivotChart.
You can add your custom report to Microsoft CRM so that it is available to all users from the Opportunities area. Because you used filtered views when you selected the data to display, users see only opportunities that they have permission to view. To add the report for all users, you must have a security role with the Manage Reports privilege, such as the System Administrator or System Customizer security role.
To add the report to Microsoft CRM
1.
In Workplace, under My Work, click Reports, and then click New.
2.
Click Browse, locate the file that you saved, and then click Open.
3.
In Related Record Types, select Opportunities, and then click OK.
4.
In Display In, select Lists for related record types. Because you selected Opportunities in the Related Record Types, this report appears in the Report list available on the Opportunities page.
5.
Click Save and Close.
6.
To see how the report appears in Microsoft CRM, on the Go To menu, point to Sales, click Opportunities, and then click the Reports icon.
Having identified neglected leads, opportunities, and customers, you can nurture those relationships. By learning to combine data from two record types in Excel, you’ve increased your reporting possibilities, also.
