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.
Clients, One Browser
The other client is called CRM Sales for Outlook. It's a subset of the
browser application with
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.
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
have the look and feel of a simple Windows Forms app, but not the rich
interactivity of Office 2003.
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.
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.
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.
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
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.
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 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
is not needed, clustering should work fine.
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.
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.
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.
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
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.
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.
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.
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!
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.
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.
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.
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
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
key="ISVIntegration" value="On" />
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:
Icon="/_imgs/ico_18_debug.gif" Title="SQL Report"
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:
Sub Page_Load(ByVal sender
As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
report link appears in a navigation tab on the left of the CRM Sales
module (see Figure 1.)
use the same approach to place a report in the CRM entity pages. Figure
2 shows the same report in the Account page.
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
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.
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