Reporting Options in Microsoft Dynamics CRM

In an earlier post, I discussed the possibility that you may need fewer reports in Dynamics CRM than you might think. 

This is because Dynamics CRM offers several approaches to get data out of the system which were served by reports in a traditional database application. 

Approach	Comments	Skills Needed
Dashboards	Great way to get the big picture and combine multiple data items on one screen	End User
Advanced Find	May be saved as personal views or shared with other users	End User
Export to Excel	Starting point for further manipulation	End User
Microsoft PowerBI	Sophisticated aggregation and business intelligence	Power User
Custom Reports	Implemented with SQL Server Reporting Service (SSRS) or FetchXML (CRM Online)	Developer
Reporting Add-Ins	Use in lieu of SSRS	Power User or Development

By taking advantage of all of these approaches, you can get the most out of Dynamics CRM reporting capabilities.

Microsoft SharePoint and Dynamics CRM -- Better Together: Part 5

SharePoint Strengths

This post focuses on the strengths of SharePoint. This blog post is an excerpt from an InfoStrat white paper. 

Portal Framework

SharePoint provides a framework for building websites, whether public websites, intranets or extranets.  You can create and populate a website quickly with SharePoint with a minimum of custom development.

Data Models in SharePoint 2013
The Microsoft Office Dev Center provides a thorough explanation of developing applications for SharePoint 2013 (https://msdn.microsoft.com/en-us/library/office/jj162979.aspx). The article on what’s new for developers (https://msdn.microsoft.com/en-us/library/office/jj163091.aspx)  highlights the key enhancements of SharePoint 2013 for developers and system architects.

The enhancements are in the following areas:

·         Cloud App Model
·         Familiar programming model using web standards
·         Development tools
·         Core platform enhancements
·         Mobility
·         Social and collaboration
·         Search
·         Workflows
·         Enterprise Content Management
·         Business Connectivity Services
·         Application services
·         Additional resources

For our analysis, the most important area is Business Connectivity Services (BCS) which enables SharePoint to access data from external data systems such as Dynamics CRM, in addition to other data-driven applications that are exposed through WCF services or OData endpoints.  BCS in SharePoint 2013 was  improved in many ways, including OData connectivity, external events, external data in apps, filtering and sorting, support for REST, and others.

There are three main approaches to defining data models for SharePoint applications:

1.    Using SharePoint data structures such as lists and content types. This approach is best for end users.

2.    You can define a data model in an external data source, such as a relational database, and then create a set of external content types to expose the data to SharePoint through the Business Connectivity Services (BCS) component. Alternatively, you could create a Web service to wrap the data source and create your external content types from the Web service.

3.    You can create a set of external content types to model and integrate an existing data source, such as a database, a Windows Communication Foundation (WCF) service, or a .NET type, to expose the data to SharePoint through the BCS.

SharePoint 2013 introduces a new way to support external data in autonomous units called apps.  Apps contain all the resources they need to run and are isolated from other apps. The app architecture allows you to access external data from SAP, Netflix, and proprietary and other types of data without involving the tenant administrator. Access to external applications is provided through Business Connectivity Services (BCS), which are a consistent and uniform interface that can be used by other SharePoint applications.

You can access external data with REST (representational state transfer) URLs for BCS.  Like the app framework, this enhancement makes it easier for developers to extend SharePoint.

A new SharePoint 2013 feature called JSLink allows rendering of fields, items and even Web Parts through a JavaScript file.

In summary, it is not that SharePoint cannot support relational databases, but rather that significant custom development is required to create a relational application that runs in the context of a SharePoint portal. Using SharePoint as a shell for a custom application may be the best approach when integrating with an existing application. 

Document Management

If unstructured data is the core of your application, SharePoint may be sufficient on its own.   For instance, SharePoint provides:

·         Shared website
·         Access without VPN client
·         Document management
·         Version control
·         Email Alerts
·         Workflow
·         Full text search
·         Document metadata
·         Meeting and project workspaces
 

While Dynamics CRM allows you to attach files to records, it is not a document management system.  If you want version control, check-in, check-out and other document features, you can turn to SharePoint.

Content Management

SharePoint is a content management system and Dynamics CRM is not. Dynamics CRM provides no functionality to build websites, intranets or extranets.  While Dynamics CRM can provide data for web services which you publish on a website, for content management, SharePoint is the hands-down winner.

Full Text Search

SharePoint offers a search engine which crawls multiple sources, searches on full text, and returns a combination of hits from web pages, documents, personal profiles and other sources.  Dynamics CRM allows searching in its structured data but has nothing to compare with the SharePoint search engine.

Once again, if you need this type of functionality, SharePoint and not Dynamics CRM would be your choice.

Decentralized Administration

The SharePoint paradigm provides a great deal of flexibility and independence to site owners.  In a decentralized organization, SharePoint allows departments and other groups to set their own standards without affecting others. 

SharePoint lists may be easily customized by adding new columns without affecting other lists in other sites.  You could have ten lists each called “proposals” with different fields and different configurations.  Nor is there a requirement that these lists share the same name, so similar lists could proliferate with different names in the same or different sites. 

Microsoft SharePoint and Dynamics CRM -- Better Together: Part 4

Dynamics CRM Strengths

This post focuses on the strengths of Dynamics CRM -- the next in the series on the strengths of SharePoint. This blog is an excerpt from an InfoStrat white paper. 

Relational Data

While SharePoint’s strength is unstructured content such as documents and web pages, Dynamics CRM is often better for structured, relational data.  SharePoint provides some features to get you closer to relational behavior, such as lookup columns in lists and site columns, but other key relational features are missing.

Several years ago my company embraced SharePoint for generating customer proposals.   It was great to store drafts, collaborate on documents, and track calendars.  We added metadata to the document library to show the type of proposal, name of customer, and other pertinent information. 

We ran into problems, however, when we want to run more sophisticated reports on sales activity, to match security permissions to account territories, and other features that are common in a CRM system.  Consequently, we implemented Dynamics CRM in order to track sales and marketing activities, but kept SharePoint as the repository for proposal documents which require document management features such as alerts and version control which are not available in Dynamics CRM. 

Dynamics CRM 2013 and 2015 versions added better support for aggregation on forms, a feature that reduces the need for reports in some instances.

Centralization and Standardization
Dynamics CRM excels at promoting consistency and shared standards that are centrally administered.  Unlike SharePoint, Dynamics CRM applications have a centralized data model so that the attributes are the same for an entity for all users. The security model and the user interface of Dynamics CRM are better suited to central administration than list objects in SharePoint which are under the control of site owners. 

Data Import and Export
Dynamics CRM comes out ahead of SharePoint in this category with its import utility and the ability to export from custom views, despite some SharePoint import and export features relating to Excel and Access.  Dynamics CRM provides a wizard interface to map data fields on import, and batches imports so unsuccessful imports can be undone.  Dynamics CRM is supported by mature third party import tools for importing and synchronization with other data sources as well. 

Contact Management
As contact management is at the core of Dynamics CRM, this one is not really a fair fight.  Straight out of the box, Dynamics CRM is a powerful contact management solution, while the SharePoint contact management template is crude by comparison, and lacks the most fundamental features (duplicate checking, field formatting, field validation, multiple addresses, links between contacts and organizations, mail merge) you would seek in contact management.

SharePoint offers a site template for contact management which implies that SharePoint provides a viable contact management system.  Unfortunately, in this case, SharePoint does not deliver.  The SharePoint contact management site template contains merely a handful of fields, and lacks the ability to track contact activities such as phone calls, meetings, or letters.  The SharePoint contact management template contains no reports.  You would be better off to track your contacts in an Outlook public folder than in the SharePoint contact management template. 

Case Management
Like contact management, case management is at the core of Dynamics CRM, and a large number of features are included out of the box such as case intake, escalation, a knowledge base, queues, and case management reports. Government agencies use case management for delivering services to constituents in areas such as health care, social services, and many more. SharePoint offers no analogous capabilities out of the box, but third party add-ins are available for some case management scenarios.

Marketing and Outreach
Dynamics CRM contains powerful features to contact people by generating emails, perform mail merges, and generate phone call lists.  Dynamics CRM helps you manage marketing campaigns and track expenses as well as responses to the campaign. Since contact management is the core of Dynamics CRM, these features are mature and rich.

To match even one of these functions in SharePoint would require significant custom development.   The only feature that comes close is the ability to send an email to members of a SharePoint site. 

Security Model
Dynamics CRM has a fundamentally different security model than SharePoint, as shown in Table 2.  It embodies the concept of an organizational hierarchy which segments data horizontally and vertically.  Dynamics CRM also relies on the concept of record ownership and assignment.  For instance, a sales manager can see all the opportunities that belong to the sales reps who report to her, but the sales reps cannot see one another’s records.  When a new opportunity is received, it may be assigned to the appropriate sales representative with the built-in Dynamics CRM feature Assign. 
 

Architectural Element	SharePoint	Dynamics CRM
Organization hierarchy	Does not exist, only site hierarchy	Represented in CRM administration
Row level security	Supported by exception in list objects	Supported by default and mapped to CRM user roles
Field level security	Requires workaround	Role Based
Page/Form Security	Included	Role Based Forms
Permission inheritance	By default by may be broken	By default, user may reassign record ownership
Security administration	Difficult to view across SharePoint farm	Centralized

Table 2. SharePoint and Dynamics Security Models

Dynamics CRM has a security model that includes the idea of an organizational hierarchy as well as individual record ownership.  For instance, you can have two divisions of a company that use the same data model and functionality but do not share data with one another.  Sales accounts may be divided into territories, and managers assigned to a group of account executives.  This means that all records associated with an account, such as contacts and activities will be visible to people with rights to that account. 

SharePoint has a security model that resembles Windows file system permissions, but with a twist.  A document library (or another list object) has permissions that flow down from the parent site.  Items in the list object inherit permissions as well.  Only if inheritance of permissions is broken can individual item permissions be set. 

SharePoint has a security model which offers its own richness and complexity, but in some ways is better suited to content management and web sites than to line of business applications. For instance, security permissions and roles may be inherited from a parent site or the inheritance may be broken to allow custom permissions.  SharePoint has no paradigm comparable to Dynamics CRM for modeling an organization, nor does it support the concept of record ownership as defined in Dynamics CRM.

While Dynamics CRM 2015 has field level security, SharePoint requires custom development to achieve this feature.  

Dynamics CRM allows you to easily view permissions by entity for each user role. The permissions for each entity are quite granular, and are enforced in conjunction with record ownership.  

Microsoft SharePoint and Dynamics CRM -- Better Together: Part 3

SharePoint is a content management system with a portal framework, a search engine and document management. It is a general purpose collaboration tool and may be applied to intranets, extranets and public websites.  SharePoint is based on the concept of lists, which are comparable to database tables. Lists are used for a large number of SharePoint features such as calendars, document libraries, content lists, news items and more.   Lists may be easily created by end users within their sites, and they do not have to be centrally managed so that these lists may differ from one site to another in a SharePoint implementation.

Dynamics CRM is a database application for tracking a number of entities relating to sales, marketing, customer service and contracts along with related activities and workflow.  Dynamics CRM exposes the data model to administrators as entities and attributes (tables and columns).  They can easily view all entities and attributes, as well as all locations such as forms where the attributes are used.  The data model of Dynamics CRM in inherently centralized compared to SharePoint’s model where centralization is optional.

The following figure shows how SharePoint and Dynamics CRM map to features commonly required in line of business solutions.   It shows which features are available out of the box and which require custom development.

Figure. 1. Product Features and Application Complexity

Microsoft SharePoint and Dynamics CRM -- Better Together: Part 2

This blog is an excerpt from an InfoStrat white paper. 

SharePoint and Dynamics CRM are based on different paradigms, and some but not all of their concepts map to one another.

Element	Dynamics CRM	SharePoint
Data	Entities (relational data)	Content types (pages, lists, etc.)
Fields	Attribute	Column / field
User Interface	Not applicable	Web parts
Data entry	Forms	Web pages
Knowledge Base	Knowledge Base	Wiki
Workflow	Workflows	Workflows
Views	Reports, views	Views, content query web part
Web sites	Not applicable	Sites and sub-sites
Web site templates	Not applicable	Site templates
Web page templates	Not applicable	Page templates
Dashboards	Dashboards, web components, charts	Web part pages, Business Data Catalog
Forms	Multipage form (tabs, one-to-many)	Single page form
Personalization	My Work	My Site
Notifications	Workflow	Alert
Tasks	Tasks	Tasks
Duplicate checking	Duplicate checking	Document names
Reports	Report designer	Report Center; Excel Services
Advanced Reports	Report Builder (SQL Reporting)	Report Builder (SQL Reporting)
Offline	Offline (requires Outlook)	Offline (requires Groove)

Table 1. SharePoint and Dynamics CRM Key Elements

Both SharePoint and Dynamics CRM are integrated into Microsoft Office in several ways.  For instance, SharePoint allows saving documents from Office into SharePoint document libraries, as well as linking to Outlook Calendars.  Outlook meeting requests can link to SharePoint workspaces.  Office programs include SharePoint functions in the ribbon interface.

Dynamics CRM (with the Outlook add-in) allows Outlook contacts, email messages, and appointments to be quickly added to Dynamics CRM.  Dynamics CRM records can even be taken offline in Outlook or viewed in mobile apps, a great feature for field workers.