Microsoft Dynamics CRM 2013 is seamlessly integrated with Microsoft SQL Server Reporting Services which enables the platform to host complex business reports based on data residing in CRM. Two types of SQL Server Reporting Services (SSRS) reports can be generated in Dynamics CRM.
- Report Wizard Reports
- Custom Reports
Report Wizard Reports
These reports are comparatively less complex reports and can be created by using the Report Wizard within the Dynamics CRM web application. This functionality enables Business Analysts and Business Users to create reports quickly without a developer’s help. The Report Wizard can summarize and display data from two types of records at once. Some capabilities of the report wizard’s reports are listed beneath.
The report can have tables and charts with a clickable reference to the specific CRM record
Report data can be grouped by any field and summarized by aggregations such as sum, average, minimum, maximum and percentages
Report wizard enables the users to configure a filtering criteria using logical operators such as ‘AND’ and ‘OR’
Report Wizard enables the users to configure a filtering criteria using
Data comparison operators such as ‘Equal’, ‘Does Not Equal’, ‘Is Greater Than’, ‘Is Less Than’, ‘Contains Data’ and ‘Does Not Contain Data’
Date comparison operators such as ‘On’, ‘On or Before’, ‘On or After’, ‘Next X Days’, ‘Last X Days’, ‘Today’ and so on
Advanced users such as Business Analysts can write comparatively complex reports utilizing logical operators, comparison operators and grouping options
Report creation is controlled by Dynamics CRM’s Security Model. Users require appropriate privileges to create custom reports either using Report Wizard or SSRS reports
Report Wizard has some limitations and is designed to enable users to create comparatively less complex reports
Complex SSRS reports can be developed and imported into Dynamics CRM. ‘Under the hood’ custom developed SSRS reports and Report Wizard reports are both structured in same XML format.
As mentioned earlier, Report Wizard reports have some limitations, which provides for a clear distinction when to write a custom SSRS report. For example, Report Wizard report cannot group data from more than two record types. Therefore it is recommended to create Report Wizard reports if it falls within this limited scope, otherwise a developer’s help will be required to develop custom SSRS reports.
Consider a scenario where a business wants to report on the open cases with its key data points grouped by Account (Customer).
- Click Service
- Click Reports
- Click New to create a new report
- Specify a Name and configure as shown by the yellow highlighting in the screenshot
- Select Start a new report and click Next
- Select Cases as Primary record type and click Next
- Specify the filtering criteria
- Select Click here to add a grouping to specify the grouping object in the report
- Add Grouping as shown below
- Select Click here to add a column to add columns from the Case entity
- Add columns as shown in the screenshot
- Select Table Only and click Next
- Proceed with the wizard and then click Finish to create the report
- Click Run Report
- Review the newly created report