Welcome to Computer Resources Inc.

Integrating SQL Reporting Services with MSCRM

Create a more flexible and powerful reporting environment

(Article is courtesy of .NET Developerís Journal - Bill Wolff)

Since Microsoft recently released the comprehensive SQL Reporting Services for SQL Server 2000, it's natural to want to integrate this inexpensive solution with Microsoft CRM (MSCRM). MBS will certainly make this a standard feature in an upcoming release, but you can have that functionality now if you follow some simple guidelines. Understanding the CRM and SRS architectures will help you make this a reality today.

Two Clients, One Browser
MSCRM has two client applications: browser-based and browser-based inside of Outlook. You have your choice of browsers as long as it's a version of Internet Explorer - the newer the better. The complex ASP.NET pages rely heavily on <iframe> tags and DHTML behaviors, a great technology that is an IE extension to standard JavaScript. The pages have the look and feel of a simple Windows Forms app, but not the rich interactivity of Office 2003.

The other client is called CRM Sales for Outlook. It's a subset of the browser application with pages exposed through folder definitions in Outlook. There are several custom Outlook forms that create things like Contacts. You can export Outlook Contacts to CRM. You don't get a rich GUI interface with drag and drop, just the same browser pages as the standard MSCRM offering. The interesting part of this approach is the offline usage option. The Web pages come from an instance of ASP.NET running on the client computer. Data access requires MSDE, which replicates lots of data to and from SQL 2000. Offline data modifications are stored in MSDE until the user reconnects. This requires a beefy client desktop since you are running MSDE (SQL Server junior), ASP.NET with ISAPI filters (including IIS), Outlook, and Internet Explorer at the same time.

Both clients get their data via Web services from a directory called MSCRMServices. This sits directly under your main Web site on the installed Web server. There are about 20 different SOAP interfaces with the file extension .SRF; these are C++ ISAPI filters. They apparently read and write directly to the SQL views described later.

There's also a set of Windows services running on the MSCRM server that monitor the SQL databases to process workflow rules, generate e-mail, and perform lazy deletes.

You can build your own custom user interface using a managed code SDK. This is a library of SOAP wrappers with 50+ entities each having 10-20 methods. All interaction requires string GUIDs and XML strings that define column sets, query specifications, and result sets. The XML-based query language is called Fetch XML with some interesting syntax for multitable joins. This is not structured query language! They use attributes a lot so good XML skills are a must. This presents an interesting challenge since most client-side databinding requires things like datasets. You can convert the XML string output to a dataset with some XML preprocessing using XMLDataDocuments. All wrappers talk to the SOAP interfaces mentioned earlier.

Much of the business logic is in the SOAP handlers. All data modifications must be made through this interface or you void your support contract. It's very tempting to write directly to the SQL tables, but that should be avoided. There is some overhead with this approach, but expect more technology like this as MBS embraces the SOA Web service design patterns.

MBS strongly recommends that you don't modify their ASP.NET pages. Instead, they provide integration points through an XML configuration file called ISV.CONFIG. Menu items, toolbar buttons, and tab pages can be added to most pages. This will allow us to display SRS reports as needed in an iframe. Now we need to find the pertinent data.

Show Me the Data!
MSCRM requires Microsoft SQL Server 2000 as the data store. There are four SQL databases created for every MSCRM installation. The database names are dependent on the business name registered with your MBS license. If our company name was ACME, the database would be:

ACME_MSCRM
ACME_METABASE
ACME_CRMCRYSTAL
ACME_MSCRMDistribution

The ACME_MSCRM database holds the critical business data in 100+ tables. The base tables don't have many indexes affecting ad-hoc query performance. All primary keys are GUIDs to support replication to the Outlook client. Triggers are also used to support replication.

Full-text indexing is placed on the knowledge base articles. This makes MSCRM incompatible with clustering services since SQL Server 2000 does not full-text index over clusters. If that feature is not needed, clustering should work fine.

All base tables are shadowed by updateable views. The views have "instead of" triggers that split updates across multiple base tables. CRM has a customizable schema that allows you to add (but never remove) your own columns. When this happens, the columns are added to the base tables, the views are dropped and re-created including the new columns, and the replacement triggers are regenerated. CRM views also handle lookup values by adding the string to the result set. If you are looking at a customer, you will see a customerid (GUID), customeridtype (int), and customeridname (string). You don't need to know where the related values come from. With this in mind, the best place to interact with CRM data is through these views. That will come in handy with our SQL Reporting Service integration.

The ACME_METABASE database has schema and design definitions for the various forms and lists. It uses a lot of XSL transformations to build ASP.NET forms on the fly.

The ACME_CRMCRYSTAL database holds the report definitions and permissions for the built-in reports. There is a Windows service that monitors this database for report job information.

ACME_MSCRMDistribtion is a system-level table that handles the replication tables for Outlook clients. The replication publication can be used for tasks other than offline clients. You could make a replica of the entire database for dedicated reporting.

