Are you neglecting any customers? Run a report to find out

Use the Neglected Accounts, Neglected Leads, and Neglected Cases default reports in Microsoft Dynamics CRM Online to identify contacts, or you can create your own report using Microsoft Office Excel 2003.

Although Microsoft Dynamics CRM Online 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.

On This Page Identify neglected accounts, leads, and cases using default reports Identify neglected opportunities by writing your own exception report in Excel Add your custom Excel report to Microsoft Dynamics CRM Online

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 the Neglected Accounts report, you can also select whether to include subcontacts and subaccounts.

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.
4. Click an area of the chart to see the details.

Top

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 Dynamics CRM Online 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 Dynamics CRM Online database, see “Tailor activities to match your employees' 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 Dynamics CRM Online so that other people can use it.

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. In the Choose Data Source dialog box, make sure the Use the Query Wizard to create/edit queries check box is selected.
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 Dynamics CRM Online database is stored, and then click Options.
6. In the Database box, select Organization: MSCRM, and then click OK three times.

Select the filtered views to include

1. The Query Wizard opens automatically. If not, open it.
2. In the Available tables and columns list, expand FilteredOpportunity, and then double-click the name field to move it to the Columns in your query box. Because the name field is a mandatory field in the Opportunities 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.

Define the connection between the filtered views

1. Adjust the width of the FilteredOpportunity view so that the field names are visible, and scroll down until the opportunityid field is visible.
2. Adjust the width of the FilteredActivityPointer view so that the field names are visible, and scroll down until 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. The Query Wizard 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 . 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 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, select the FilteredActivityPointer: Subject field, and in the first Value field, type "Is Null", without the quotation marks. This specifies that opportunities that have an associated activity with a subject are not included.
8. Drag any columns from the FilteredOpportunity view that you want in your report, such as owneridname and createdonutc, 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.

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 Dynamics CRM Online, 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 Report, and use the PivotTable and PivotChart Wizard to create your PivotTable or PivotChart.

Top

Add your custom Excel report to Microsoft Dynamics CRM Online

You can add your custom report to Microsoft Dynamics CRM Online so that it is available to everyone from the Opportunities area. Because you used filtered views when you selected the data to display, people see only opportunities that they have permission to view. To add the report for everyone, you must have a security role with the Manage Reports privilege, such as the System Administrator or System Customizer security role.

Add the report to Microsoft Dynamics CRM Online

1. In Workplace, under My Work, click Reports, and then click New.
2. From the Report Type list, select Existing File.
3. Under File Location, click Browse, locate the file that you saved, and then click Open.
4. In Related Record Types, click Select or change the values for this field, and then, on the Select Values dialog box, double-click Opportunities, and then click OK.
5. In Display In, click Select or change the values for this field, and then, in the Select Values dialog box, double-click Lists for related records types, and then click OK. Because you selected Opportunities in the Related Record Types, this report also appears in the Reports list available in the Opportunities area.
6. Click Save and Close.
7. To see how the report appears in Microsoft Dynamics CRM Online, on the Go To menu, point to Sales, click Opportunities, click the Reports icon, and then select the report you just created.

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.

Top

Related Links

Are your customers buying?

Tailor activities to match your employees' tasks