Monday, May 14, 2007

Excel Services as report system for CRM

Today I held a presentation at this years SharePoint and Exchange Forum in Nynäshamn, close to Stockholm, here in Sweden. Lots of very interesting experts here like Patrick Tisseghem, Michael Noel and Nick Swan, not forgetting my boss, Göran Husman and other great speakers from Microsoft and other companies.

The presentation I held was BDM-focused and concerned the power of simple mesh-up integration between SharePoint and MS CRM 3. The main point being that SharePoint has a lot of great features that can be used by other applications.

One of the most powerful ones is excel services that can very powerfully be used as a reports tool for MS CRM. The main advantages being the ease of use for the end users and report owners since they easily can modify their own reports with Excel.

It is almost too simple to write a blog about it, since it is very straight forward, but I will see if I can get something together anyway.

By the way, I am expecting to be a father around the 1:st of june (my first child!) and I migth be a bit absent for a week or two after this event, but I will be back.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB


  1. Gustaf,

    I spent some time playing with Excel services. I really like the idea; however, I found what I perceive as several drawbacks to using it for a CRM reporting tool.

    1. It does not support query tables. What this means is that you cannot use Excel’s built in Microsoft Query functionality to do SQL queries and table joins from the CRM database. You can bring data in from single CRM filtered views, but that limits you to a list or pivot table from just one table. This also means that I can’t view a dynamic spreadsheet that I have created from CRM in Excel Services.

    2. It doesn’t support the Excel snap functionality

    I think it would be possible to bring multiple CRM tables into an excel workbook and join them in Excel, but the process would get ugly and would require a lot more work than SRS. My hope is that this is just Excel Services 1.0, and they will get it right by v 2 or 3.

    Is my impression of this incorrect?

  2. Hi,
    Yes, I believe you are correct. There are work-arounds for your problems, for instance creating a new database containing personally created views based on the SQL-statements. It is however, not as user friendly as you might want.

    And, yes, it is Excel Services 1.0 and I expect the next version to be a lot more comprahensive. Another lack of functionality is also that you cannot use sharepoint-list synkronization in excel services.