Using Microsoft SQL Reporting Services with Microsoft CRM Online
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….
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.
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.
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.
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.
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.
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.
Here’s an example of querying all opportunities records in CRM.
<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.
<fetch mapping="logical" >
<entity name="opportunity">
<attribute name="estimatedclosedate" />
<attribute name="estimatedvalue" />
<attribute name="name" />
<filter>
<condition attribute="salesstagecode" operator="eq" value="200001" />
</filter>
<link-entity name="account" from="accountid" to="accountid">
<attribute name="name" />
</link-entity>
<link-entity name="systemuser" from="systemuserid" to="owninguser">
<attribute name="fullname" />
<attribute name="systemuserid" />
</link-entity>
</entity>
</fetch>
Step 1 complete.
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 reccomend.
Programming Microsoft SQL Server 2005
Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server Reporting Services
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.
<localhost/Reports/Pages/Report.aspx?ItemPath=%2fAcme+Reports%2fOpen+Pipeline+Review+Link>
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.
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.
cheers
-jonw
Comments
Anonymous
October 24, 2013
Unable to download MICROSOFT UPDATE. Examples and instructions useless .... no buttons to click!Anonymous
December 02, 2013
Useless article as FetchIt application no longer available. Poorly laid out as well as only realised that FetchIt was the extraction tool after the second reading....