|
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 Cheers
(comments are locked)
|
|
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 eep - a SSRS report with 300 data sources..?!
Jul 26 '11 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 '11 at 02:52 AM
WilliamD
(comments are locked)
|
|
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? @Dave_Green you can fine tune the queries even in previous versions of Excel. :-)
Jul 26 '11 at 05:48 AM
Pavel Pawlowski
@Pavel Pawlowski - Absolutely; Excel 2007 was just what I had to hand at the time!
Jul 26 '11 at 06:31 AM
Dave_Green
(comments are locked)
|