The MSCRM tutorial is based on the fictional Adventure Works Cycle company stored in a separate set of databases. SQL Reporting Services uses the same fictional company for their walkthroughs.

All CRM security relies on Active Directory permissions, so a Windows 2000/2003 infrastructure is a must. CRM uses are assigned license keys and access groups. The groups are created in Active Directory with related entries in the SQLAccessGroup security group. The Web sites and Web services use impersonation to restrict SQL resource access. This approach is similar to that of SQL Reporting Services, so plan to use this SQLAccessGroup to implement reporting security.

Making the SRS Connection
SQL Reporting Services RS requires SQL Server 2000 and builds two SQL databases. One stores the report definitions and permissions; the other persists report snapshots so several users can share the same report data. There is a Web viewer for report administration and a virtual directory for report definitions.

All MSCRM users require an SQL Server client access license. SRS is free to any user with an SQL CAL, so all CRM users have full rights to SRS. Can't beat that price!

CRM deploys to a Windows 2000/2003 server with IIS/ASP.NET and the 1.1 .NET Framework installed. It likes to take over the default Web site. SRS deploys to a Windows XP/2000/2003 box with IIS/ASP.NET and the 1.1 .NET Framework installed. It seems like a no-brainer to install both on the same box, but the CRM assemblies and ISAPI filters seem to interfere with SRS admin functionality. Better to add a second site with another IP address and run SRS there. From a performance perspective, moving SRS to a separate box is ideal. In fact, putting SQL and SRS on one box and CRM on a second box makes a lot of sense.

Reports are authored in Visual Studio but require no formal programming language skills. The definitions are stored in an XML variant called RDL (Report Definition Language). You can edit the XML directly, but Visual Studio is much more productive.

Each report requires one or more data sources. Some of these drive the reporting data and some are used for parameters. This is where we turn to the MSCRM views mentioned earlier. To get a list of customers, connect to the ACME_MSCRM database customer view. Select the desired columns and place them on the report design surface.

The reports are tested in Visual Studio and then published to the report server in RDL format. The published reports are URL addressable and output in several formats including HTML, XML, PDF, Excel, Word, or text.

Customizing CRM Pages
Since the reports are URL addressable, they can run in an iframe inside of a customized CRM page. You need to allow customizations by editing the web.config file on the wwwroot folder of the MS CRM server. Change the value of ISVIntegration to On (remember, config files are case-sensitive XML):

<add key="ISVIntegration" value="On" />

Turning this on will automatically display customizations on your CRM Web pages. There are sample entries for menu, toolbar, and tab integrations. Edit these to get the desired look and feel. They're based on another XML file called isv.config in the wwwroot\_Resources folder. Add an entry to display a page for a report:

<NavBarItem Icon="/_imgs/ico_18_debug.gif" Title="SQL Report"
Url="http://SRSReports/reporttest/webform1.aspx" Id="navIsv1"/>

This URL points to a Web page on the SRS server that uses redirection to call the intended report. The CRM customization allows a URL specification but not a querystring. The querystring is actually appended by the CRM processing. Our redirection page parses this string and calls the intended report, optionally passing some query string. Here's some code from the redirection page:

Private Sub Page_Load(ByVal sender
As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Response.Redirect
("http://SRSReports/Reportserver?%2fSampleReports%
2fEmployee+Sales+Summary&rs:Command=Render")
End Sub

The report link appears in a navigation tab on the left of the CRM Sales module (see Figure 1.)

You can use the same approach to place a report in the CRM entity pages. Figure 2 shows the same report in the Account page.

This same technique can support the SRS Admin menus within CRM. This allows an administrator to set security, data source, and distribution options on reports. There are SOAP interfaces to these management services so the same functionality can be integrated with your custom code.

One of the most interesting features of SRS is the distribution engine. The Crystal reporting solution in CRM is demand-driven only. SRS allows push or pull delivery, so nightly or weekly reports can land in a folder or Web directory on schedule. Data-driven subscriptions query a table to render reports personalized to an individual or department. These are available on-demand or through e-mail distribution. With so many options, almost any reporting requirement can be satisfied with minimal coding.

Next Steps...
SQL Reporting Services is an excellent option for customized CRM reporting. Since CRM rarely fits customer needs right out of the box, integrators and consultants are typically used to tune the CRM infrastructure. They can do the same for the reporting suite. Take some time to assess your reporting needs and see how well the built-in Crystal reports meet those needs. If the reports need a fair amount of tweaking, consider the SRS alternative. With a little work, you will have a more flexible and powerful reporting environment with minimal licensing impact.

 

 

     A Smart Move  |  Products  |  Support  |  Career Opportunities | About Us  | Contact Us  | Home

Computer Resources, Inc. of Memphis - a Microsoft Business Solutions Reseller

Computer Resources, Inc.
901-382-1MEG (1634)

Serving Memphis, Mississippi, Arkansas, Tennessee, and the Mid-South 


Copyright ©2002- 2009, Computer Resources, Inc. All rights reserved.