In-Depth

Microsoft Ties CRM to Reporting Services

Users can now tap SQL Server 2000 Reporting Services to report against Microsoft CRM data

• By Stephen Swoyer
• 10/06/2004

At its Professional Association for SQL Server (PASS) summit held last week in Orlando, Microsoft announced two new Report Packs—or collections of report templates—for its CRM and Exchange applications.

On top of these, the software giant also introduced a Report Pack for a sample financial reporting application.

Alex Payne, a senior product manager on Microsoft’s SQL Server team, says the new Exchange Report Pack includes a sample database and ships with 13 canned reports. Not surprisingly, Payne says, it’s designed mostly to report on Exchange administrative issues, such as mailbox sizes, which users are sending the most e-mail, and which keywords occur most frequently in company e-mail.

The CRM Report Pack, on the other hand, consists of six reports and a sample database based on CRM version 1.2. Payne says the reports are designed to help developers support key sales activities, including CRM mainstays such as doughnut charts of revenue grouped by account, industry, salesperson, or territory; detailed account information for a single customer or groups of customers; charts of customers in the pipeline; and lead summaries.

“With CRM, it mostly has to do with pipeline reports—what does our pipeline look like, how many new leads have we brought in over the last ‘N’ periods, how are we in the opportunity life cycle,” he says. “We’ve looked at what kinds of reports people are commonly building in those environments to date, and based these [Report Packs] on those.”

Like Visual Studio.NET and several other Microsoft products, CRM 1.2 currently ships with a bundled version of Crystal Reports. As a result, many existing Microsoft CRM users are tapping that product—or using their own internal reporting solutions—to report against CRM data. Nevertheless, some CRM consumers—and even a few Crystal users—say they’re intrigued by the new Report Pack offering.

Brandon Richey, CIO with The Azur Co., an SME consultancy based in Utah, says his company just implemented Microsoft’s Small Business Server 2003 and Microsoft CRM six months ago. At this point, Richey says, his company has “just started looking at what particular reports we need to generate to capture the data we need.” Nevertheless, Richey is eager to see what the new CRM Report Pack can do for him. “The more usable information I can generate, the better informed decisions we make,” he concludes.

Edwin Garst is a CRM administrator with Epcon Systems, a company that provides consulting services for users of midrange server products from IBM Corp. Garst says he doesn’t currently use the version of Crystal Reports (9.2.2) that Microsoft bundles CRM 1.2 because Epcon Systems currently uses Crystal 8.5, which Garst did test with CRM 1.2. This worked fine, he says—except he wasn’t able to schedule reports to run at off hours.

Because of this, Garst has already downloaded the CRM Report Pack, which he thinks he’ll be able to use for precisely this purpose. “Yes, I would schedule reports to run at off hours using MS Reporting Services,” he comments. “This function is not available with the version of Crystal Enterprise currently shipped with MSCRM. You must run all reports interactively.”

Garst does, however, express some concerns about how, exactly, the Report Packs work—particularly on the security front. “The major downside to the new reports is security. The existing reports in [Microsoft] CRM filter the results to exclude information to which to user is not authorized,” he comments. “The CRM data access API enables this behavior. Since the new reports don't use this API, another security mechanism must be established.”

Jason Beckett, a CRM administrator with an insurance provider based in California, says he, too, currently uses Crystal Reports. “Crystal is very powerful and useful; however, it is also costly and complex."

Nevertheless, Beckett, too, has downloaded the CRM Report Pack and plans to try it out. “The description of the included reports does sound useful,” he observes. “You will never find two sales people to agree on a single report that they like to use, so the more sales reports, the better.”

For version 2.0 of Microsoft CRM, which the company plans to deliver next year, Reporting Services will be bundled from the start. “The next version of Microsoft CRM is going to ship with Reporting Services natively as the reporting component in their application. They’re going to embed the controls in their application, so they’ll provide native Reporting Services reporting,” Payne says.

Financial Reporting—Or Proof of Concept?

Microsoft last week also delivered a new Financial Reporting Report Pack, which contains six reports for a sample financial database called FinSampleDB.Reports include a balance sheet, an income statement, a check of the double-entry system, and a budget variance. Although Microsoft Business Solutions currently markets its Great Plains financial application software, Payne says the new Report Pack isn’t designed expressly for use with that product.

“It’s not a reporting pack for a common application. It’s the kind of reports that people are building off of general-ledger-type data, and we’re putting in some common templates and formats for people to go build them,” he says. “There’s probably a little more work you’ve got to do in that environment, because we obviously don’t have a common underlying schema that we’re building these reports off of.”

In this respect, Payne says, the FinSampleDB Microsoft includes with the new Financial Reporting Report Pack gives people a good place to start. “For the financial reporting pack, the database is pretty generic in some respects—it doesn’t look like any particular financial application,” he says. “You can basically think of this as samples that are germane to your environment, with the goal of letting you start [with the sample database] and customize it from there.”

Going forward, Payne says Microsoft will almost certainly deliver Report Packs for several additional application offerings. “Right now, we’ve got a couple of scenarios that we’re looking at internally. We want to make sure that they’re going to be of value to customers, so we want to make sure what are the kinds of questions we receive from customers."