Chapter at a Glance
In Chapter 13, and Chapter 14, you learned how to use the chart and dashboard tools to create real-time visualizations to enhance the user experience and allow for more efficient decision making. Although those options are powerful and will meet many of your reporting needs, Microsoft Dynamics CRM also uses Microsoft SQL Server Reporting Services for advanced reporting solutions. Programmers can create advanced reports by working directly within SQL Server Reporting Services, but Microsoft Dynamics CRM also includes a Report Wizard that provides users of all skill levels with a tool to create SQL Server Reporting Services reports. The following table compares the features of charts and dashboards with those of the SQL Server Reporting Services Report Wizard.
Microsoft Dynamics CRM includes 25 standard SQL Server Reporting Services reports in the base product. You can find these reports by navigating to the Workplace area and clicking the Reports link.
You will find that these reports solve some of your reporting needs and will probably serve as a solid starting point for your organization. The reports also give you a high-level understanding of the possibilities that exist with SQL Server Reporting Services. The following table summarizes the reports and their applicability within the Marketing, Sales, Service, and Administrative areas of Microsoft Dynamics CRM.
Report Name | Marketing | Sales | Service | Other |
|---|---|---|---|---|
Account Distribution | X | X | X | |
Account Overview | X | X | X | |
Account Summary | X | X | X | |
Activities | X | |||
Campaign Activity Status | X | |||
Campaign Comparison | X | |||
Campaign Performance | X | |||
Case Summary Table | ||||
Competitor Win Loss | X | |||
Invoice | X | |||
Invoice Status | X | |||
Lead Source Effectiveness | X | X | ||
Neglected Accounts | X | |||
Neglected Cases | X | |||
Neglected Leads | X | |||
Order | X | |||
Products By Account | X | |||
Products By Contact | X | |||
Progress Against Goals | X | |||
Quote | X | |||
Sales History | X | |||
Sales Pipeline | X | |||
Service Activity Volume | X | |||
Top Knowledge Base Articles | X | |||
User Summary | X |
In this chapter, you will learn how to create, modify, and format reports by using the Microsoft Dynamics CRM Report Wizard. You will also learn how to share a report with other users, schedule delivery of a report, and categorize a report.
Important
The images used in this book reflect the default form and field names in Microsoft Dynamics CRM. Because the software offers extensive customization capabilities, it’s possible that some of the record types or fields have been relabeled in your Microsoft Dynamics CRM environment. If you cannot find the forms, fields, or security roles referred to in this book, contact your system administrator for assistance.
Important
You must know the location of your Microsoft Dynamics CRM website to work the exercises in this book. Check with your system administrator to verify the web address if you don’t know it.
The Report Wizard allows you to create sophisticated reports within the Microsoft Dynamics CRM interface by guiding you through a step-by-step process that is easy to understand. It allows you to produce grouped, summary-level data in addition to record-level data. Consider the following scenarios:
You need to create an Opportunity Pipeline report that shows all opportunities by owner and includes the sum of all estimated revenue across those opportunities.
You need to compare the number of accounts assigned to each user to determine account distribution levels.
With the Report Wizard, you can get the aggregated summary numbers for these types of reports.
In this exercise, you will use the Report Wizard to create a report that shows active opportunities by owner.
Note
SET UP Use your own Microsoft Dynamics CRM installation in place of the site shown in this exercise. Use the Windows Internet Explorer web browser to navigate to your Microsoft Dynamics CRM website before beginning this exercise. You need a user account that has privileges to create reports.
In the Workplace area, click Reports.
In the Records group on the ribbon, click the
New button to launch the New
Report form.In the Source section of the form, click the Report Wizard button.
The Report Wizard Get Started page displays.
In the Report Wizard, leave Start a new report selected, and click Next to move on to the Report Properties page.
In the Report name field, enter Active Opportunities by Owner, and in the Report description field, enter List of opportunities grouped by owner.
In the Primary record type field, select Opportunities.
Click Next to move on to the Select Records to Include in the Report page.
In the Report Filtering Criteria section, replace the default Modified On search parameter by choosing Status instead of Modified On in the Select list.
Leave Equals in the operator field, and in the Enter Value field, select Open.
Click Next to move on to the Lay Out Fields page.
In the Column list, select Owner.
In the Summary Type field, select Count, and then click OK to add this grouping to your report.
This summary type will allow you to see how many active opportunities exist for each owner.
Click Click here to add a column. The Add Column dialog box appears.
In the Column field, select Potential Customer. Set the column width to 150 pixels, and click OK to add the column to your report.
To the right of Potential Customer, click in the Click here to add a column field to add another column to your report. Continue this process to add the following fields and related information:
Column
Column Width
Summary Type
Topic
300 pixels
None
Probability
75 pixels
None
Est. Close Date
100 pixels
None
Est. Revenue
100 pixels
Sum
Your additions are reflected on the Lay Out Fields page of the wizard.
In the Common Tasks pane, click Configure Sorting.
In the Sort By field, select Probability. Then select Descending Order.
Click OK, and then click Next to move on to the Format Report page.
Leave Table only selected for your report format, and then click Next.
The Report Summary screen displays your report selections.
Review the report details, and then click Next.
The Report Successfully Created confirmation page appears, indicating that you have successfully created a report.
On the confirmation page, click Finish to exit the Report Wizard.
The Report Wizard closes and you are returned to the New Report form, which automatically updates to reflect the details of your report. To see the results, you will have to run the report.
On the form toolbar, click the Run Report button.Your report displays within SQL Server Reporting Services. The resulting report provides insight into how many open opportunities exist for each owner.
Now that you have seen the power of the Report Wizard, you can use it to create reports that are relevant to your business needs. Business needs change over time, and your reports will need to change accordingly. Additionally, you will often want to make small tweaks to the reports you have already built. For example, you might want to add a column or a grouping level, or you might want to modify the sort order. You can use the same interface in the Report Wizard to modify existing reports without having to start over.
In this exercise, you will modify the Active Opportunities By Owner report you created in the previous exercise. Specifically, you will modify the filter to include only those opportunities with a closing probability greater than 50 and group results by manager in addition to record owner.
Note
SET UP Use your own Microsoft Dynamics CRM installation in place of the site shown in this exercise. Use the Internet Explorer web browser to navigate to your Microsoft Dynamics CRM website, if necessary, before beginning this exercise. You need the Active Opportunities By Owner report you created in the previous exercise, and you need a user account that has privileges to create and update reports.
In the Workplace area, click Reports.
Select Active
Opportunities by Owner without opening it. In the Records group on the ribbon, click the
Edit button.The Report form appears.
In the Report form, click Report Wizard to launch the Report Wizard.
On the Get Started page of the Report Wizard, leave the default selections to work from your existing report so that your changes will overwrite the original settings. Click Next to proceed to the next step.
On the Report Properties page, leave the current settings intact and click Next to proceed to the next step.
The Select Records To Include In The Report page appears, displaying the Status report parameter you configured in the previous exercise.
Add a new row by clicking Select and then choosing Probability.
Select Is Greater Than as the operator.
In the Enter Value field, enter 50.
Click Next to proceed to the next step of the Report Wizard.
This selection refines your results to include only records with a probability value greater than 50.
On the Lay Out Fields page, revise the report format by clicking in the Click here to add a grouping field.
In the Record type field, select Owning User (User).
In the Column field, choose Manager.
In the Summary type field, select Count.
With these selections, you are adding the owner’s manager to the report as an additional grouping level.
Click OK to close the Add Grouping dialog box. The new grouping level is added below the existing Owner grouping level in the report.
In the Common Tasks pane, click the up arrow.
This moves the manager grouping level above the owner, so that the opportunities for each sales owner will be grouped by the sales managers.
Note
Troubleshooting You might see a warning message showing that the table exceeds the width of one printed page. This is due to the total pixel count of all columns in the report. To keep the report under one page wide for printing purposes, make sure that the sum of your column pixels is less than 960.
Click Next to proceed to the next step of the Report Wizard.
On this page and on the Report Summary page that follows, click Next to maintain the current selections.
The Report Successfully Created confirmation page appears, indicating that you have successfully updated the report.
Click Finish to exit the Report Wizard.
The Report Wizard closes and you are returned to the Report form. The changes to your report are automatically saved.
In the form toolbar, click Run Report to view the report with your changes.
The report now includes an additional grouping level that shows total open opportunities at the manager level as well as at the owner level, and only includes those opportunities with a closing probability greater than 50. You can continue to refine your reports at any time with the Report Wizard.
The report you created in the previous exercise will reside in the Reports view of the Workplace area but will only display for you; most other users will not be able to see the report unless they have system administrator or other security rights. By default, Microsoft Dynamics CRM prevents other users from being able to use your report at this point, but it does allow you to share the report with other users as you see fit. This is helpful because:
Several different users might use the Report Wizard to create many reports. If all reports are immediately available to everyone, the number of reports in the list will grow quickly and might lead to confusion.
You might need a report for a very specific reason that does not apply to any other users, and therefore there is no reason to share it.
Although reports are not shared immediately, you can share them with other users in just a few steps.
In this exercise, you will share the report you created in the previous section with a specific user and then make it available to all users in your Microsoft Dynamics CRM environment.
Note
SET UP Use the Internet Explorer web browser to navigate to your Microsoft Dynamics CRM website, if necessary, before beginning this exercise. You need the Active Opportunities By Owner report you created earlier in this chapter, and you need a user account that has a security role with privileges to publish reports.
In the Workplace area, click Reports.
Select Active Opportunities by Owner without opening the report.
In the Collaborate group on the ribbon, click the
Share button.The Who Would You Like To Share The Selected Report With? dialog box appears.
In the Common Tasks pane, click Add User/Team.
The Look Up Records dialog box appears.
Search for system users and select any user. Then add the user to the Selected Records area, and click OK.
The selected user displays in the sharing dialog box, with Read rights assigned by default. You can modify the sharing rights to suit your specific needs.
Click OK to save your sharing settings for the report.
The specified user will now see the report in the Reports view of the Workplace area.
Occasionally you might want to make the report available for every user rather than for specific users. Although you could select all users in the sharing dialog box, users that join your organization at a later time will not automatically have access to the report. In the next steps, you will make a report available to the entire organization so that even users who are added later are still able to access your report.
In the Reports view, select the Active Opportunities by Owner report without opening it, and on the ribbon, click Edit.
The Report form is displayed.
On the form toolbar, click Actions, and then select Make Report Available to Organization.
Note
Troubleshooting If the Make Report Available To Organization option does not appear in the Actions menu, you do not have rights to publish reports. Contact your system administrator about adding the Publish Reports privilege to your security role.
Your report is now available to the entire organization. You can also make the report a personal report again by following steps 7 and 8 above and selecting Revert To Personal Report.
When you run a report by using SQL Server Reporting Services in Microsoft Dynamics CRM, the report runs in real time and reflects the current data in the application. This works well for most of your reporting needs, but it can also pose challenges. For instance:
Users who run the report at different times can communicate conflicting information, which leads to confusion and data integrity concerns.
Real-time reports provide no historical perspective for comparison or trending purposes.
For example, you might want to run a monthly pipeline report to understand how the pipeline looks at the beginning of each month and to compare to previous months.
Microsoft Dynamics CRM provides a Report Scheduling Wizard to address this need. The wizard allows you to generate report snapshots either on demand or at a regularly scheduled time.
In this exercise, you will schedule a report to run once a month at midnight.
Note
SET UP Use the Internet Explorer web browser to navigate to your Microsoft Dynamics CRM website, if necessary, before beginning this exercise. You need the Active Opportunities By Owner report you created earlier in this chapter, and you need a user account that has a security role with privileges to add Reporting Services reports.
In the Workplace area, click Reports.
Select Active Opportunities by Owner without opening the report.
In the Actions group of the ribbon, click Schedule Report.Note
Troubleshooting If the Schedule Report button is not available on the ribbon, you do not have rights to schedule reports. Contact your system administrator about adding the Add Reporting Services Reports privilege to your security role.
The Report Scheduling Wizard appears. Here you can specify when a snapshot should occur.
Select On a Schedule, and then click Next to proceed to the Select Frequency page.
On the Select Frequency page, select Monthly and leave the default settings for the monthly schedule.
The default settings schedule the report for the first day of the month at midnight.
Click Next to proceed to the next step of the Report Scheduling Wizard.
On the Select Start and End Dates page, leave the current date as the default start date, and leave No end date selected.
Click Next to proceed to the next step.
The Define Report Parameters page is displayed, indicating that there are no parameters for the selected report.
Click Next to proceed to the next step of the Report Scheduling Wizard.
The Review Snapshot Definition page is displayed.
Verify that the settings are correct, and then click Create to schedule your report.
A Completing The Report Scheduling Wizard confirmation page displays when the scheduling process is complete.
On the confirmation page, click Finish to exit the Report Scheduling Wizard.
You have successfully scheduled the report. On the first day of the next month, the report snapshot will be taken and will be available for all users who have access to the report. When the report is run, an additional report will appear in the list with a name similar to the original report that includes a timestamp of the report’s run date.
You now know how to create, modify, share, and schedule reports. As you begin creating additional reports, you’ll find proper categorization very helpful for organizing different types of reports into logical groupings. By default, the following categories are available for reports in Microsoft Dynamics CRM:
Administrative Reports
Marketing Reports
Sales Reports
Service Reports
Tip
Users with elevated security rights can modify and add report categories to match their business needs. This option is available on the Reporting tab of the System Settings dialog box, which is accessible in the Settings area. Contact your system administrator if you do not have access to this area.
In addition to providing local groupings for reports, the categorization feature provides other options, as described in the following table.
Categorization Field | Description |
|---|---|
Related Record Types | This specifies the types of records relevant to the report. By default, this is set to the primary record type. In the Active Opportunities By Owner report example in this chapter, the Related Record Type is set to Opportunities. |
Display In | This specifies where the report can be accessed within Microsoft Dynamics CRM. The available options are:
|
Reports can be configured so that users can access them from grid and form toolbars as well as from the Reports view in the Workplace area. The Display In option of each report allows you to designate where the report can be accessed. The Reports Area option is set by default and makes the report available under the Reports list in the Workplace area. The other options are:
Forms For Related Record Types This option is selected by default and allows a report to be run from within a record. For example, the Account Overview report can be run from within an account record.
Lists For Related Record Types This option is selected by default and allows a report to be run from the grid toolbar. Again, the native Account Overview report can be run from account grids. You can select multiple records to include in the report, or run it for a single record.
In this exercise, you will categorize the Active Opportunities By Owner report you created earlier in this chapter.
Note
SET UP Use the Internet Explorer web browser to navigate to your Microsoft Dynamics CRM website, if necessary, before beginning this exercise. You need the Active Opportunities By Owner report you created earlier in this chapter.
Select the Active Opportunities by Owner report without opening it, and on the ribbon, click the Edit button.
The Report form displays.
In the Categorization section, in the Categories field, click the ellipsis button.In the Available Values section, click Sales Reports, and then click the right arrow button to select the value.
Click OK to close the dialog box.
On the Report form, click Save and Close to save the category
selection.In the Reports grid, in the view selector, select Sales Reports.
The Active Opportunities By Owner report now appears in the sales grouping.
The Report Wizard allows you to create SQL Server Reporting Services reports through an easy interface by using a step-by-step process.
You can aggregate data by grouping fields with the Report Wizard. Additionally, you can specify which columns to include in the output of the report.
Several report formatting options are available, including column width definition, column ordering, and sorting.
You can modify Report Wizard reports by using the same wizard you use to create reports.
You can share reports you create with other users or make them available to all users in your organization.
Report scheduling allows you to record point-in-time snapshots of a report automatically by specifying a single or recurring time. You can also record a report snapshot on demand.
Microsoft Dynamics CRM provides several report categorization options to enable you to specify how a report is grouped and where the report is available to be run within the application.