January 12, 2021 at 2:16 pm
Hello everyone,
I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.
Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?
Cheers
Markus
January 12, 2021 at 4:58 pm
As far as I know, SSIS is going to be complicated to do this. You are basically going to need to build up a custom script in C# (or equivalent) to run in SSIS.
What is likely going to be the easiest way to do what you need (I am making a LOT of assumptions here) is have one workbook in Excel that connects to your SQL data source to pull in the raw data. Then use other workbooks to pull data from the Raw data via pivot tables for example.
Excel can either pull data directly from SQL or from another data source (such as SSRS). There are advantages and disadvantages to each approach, so pick one that works best for you.
Disadvantage to the "do it all in Excel" approach is end users need to click the "refresh all" button to make it refresh. You can have it auto-refresh on file load, but I find this sometimes causes issues so I do not recommend turning that on.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 12, 2021 at 4:59 pm
Hello everyone,
I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.
Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?
Cheers
Markus
The simplest (!) way of doing anything sophisticated with Excel is to install the Excel client program on the server which is doing the work. Once you have this in place, you can programmatically create an instance of Excel and do almost anything a user would be able to do.
But if you do not like the idea of installing Excel on a server, you are not alone.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 12, 2021 at 5:15 pm
This would be extremely laborious to do in SSIS, and I wouldn't recommend it. I would use either Power BI and its "Analyze in Excel" feature for something like this, or an SSAS cube and Excel pivot table workbook connected to it.
January 12, 2021 at 5:31 pm
msdeibel wrote:Hello everyone,
I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.
Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?
Cheers
Markus
The simplest (!) way of doing anything sophisticated with Excel is to install the Excel client program on the server which is doing the work. Once you have this in place, you can programmatically create an instance of Excel and do almost anything a user would be able to do.
But if you do not like the idea of installing Excel on a server, you are not alone.
not supported by Microsoft and against licensing rules to install a Client only application on a server.
And would require anyone with potential access to that server (directly or indirectly) to be licensed for the same version of Excel.
January 12, 2021 at 5:35 pm
you can do it - but not easy.
option 1 - Sharepoint Excel Services - https://docs.microsoft.com/en-us/sharepoint/dev/general-development/excel-services-overview
option 2 - use OpenXML https://github.com/OfficeDev/Open-XML-SDK - C# code - better as an independent app but could be done as a SSIS C# Script
January 12, 2021 at 5:44 pm
not supported by Microsoft and against licensing rules to install a Client only application on a server.
I found the following text here:
Besides the technical problems, you must also consider licensing issues. Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).
My reading of this is that as long as the Excel installation on the server is licensed along with (of course) all clients which are going to use the results of any Excel automation, there is no problem from a licensing point of view.
I agree that it's unsupported. But such automation is in place and running successfully at (I would guess) thousands of companies. It's perfectly doable.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 13, 2021 at 8:57 am
Thanks for all the answers so far. This is a lot more promising than what I read until now.
The most likely approach for me, would now be a combination of a C# implementation and one of
3rd party libs could be the one proposed by @frederico_fonseca or spreadsheet light with which I've worked before.
The only commercial product that came close was the one from DevExpress, but also their server can't do these things out of the box.
January 29, 2021 at 9:02 am
I would create the excel file as a template with a Raw Data tab that feeds your formula tabs and Pivot Charts or tables. A bit of C# can copy, paste and rename the template file as a destination file and then fill it with data from the database.
I don't really like working with Excel files but a lot of times is necessary due to business requirements.
I have a template package that I use for this matters. Can help you more if needed
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply