CRM 2011 (54) CRM 2013 (8) CRM 2015 (13) CRM 2016 (4) CRM Field Guide (29) CRM. Reports (2) Sales Management (2) SDK (2) Security (9) Service Management (3) SFDC (4. Windows 8 (2) Windows Live ID (1) Workflow (3) For Microsoft Dynamics CRM Sales, Marketing Solutions and Consulting. Connect with CRM Inovation. Microsoft Dynamics CRM is a multi-lingual Customer Relationship Management software package. The product focuses mainly on Sales, Marketing, Service and Help Desk sectors but Microsoft has been marketing Dynamics CRM as an XRM platform encouraging partners to use its proprietary (.NET based) framework to customize it to meet many different demands.
Microsoft CRM Online has a built in reporting wizard that allows you to create reports online without any other tools. These reports are persisted in the CRM Online application. This allows you to create reports without any detail knowledge of the report architecture. This is very convenient for users and requires very little training to create your own reports, eliminating the need to know query languages or report formatting. The report wizard reduces the complexity of building reports and makes report building available to end users regardless of their report building prowess. Reducing complexity often results in scaled down functionality. An example of this ability to insert images or complex computations inside the report itself. So, to start, let’s at look at our supported, out of the box options:
1. CRM Online Reporting Wizard
2. CRM Online Views
3. CRM Online Export to Excel
4. CRM Online Mail Merge
As you can see, there are a number of ways to do reporting out of the box with CRM Online. Each of these offers both pros and cons. It’s widely known that Microsoft Reporting Services offers an extensive and robust framework for managing reports, commonly known as SRS.
Some of the advantages of SRS is the built in Visual Studio Report Designer and the server application to host the reports for access via a web browser. There are a good number of reasons to want to use SRS to manage CRM reports. The heart of an SRS report is RDL. RDL stands for Report Definition Language. RDL is defined using XML. RDL allows user to build reports similar to how html allows users to build web pages.
Get on with it already….
Get on with it already….
To start, let’s define the steps we need to take to use SRS to host a CRM Online Report.
1. Extract CRM Data and make accessible to SRS.
2. Create SRS Report based on the data.
3. Create a report in CRM Online that points to the SRS report.
*The solution assumes you have your own instance of SRS.
1. Extract CRM Data and make accessible to SRS.
For obvious reasons, I wanted to introduce the out of the box reporting of CRM Online as well as SRS. First Loaded Question - Can I use SRS with CRM Online ? Well, not out of the box. SRS needs a data source to connect to the data. CRM Online supports web service access to the data. Access directly to the db layer is not accessible. Let’s look at the SRS data sources available:
You can see here that there is no native support for CRM Online. So we have to transform CRM Online data into a format that can be supported by SRS. Here are the 3 popular options:
Create a copy of CRM Data into a local SQL Server
The first option you might consider is to create a copy of the data from CRM Online to a local SQL Server. This to me is the least reasonable choice because it will require some pretty serious kung fu to move all the data plus build a new database in SQL to hold the data. Also, moving large amounts of data across an HTTP connection is not really sufficient.
The first option you might consider is to create a copy of the data from CRM Online to a local SQL Server. This to me is the least reasonable choice because it will require some pretty serious kung fu to move all the data plus build a new database in SQL to hold the data. Also, moving large amounts of data across an HTTP connection is not really sufficient.
Build an ODBC Connector
The next option to consider is to build an ODBC connector that used CRM Web Services calls on one side and exposed an ODBC interface on the other. This is not a bad option but I wanted something that could build reports in the background also. Plus the last time I wrote an ODBC connector , well let’s just say I’ve changed keyboards a few times.
The next option to consider is to build an ODBC connector that used CRM Web Services calls on one side and exposed an ODBC interface on the other. This is not a bad option but I wanted something that could build reports in the background also. Plus the last time I wrote an ODBC connector , well let’s just say I’ve changed keyboards a few times.
So we are left with our third choice, XML. ( Who would have thunk ‘d it ).
XML is the lingua franca of data exchange ( just say no to tables ). It’s entire purpose is in describing data exchange. Industry approved. Respected, liked and loved by all.
At this point, I should point out that when using the SRS XML data source you must specify a web url to be used to load the data. File protocols are not supported. However, you can store the file on a web server and access using HTTP.
Now, we need to determine how to get our data out of CRM Online and into to scantily clad XML.
Out of the box
Back to the out of the box options for a moment. You could create advanced find views and export the data into an Excel file and use Excel to save as xml. A bit manual but should work fine.
Back to the out of the box options for a moment. You could create advanced find views and export the data into an Excel file and use Excel to save as xml. A bit manual but should work fine.
Custom Web Service
This option would require you to create a web service that accesses CRM Online for data and return it as XML. I like this option from an architecture but not crazy about since I can’t schedule a web service to run automatically to update reports. Plus requires a server to host my web service and there are more security considerations. One advantage is you might be able to reuse the web services in other applications needing CRM data.
This option would require you to create a web service that accesses CRM Online for data and return it as XML. I like this option from an architecture but not crazy about since I can’t schedule a web service to run automatically to update reports. Plus requires a server to host my web service and there are more security considerations. One advantage is you might be able to reuse the web services in other applications needing CRM data.
Agent/Service
This option is to create software that runs locally that will pull data out of CRM and store in a XML file. I like this option the best since the reports can be scheduled run off hours and automatically. In addition, I find it the simplest of all the development options if your building the data extract yourself.
This option is to create software that runs locally that will pull data out of CRM and store in a XML file. I like this option the best since the reports can be scheduled run off hours and automatically. In addition, I find it the simplest of all the development options if your building the data extract yourself.
Another option would be to look at 3rd party integration tools such as ScribeSoft.
I decided to use a custom application or also known as agent/service/application.
I’ve built a tool previously, FetchIt Part 1 and FetchIt Part 2, that allows a fetch xml query to execute against CRM Online and have the results written to a file. It’s a simple console application that can run anywhere. It’s a simple in design. The development effort is relatively low and there is lots of sample code for how to make a fetch call. A console application can be scheduled to run automatically using the Windows Scheduler. It can be run on any machine with internet access. It can be run multiple times on a single machine ( once for each query ). The only thing needed is to change the fetch query statement in a configuration file for each report.
Fetch XML
Fetch XML is an important part of the solution since it can be used to make complex queries into CRM Online. The prevents the need to have all the data local to do complex queries using SQL server. Fetch XML is part of the CRM Online web services and software SDK.
Fetch XML is an important part of the solution since it can be used to make complex queries into CRM Online. The prevents the need to have all the data local to do complex queries using SQL server. Fetch XML is part of the CRM Online web services and software SDK.
Here’s an example of querying all opportunities records in CRM.
<fetch mapping='logical'><entity name='opportunity'><all-attributes/></entity></fetch>
<fetch mapping='logical'><entity name='opportunity'><all-attributes/></entity></fetch>
Here’s an example of querying all open opportunities records in CRM and joining data from the account record.
2. Create SRS Report based on the extracted CRM Data.
Creating SRS reports is well documented and describe elsewhere much better than I would attempt. A few resources I would recommend:
While I won’t build the report in depth in the blog, I will point out the process I used. One of the reason I wanted to create an SRS report was to do computations in the report. Notice in my sample report that I sum the estimated value of all the opportunities for each user and provide a total count. This is just to demonstrate some of the computations you could do in a SRS report.
The first step is to create a shared data source to be used by the report designer. Notice that xml file that I used FetchIt to create is stored on my localhost web server.
This can be any URL other than file protocols.
Here’s a look at the Report Designer for my report.
On the Data tab, be sure to set your Data Query for you report to be empty. <Query></Query>. See SRS documentation for more info. Basically, we want all the records in the XML file. You could use this query to query the xml data.
Next load your report into SQL Reporting Manager. Be sure to create a data source for the report in the SRS Manager.
Next, create a report link in the SRS Manager. Copy the link and use in CRM Online. I found this easiest to open the report in my browser and copy the address.
Step 2 complete.
3. Create a report in CRM Online that points to the SRS report.
A nice feature of the built in CRM Online Reporting is that I can create a report that points to a web page or a file. The file option works nice for Excel documents and Word docs. I can use the web page option to specific my SRS Report.
A nice feature of the built in CRM Online Reporting is that I can create a report that points to a web page or a file. The file option works nice for Excel documents and Word docs. I can use the web page option to specific my SRS Report.
Step 3 complete.
Opening the report from CRM Online will open the report in SRS.
Summary
You can build SRS reports for CRM Online in 3 easy steps.
1. Extract CRM Data and make accessible to SRS.
2. Create SRS Report based on the data.
3. Create a report in CRM Online that points to the SRS report.
Fetch XML allows you to build complex queries to sort and present your CRM Online data. It’s part of the CRM SDK, easy to use and well documented.
Using Microsoft SQL Reporting Services to report on CRM Online data is possible with a little effort. SRS gives you the ability to create rich reports using charts, computations, and text/images. In addition, you could use SRS to schedule reports to be automatic and emailed. There are many advantages to using SRS.
Using Microsoft SQL Reporting Services to report on CRM Online data is possible with a little effort. SRS gives you the ability to create rich reports using charts, computations, and text/images. In addition, you could use SRS to schedule reports to be automatic and emailed. There are many advantages to using SRS.
Cheers,
-->This section lists the hardware and software requirements for model-driven apps and mobile device client applications.
Web application hardware requirements
The following table lists the minimum and recommended hardware requirements for the web application.
Component | Minimum | Recommended |
---|---|---|
Processor | 1.9 gigahertz (GHz) x86- or x64-bit dual core processor with SSE2 instruction set | 3.3 gigahertz (GHz) or faster 64-bit dual core processor with SSE2 instruction set |
Memory | 2-GB RAM | 4-GB RAM or more |
Display | Super VGA with a resolution of 1024 x 768 | Super VGA with a resolution of 1024 x 768 |
Running model-driven apps on a computer that has less than the recommended requirements may result in inadequate performance. Additionally, satisfactory performance may be experienced running systems that use a different hardware configuration than those published here—for example, a system with a modern quad-core processor, lower clock speed, and more RAM.
Network requirements
Model-driven apps are designed to work best over networks that have the following elements:
- Bandwidth greater than 50 KBps (400 kbps)
- Latency under 150 ms
Notice that these values are recommendations and don’t guarantee satisfactory performance. The recommended values are based on systems using out-of-the box forms that aren’t customized. If you significantly customize the out-of-box forms, we recommend that you test the form response to understand bandwidth needs. More information: Verify network capacity and throughput for clients
Supported versions of Internet Explorer and Microsoft Edge
The following table describes the Windows and Internet Explorer or Microsoft Edge versions supported for use with the web application.
Windows version | Internet Explorer 10 | Internet Explorer 113 | Microsoft Edge |
---|---|---|---|
Windows 10 | Not supported1 | Supported | Supported |
Windows 8.1 | Not supported1 | Supported | Not supported |
Windows 8 | Limited support2 | Not supported1 | Not supported |
Windows 7 | Limited support2 | Supported | Not supported |
1 This version of Windows doesn’t support the version of Internet Explorer. More information: Internet Explorer 11 – FAQ for IT Pros
2 Supported with the classic web application. Not supported with Unified Interface apps.
3 Check requirements for individual apps, such as Customer Service Hub application requirements.
2 Supported with the classic web application. Not supported with Unified Interface apps.
3 Check requirements for individual apps, such as Customer Service Hub application requirements.
Important
Although you may be able to use Internet Explorer 8, Internet Explorer 9, or an Internet Explorer and Windows combination that is not supported in the previous table, those web browsers are not recommended and are not supported with this version of model-driven apps in Dynamics 365.
Using plug-ins or other third-party extensions in your browser can increase load times on pages with lists of data.
Supported non-Internet Explorer web browsers
The web application can run in any of the following web browsers running on the specified operating systems:
- Mozilla Firefox (latest publicly-released version) running on Windows 10, Windows 8.1, Windows 8, or Windows 7
- Google Chrome
- Google Chrome (latest publicly-released version) running on Windows 10, Windows 8.1, Windows 8, Windows 7, or Google Nexus tablet
- Google Chrome (latest publicly-released version) running on Mac OS X 10.8 (Mountain Lion), 10.9 (Mavericks), or 10.10 (Yosemite)
- Apple Safari (latest publicly-released version) running on Mac OS X 10.8 (Mountain Lion), 10.9 (Mavericks), 10.10 (Yosemite), or Apple iPad
To find the latest release for these web browsers, visit the software manufacturer’s website.
Important
- Using plug-ins or other third-party extensions in your browser can increase load times on pages with lists of data.
- Mozilla Firefox ESR (Extended Support Release) versions aren’t supported.
Supported versions of Office
To use model-driven apps in Dynamics 365 with Microsoft Office integration features, such as Export to Excel and Mail Merge, you must have one of the following Microsoft Office versions on the computer that is running the web application:
- Office 365
- Office 2016
- Office 2013
- Office 2010
For full Office 365 feature integration with Dynamics 365 and Customer Engagement (on-premises), you'll need Office 365 Enterprise E3 or later. Skype for Business PSTN calling and conferencing requires Office 365 Enterprise E5. Other Office 365 plans are not supported. For more information on licensing and pricing, see:
- Dynamics 365 pricing
Printing reports
The Reporting ServicesMicrosoft ActiveX control is required to print reports. If you try to print a report and the control isn’t installed, you’ll be prompted to install it. The installer package is named RSClientPrint.cab and can found on the SQL Server Reporting Services server at <drive>:Program filesMicrosoft SQL Server<MSSQL>Reporting ServicesReportServerbin.
Transport Layer Security (TLS) requirement
Web browsers and other client applications that use Transport Layer Security (TLS) versions earlier than TLS 1.2 won't be able to connect to their Dynamics 365 (online) environments and the admin center.
For more information, see these blog posts: