SQL Reporting on multi site

Hi I am hoping someone can answer this. i am not a sql pro but i know my way around it. i have currently set up excel reports to pull back data from a single off site sql database, but what i need is to run the same report across 300 sites and get back only the results i need. EG.. show me all sites where the fire door has been opened more then 5 times, currently i run the report on 2 sites and it pulls the data back down to me then querys it, but as you can imagine it takes a very long time. does any one have any other ideas? eg tell the sql to run the report locally then transmit only the data i have asked for

more ▼

asked Jul 26, 2011 at 02:23 AM in Default

Dean161 gravatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You could use SQL Server Reporting Services (SSRS). This would allow you to turn your "pull" mechanism inside excel into a "push" mechanism. The report data would be on the central server, this can then be rendered into excel (or a number of other formats) and sent via email to the people who should receive it.

This would allow the slow transport of the information to be done at a time when the end-user doesn't necessarily have to wait on it (deliver before office hours).

This is one example of an SSRS tutorial at [MSSQLTips.com][1]

[1]: http://www.mssqltips.com/tutorial.asp?tutorial=222
more ▼

answered Jul 26, 2011 at 02:36 AM

WilliamD gravatar image

25.9k 17 19 41

eep - a SSRS report with 300 data sources..?!
Jul 26, 2011 at 02:48 AM Fatherjack ♦♦

I understood it as a report with 300 targets. The source is a central box/database.

But still "eep"

Some sort of data driven subscription?
Jul 26, 2011 at 02:52 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

Taking a slightly different approach - If I understand you correctly, you're using Excel as a data destination, and I suspect pulling all the records back and getting excel to query them. This is slow.

Using Excel 2007, I found that the data connection could be modified and changed from a table to an SQL type, allowing me to put the query in there - which was then passed as an (unchanged) SQL query to the database. This allows you to tune your query a little better, and only receive back the rows that you are interested in.

(Data menu -> Connections -> Select connection -> Properties -> Definition tab -> Command type = SQL, and type in the correct SQL syntax)

Did I interpret your situation correctly?
more ▼

answered Jul 26, 2011 at 05:41 AM

Dave_Green gravatar image

Dave_Green ♦
4.2k 3 4 8

@Dave_Green you can fine tune the queries even in previous versions of Excel. :-)
Jul 26, 2011 at 05:48 AM Pavel Pawlowski
@Pavel Pawlowski - Absolutely; Excel 2007 was just what I had to hand at the time!
Jul 26, 2011 at 06:31 AM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 26, 2011 at 02:23 AM

Seen: 715 times

Last Updated: Jul 26, 2011 at 02:23 AM