A conceptual question about RS (when is it usefull)

In my department we are trying to benefit from the RS: we create a lot of
reports for the Marketing, and generally supply them through an Excel
spresdsheet:
1. Ad hoc reports with data which won't be updated.
2. Reports connected to views which are updated once they are oppened by the
user (=the Excel spredsheet is oppened).
3. Reports connected to tables which are daily updated (if the view is to
heavy).
In all the three situations, the RS doesn't help us.
I guess this tool is usefull for information departments who want to publish
reports to many subacribers at once
or to subscribers who need a "hard copy" of the report (=to print it)
or for interactive reports in which the user has to choose values (a year or
a customer or a product) and we don't want to write code.
Am I right?
Any comment or suggestion will be wellcome!RS is not excel and Excel is no RS. You have gotten used to Excel for
reporting and that is fine but it seems to me that you are approaching this
specifically from the viewpoint of a spreadsheet. If you look at what RS can
do you will find that you will be designing reports in a whole new way.
You say that RS doesn't help you. If you are already really skilled in Excel
and want to keep doing things the way you are then fine. It seems to me you
have already made your decision.
Some capabilities of RS:
1. You can schedule long running reports to run at a certain time, then the
user will use that snapshot.
2. Lots of caching configurations you can do
3. Drill through. This is huge. Instead of one mega report, put the data
they need and then provide links that pull up additional information in
reports passing the parameters to the report.
I'm not sure why you say RS doesn't help you in the three items you list.
Seems like it does for #3 for sure. Perhaps #2 as well.
As far as interactive reports, you can set defaults so the user doesn't have
to make a decision but can change it if they want to.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Geri Reshef" <GeriR@.Dubek.co.il> wrote in message
news:%23NwTAWI%23EHA.3260@.TK2MSFTNGP14.phx.gbl...
> In my department we are trying to benefit from the RS: we create a lot of
> reports for the Marketing, and generally supply them through an Excel
> spresdsheet:
> 1. Ad hoc reports with data which won't be updated.
> 2. Reports connected to views which are updated once they are oppened by
the
> user (=the Excel spredsheet is oppened).
> 3. Reports connected to tables which are daily updated (if the view is to
> heavy).
> In all the three situations, the RS doesn't help us.
> I guess this tool is usefull for information departments who want to
publish
> reports to many subacribers at once
> or to subscribers who need a "hard copy" of the report (=to print it)
> or for interactive reports in which the user has to choose values (a year
or
> a customer or a product) and we don't want to write code.
> Am I right?
> Any comment or suggestion will be wellcome!
>

A better way of running SPs from Excel

Hi All,

I didn't find an Excel forum here so I thought this might be the 'next best place'.

We have developed a 'packaged EDW' and the first version is all on the MSFT stack....though we do not want to use AS as we do want the product to be able to be back ended by other databases.....(heresy here, I know).

We have built what we are now called the 'Data Access Layer'.

The DAL is a suite of stored procedures.......the idea is simple.......ask a question via a SP with a set of parameters and it gives you a data stream in return. We are using Report Services first and it calls the DAL for its data.

But we have been thinking.....

The entire world has Excel on their desktops........and lots of those people love to have their reports in Excel (and not Report Services)....

We would like to find a really good way of using Exel on top of the Data Access Layer....so far, in looking into excel it seems we can really only run MS Query and return the data to a sheet and then go from there....this seems quite 'clunky'.

We are well aware of the idea of putting the data into AS and going from there...but we want to explore getting the data from SPs so that if we build some excel based reports and put the data into another database it will still work....

We have seen Oracle provides java add ins to Excel to get data directly from Oracle databases...

We are wondering if someone, somewhere, has developed 'the best' way of getting data from SPs into an excel workbook.

What we have are SPs to select options....such as time, product hierarchy, company reporting structure etc....and then the user selects from these options and the selections are passed to an SP to get the results for the selection..all the usual things needed for RS reports.....

We are wondering if someone has done something that is similar where the data is then delivered to Excel reports...

Our first big questions would be:

How to effectively select parameters for the report where the parameters are in the database?

1. How to effectively refresh the Excel reports on request or on a schedule?
2. How to most effectively imbed the calls to the stored procedures in Excel?

Sorry if I am in the wrong place....

Best Regards

OK, agreed that calling stored procedures from Excel is really clumsy. You will have to use VBA to do all the stuff behind, there is no easy way to implement that in Excel. Another thing would be to use an Analysis Source rather than a relational. This is far away from your original questions but can ive you the data in a much better and effective way than procedures can eventually do. But now to your questions:

1. You want tsomething like a dropdown box for selectable values ? You will have to populate them using VBA. A usable approach would be to fill some invisible excel sheets and point the filter boxes to the range of the extracted values.
2. You can use timers in VBA to refresh the code. Off the top of my head I cannot remember wehre the option in Excel was to refresh datasources on a regular basis. (By the way, using Reporting Services can do this all behind the scenes using a delivered report to a fileshare)
3. Well, all I know is that the most flexible way to do this is calling the procedures from VBA.

Hope my thoughts can help you, maybe you post your question also in one of the public newsgroups regarding Excel as there is none here in the MSDN ones.

Jens K. Suessmeyer

http://www.sqlserver2005.de

50% BLOAT in the Excel files created by Reporting Services.

First, I have SP1.
Microsoft SQL Server Reporting Services Version 8.00.878.00
I created a snapshot - to ensure the db query is not the cause.
I am working on the production hardware (beefy) - and I am the only
one on it.
The HTML version is quite fast off the snapshot (maybe 20 second).
The resulting table is 11k rows spread over 17 pages in HTML.
When I click export to Excel, it's a good 4-7 minutes before it
prompts me to OPEN or SAVE and then another minute or more to SAVE.
The resulting file is 7.8MB. The resulting file takes a good 3 minutes
to load in Excel.
Here's the problem.
The original spreadsheet (created by Business Objects) I am replacing
with RS is only 3.xMB and it has MORE tabs than the one I am creating!
It opens much faster - and is basically the same on the inside.
When I try SAVE AS and select Office 95 - it is still 5.5MB - the same
size I get when I SAVE AS to 2003 but with a different file name. **
This alone tells me there is 50% BLOAT in the Excel files created by
Reporting Services. **
Someone help me.
Why is it so slow?
Why is it so large?
What can I do to make this better? (and don't say SP2, ;))
Thanks in advance, JerryFirst, you can't compare the first page in HTML to the Excel output. You
could compare the MHTML output as it is also a single stream. I'm not sure
how BO's export works so you might not be comparing apples to apples if the
feature set (formatting, etc.) isn't the same. There are also style
duplication optimizations that we can't really do because of our current
flexibility. You might see what happens if you format your entire report as
a single style.
That being said, we are always looking to improve the size and speed of the
exports. If you can isolate what you think might be the root cause, we will
take a look.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410151234.1b184eb0@.posting.google.com...
> First, I have SP1.
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> I created a snapshot - to ensure the db query is not the cause.
> I am working on the production hardware (beefy) - and I am the only
> one on it.
> The HTML version is quite fast off the snapshot (maybe 20 second).
> The resulting table is 11k rows spread over 17 pages in HTML.
> When I click export to Excel, it's a good 4-7 minutes before it
> prompts me to OPEN or SAVE and then another minute or more to SAVE.
> The resulting file is 7.8MB. The resulting file takes a good 3 minutes
> to load in Excel.
> Here's the problem.
> The original spreadsheet (created by Business Objects) I am replacing
> with RS is only 3.xMB and it has MORE tabs than the one I am creating!
> It opens much faster - and is basically the same on the inside.
> When I try SAVE AS and select Office 95 - it is still 5.5MB - the same
> size I get when I SAVE AS to 2003 but with a different file name. **
> This alone tells me there is 50% BLOAT in the Excel files created by
> Reporting Services. **
> Someone help me.
> Why is it so slow?
> Why is it so large?
> What can I do to make this better? (and don't say SP2, ;))
> Thanks in advance, Jerry|||I am comparing apples to apples. I am not comparing to HTML.
My solution, finally, was to export as CSV and use Excel's Web Query -
that saves tons of space and time as the export is WAY faster than to
Excel. Thanks for your response, I have abandoned the Excel export
function as it was too slow.
This line from Books Online sealed the dealf for me:
"Excel / Renders a report in Microsoft Excel. / This format is useful
for report data that you want to manipulate offline or in Microsoft
Excel. Avoid this format for large reports."
BR//Jerry