Excel automation on sql server without Excel or third party addons… Is it possible?
Most people will tell you not if you want pivot tables updated.
Accidently found out why when pumping data into ranges, the amounts are put in as text fields, not numbers.
This means the pivot tables don't update!
But there is a simple workaround to make the data numeric rather than text, so the pivot tables work nicely.
We are working on pumping out dozens of spreadsheets with many pivot tables to a SharePoint Extranet.
SSIS is an obvious choice because they want one big excel spreadsheet with many sheets and Reporting Services (even in sql 2008) cannot deliver a professional finish.
* For those unaware of this issue – reporting services pumps out each sub report on a separate sheet but the sheet names cannot be changed from sheet 1,sheet 2 etc…
The end result is a nice SharePoint Extranet reporting solution which goes well with our special Excel SharePoint Extranet creator Application.
What's funny, is how many times you need to find workarounds for Microsoft Products even in the 21st century…like the workaround to update links in excel services etc.
If you have a similar problem and want a nice resolution, let us know.
Regards,
Tom Bizannes
Solutions for Microsoft office and SharePoint
