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
asked Jul 26 '11 at 02:23 AM in Default
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
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?
answered Jul 26 '11 at 05:41 AM