Export report to an *existing* Excel spreadsheet

Hello all -
I have a report that I need to export to an existing Excel spreadsheet, but
I haven't seen so far any options where I can accomplish that.
For example, I have a generic skeleton spreadsheet that contains two
worksheets, "PivotTable" and "DetailData". The PivotTable worksheet contains
a pivot table that is bound to data in the DetailData worksheet (stating the
obvious, I know.) Based on the parameters entered in the Reporting Services
report, I want to populate the DetailData tab with the corresponding data.
That way, when the user opens the spreadsheet, it will contain the data based
on the selected parameters, and the pivot table will be refreshed.
I've explored 3rd party components, which work like a charm whether it be a
Windows or a Web application, but they can run in upwards of $5,000 per
processor. That said, I'm looking for a more cost effective solution where I
can do everything withing Reporting Services.
I realize too that I can use Excel automation to programmatically manipulate
the spreadsheet generated from Reporting Services, but server-side Office
automation is a NO-NO due to its instability and inability to scale.
Anyone have any ideas on how to accomplish this?
Thanks for your help!
KSA@.discussions.microsoft.comHi KSA,
I have two ideas -- don't know if either is a fit for your situation.
First, it sounds to me like you'd be better off just putting a query on the
DetailData worksheet. If your users are not on the LAN, then it'd be more
work, but I guess you could use Office Automation on the web server to
refresh the spreadsheet, then serve it up in IIS.
The other low-tech idea I had was to simply tell users to save the Excel
spreadsheet from RS in a certain directory using a certain name. Then, put
your PivotTable worksheet in a separate spreadsheet that references the
DetailData by filename.
HTH,
Ted
"KSA" wrote:
> Hello all -
> I have a report that I need to export to an existing Excel spreadsheet, but
> I haven't seen so far any options where I can accomplish that.
> For example, I have a generic skeleton spreadsheet that contains two
> worksheets, "PivotTable" and "DetailData". The PivotTable worksheet contains
> a pivot table that is bound to data in the DetailData worksheet (stating the
> obvious, I know.) Based on the parameters entered in the Reporting Services
> report, I want to populate the DetailData tab with the corresponding data.
> That way, when the user opens the spreadsheet, it will contain the data based
> on the selected parameters, and the pivot table will be refreshed.
> I've explored 3rd party components, which work like a charm whether it be a
> Windows or a Web application, but they can run in upwards of $5,000 per
> processor. That said, I'm looking for a more cost effective solution where I
> can do everything withing Reporting Services.
> I realize too that I can use Excel automation to programmatically manipulate
> the spreadsheet generated from Reporting Services, but server-side Office
> automation is a NO-NO due to its instability and inability to scale.
> Anyone have any ideas on how to accomplish this?
> Thanks for your help!
> KSA@.discussions.microsoft.com|||Hi Ted,
Thanks for your reply.
As far as your first idea, that was my exact alternative if we didn't
go with a 3rd party component. I'd just move the processing/pulling of
the data from server-side to client-side. I can append the parameters
from the WebFrom to an Excel template on the webserver using ADO .NET,
then stream the file to the client. Then I'll have a macro in the
spreadsheet that runs when the sheet is opened, pulling the
corresponding data based on the parameters.
For the second one, it doesn't fit the project's requirements precisely
as they require both the pivot table and detail data in the same
spreadsheet. I haven't fully explored all the options yet, and once I
do that, I can see how much "wiggle room" we have in modifying the
requirements a little.
Side note: I started exploring creating my own rendering extensions in
Reporting Services, but I'm not finding any good documentation or
resources on how to do it. At first glance, it seems it will be very
complex to do, so I might be left with choosing a 3rd party component
or maybe suggest we modify the project requirements slightly.
Thanks again!
Kael
Ted K wrote:
> Hi KSA,
> I have two ideas -- don't know if either is a fit for your situation.
> First, it sounds to me like you'd be better off just putting a query
on the
> DetailData worksheet. If your users are not on the LAN, then it'd be
more
> work, but I guess you could use Office Automation on the web server
to
> refresh the spreadsheet, then serve it up in IIS.
> The other low-tech idea I had was to simply tell users to save the
Excel
> spreadsheet from RS in a certain directory using a certain name.
Then, put
> your PivotTable worksheet in a separate spreadsheet that references
the
> DetailData by filename.
> HTH,
> Ted
> "KSA" wrote:
> > Hello all -
> >
> > I have a report that I need to export to an existing Excel
spreadsheet, but
> > I haven't seen so far any options where I can accomplish that.
> >
> > For example, I have a generic skeleton spreadsheet that contains
two
> > worksheets, "PivotTable" and "DetailData". The PivotTable
worksheet contains
> > a pivot table that is bound to data in the DetailData worksheet
(stating the
> > obvious, I know.) Based on the parameters entered in the
Reporting Services
> > report, I want to populate the DetailData tab with the
corresponding data.
> > That way, when the user opens the spreadsheet, it will contain the
data based
> > on the selected parameters, and the pivot table will be refreshed.
> >
> > I've explored 3rd party components, which work like a charm whether
it be a
> > Windows or a Web application, but they can run in upwards of $5,000
per
> > processor. That said, I'm looking for a more cost effective
solution where I
> > can do everything withing Reporting Services.
> >
> > I realize too that I can use Excel automation to programmatically
manipulate
> > the spreadsheet generated from Reporting Services, but server-side
Office
> > automation is a NO-NO due to its instability and inability to
scale.
> >
> > Anyone have any ideas on how to accomplish this?
> >
> > Thanks for your help!
> >
> > KSA@.discussions.microsoft.com|||Kael,
FYI, anything you can do in a VBA macro, you can also do from an external
app, e.g. an ASP.NET page. If there are security concerns about enabling
macros (which is totally understandable), I would recommend you run code to
update the data _before_ streaming the Excel file to the user.
Oh, and you're right about rendering extensions. They are by far the most
difficult extensions to RS to write. I doubt you want to go that way.
Ted
"kaeldowdy@.hotmail.com" wrote:
> Hi Ted,
> Thanks for your reply.
> As far as your first idea, that was my exact alternative if we didn't
> go with a 3rd party component. I'd just move the processing/pulling of
> the data from server-side to client-side. I can append the parameters
> from the WebFrom to an Excel template on the webserver using ADO .NET,
> then stream the file to the client. Then I'll have a macro in the
> spreadsheet that runs when the sheet is opened, pulling the
> corresponding data based on the parameters.
>
...
> Side note: I started exploring creating my own rendering extensions in
> Reporting Services, but I'm not finding any good documentation or
> resources on how to do it. At first glance, it seems it will be very
> complex to do, so I might be left with choosing a 3rd party component
> or maybe suggest we modify the project requirements slightly.
> Thanks again!
> Kael|||Ted,
I'm about resigned to not developing my own rendering extensions,
although I've read there is some good documentation on it in the 2005
beta. I haven't downloaded it yet from MSDN to see if it's even
feasable.
As far as doing it server-side (ASP .NET web app), are you referring to
using Excel/Office automation? That was one of the first things we
tried, but we found it to be VERY instable. The typical usage scenario
of this application would be about 10-15 *concurrent* users with
spreadsheets in upwards of 50 MB each. In this scenario (in which
we've proved out) the ASP .NET worker process gets extremely inflated,
and multiple instances of the Excel COM EXE process hangs out on the
webserver...even though you may set it to Nothing/null. I wouldn't
want to implement a solution that would potentially crash the webserver
and require contstant reboots.
Also, I did read in MSDN that Microsoft does not recommend or support
server-side automation.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757 That
was where I found some information on 3rd party components.
SoftArtisans is very promising, but they are in-between major version
releases and I haven't received any firm information from them for the
new version yet. I've since started exploring other options (such as
SQL Server RS) as well as other 3rd party components from Syncfusion,
Polarsoft, and Apose. Syncfusion at first looked great, but in my
project I have to deal with pivot tables, and Syncfusion currently
doesn't support them. BTW: I checked to see if RS can export a
matrix to an Excel pivot table, but no luck there.
I'm thinking we're going to have to rework some of the requirements
with the client since I'm finding it challenging to find an automated
solution that matches what they've envisioned ... or we go with the 3rd
party component.
I guess we'll see...
Thanks!
Kael
Ted K wrote:
> Kael,
> FYI, anything you can do in a VBA macro, you can also do from an
external
> app, e.g. an ASP.NET page. If there are security concerns about
enabling
> macros (which is totally understandable), I would recommend you run
code to
> update the data _before_ streaming the Excel file to the user.
> Oh, and you're right about rendering extensions. They are by far the
most
> difficult extensions to RS to write. I doubt you want to go that
way.
> Ted
> "kaeldowdy@.hotmail.com" wrote:
> > Hi Ted,
> >
> > Thanks for your reply.
> >
> > As far as your first idea, that was my exact alternative if we
didn't
> > go with a 3rd party component. I'd just move the
processing/pulling of
> > the data from server-side to client-side. I can append the
parameters
> > from the WebFrom to an Excel template on the webserver using ADO
.NET,
> > then stream the file to the client. Then I'll have a macro in the
> > spreadsheet that runs when the sheet is opened, pulling the
> > corresponding data based on the parameters.
> >
> ...
> >
> > Side note: I started exploring creating my own rendering
extensions in
> > Reporting Services, but I'm not finding any good documentation or
> > resources on how to do it. At first glance, it seems it will be
very
> > complex to do, so I might be left with choosing a 3rd party
component
> > or maybe suggest we modify the project requirements slightly.
> >
> > Thanks again!
> >
> > Kael