13. Reports

Reporting covers several different methods of extracting and presenting information including:

· The use of Views to export (and re-import) data from CRM.

· Creating standalone mail merge files.

· Integrated reports using the report wizard for SQL Reporting Services.

· Programmer created reports integrated with CRM.

· Reporting and data extraction from external programs.

You should note that the CRM database is a normal SQL Server database and can be accessed directly using a variety of standard software including Microsoft Access and Office, Crystal reports, and many other report writers and database access software programs.

Note: Remember to log in to the database using your windows identity so that the CRM security settings are preserved and to use only the views prefixed with the word filtered.

13.1. Exporting CRM data to Excel

All Views can export data to Excel easily and a custom view can be created (either individually or shared with the business unit or organisation) to contain any fields from related (many to one) entities.

Select the Export to Excel button from the View Toolbar (control-click to make a selection of records if required) and then chose from the following options:

· Static Worksheet. Simply exports the current view to Excel.

· Dynamic Pivot Table. Allows you to make a selection of columns (so you do not have to create a view with all required fields) and export to a dynamic pivot table.

· Dynamic Worksheet. Allows you to select the columns required and export to a dynamic Excel file.

Note: Dynamic Excel files extract data from the database each time they are refreshed. The CRM database will use the Windows security of the currently logged on user so the data returned may be different for different users.

13.2. Re-importing Updated Excel Data

A very useful feature here is to tick the checkbox to Make the data available for re-importing. This adds some fields required to track where the data came from and allows you to email the spreadsheet to a colleague or external contact for updating. You can then re-import the data using the Tools-Import Data option whereupon the changed fields will be updated.

Note: This is perhaps the single most important feature to allow non-programmers to integrate CRM with external systems and works with custom entities and allows you both to modify and add records.

13.3. Creating Mail Merge files

Each View menu has a Mail Merge option for mail merging to Outlook 2007 using a wizard and a blank template or templates saved within CRM (see the marketing chapter). You can also mail merge from the Outlook client or create an exported Excel file (see above) with the required fields and save as a CSV file for integration with any mail merge program (or for sending to an external fulfilment house). See the marketing chapter for further details.

13.4. Report Viewer

Reports can be integrated and run from several different places within the CRM interface:

· Reports can be called from the Workplace-Reports area with default or custom selection criteria.

· Reports can be run from the current View which allows for a single record or all records in the current view to be selected. The user can also control-click on a selection of required records to be included in the report.

· Reports can be called when looking at a form in which case just a single record is selected for the report.

Reports appear in a report viewer which may allow items to be sorted by clicking on the arrows to the right of the column heading (some reports also have drill down facilities and hyperlinks). The User can also print the report or choose a format for exporting to a file.

Reports can be exported into several different formats:

· XML. XML is a good format for exporting to other applications (particularly good for programmatic access).

· CSV. CSV is a general format readable by the widest range of software programs.

· TIFF. This is an old graphic format and not recommended.

· Adobe PDF. PDF documents are the best format for distributing reports widely and can be printed from any machine.

· Excel. The report formatting is preserved as much as possible and this is a good format for distributing to other users who need to cut and paste the data.

· Web Archive. A proprietary Microsoft format easily publishable as web pages accessible from a browser.

13.5. Report Wizard

The Report Wizard allows for the creation of simple reports (implemented in SQL Reporting Services) that are automatically integrated into CRM.

You can start the Report Wizard by creating a new report from the Workplace-Reports work area and you can modify an existing report or start from scratch. You need to specify a report name and chose the base entity (or record type) for the report. You can also choose a related entity here to show related records for the base entity (for example activities against a contact).

The next page allows you to specify report filtering criteria. Perhaps you can clear these and specify them with the reports form at the end of the process.

Now the Lay Out Fields form appears allowing you to add fields onto the report and specify the sorting criteria. To add fields click on the Click here to add a column button and select the required field. You can select from the base entity and all related entities as well (just many to one relationships) and can use the Change Properties button to change the width of the column.

Click on Configure Sorting to specify the default sort selection although you can also click on the column heading in the report viewer to sort the report.

The report form appears once the report is completed and allows you to fine tune the integration with CRM. The categories section simply specifies which of the views within the Workspace-Reports work area present the report.

Y The more important integration feature is the Display In option which provides for the three types of integration mentioned earlier:

· Forms. This allows a report to run when viewing a single entity occurrence.

· Lists. Allows for a selection of records from the current view to be included in the report.

· Reports. Adds the report to the Workplace-Reports work area according to the categories specified.

You can share your report with other users or make it available to the whole organisation from the actions menu on this form.

13.6. Running Reports

Reports can be run by pressing the report button (the pie chart) available, where specified, on a form or a view.

Note: Running a report from a View allows individual records to be selected with control-click.

You have much more control over reports when running from the Workspace-Reports area. You can run or edit the report or run the report wizard again to change the report specification. The default report filter allows you to specify the filter selection on your report to produce summaries according to a date range for example, or include only items created within the last month.

Quite complex report selection criteria can be built including related entities and the filter criteria can be overwritten by the end user when running the report.

13.7. Scheduling Reports

Report can be scheduled to run at defined points in time either to reflect a snapshot at a particular time or to save on resources where many different people want to view the same report.

13.8. SQL Server Reporting Services

Microsoft CRM reports are built on top of a software technology known as SQL Server Reporting services. Previous versions of CRM required technical expertise in this technology in order to build even simple reports which can now be built using the Report Wizard.

There are limitations to the complexity and power of reports created with the Report Wizard and so all reports can be downloaded and worked on by a programmer and still remain integrated with CRM. More complex reports may need to be created from scratch by the programmer.

Note: Existing reports can be downloaded from the CRM environment (select a report in with Workplace-Reports area and use the action menu) and modified by a programmer using the report designer in Visual Studio 2005.