Tuesday, September 1, 2009

SFDC Connect for Office: Better together?

It really depends I guess.  It's a great tool to bring a Salesforce.com report into Excel, which has a rich set of presentation and data processing features.  However with slim documentation on how the software works, a few caveats really should be noted for any serious uses.

Basically Connect for Office works by using Excel's Data Connections feature.  However you can't edit any connection created by it.  This is understandable for reducing the chance of user messing up the inner workings, but also limits your options.  It'd be great to be able to replace an imported report that way - all I need to do is just changing the URL of the report source - then I get to keep all the formatted calculations and charts based on the report.  Well, you know life is never that kind, is it?  For that kind of change to happen, you'll have to go through the tedious steps of importing the new report, copying the dependent elements (formatting, formula, charts, etc.), removing the old setup, and adjusting anything that might break. 

Please note the order of those steps for altering source report is the best practice to follow.  Any deviation from that will be even harder to do (say removing the old report first) due to the fact that there're hard coded behavior - some from Excel, some from Connect for Office - that can't be changed: imported report only inserts (never overwrites), Excel adjusts references in an unmovable "smart" way, etc. 

And don't forget this: never change the worksheet name after you imported the reports, because the names are the IDs for the data connections to work.  It may be Connect's developer's fault, but could also be the result of Excel's API.  I have no interest to find out who's to blame, but this makes life very hard if you have to change the sheet names.  So try to be generic: for instance, anything says FY 2009 will get you into trouble if this is going to be reused next year - you just have to redo things all over again.

No comments:

Post a Comment