Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query multiple database platforms from Excel
Each week I have to prepare a "Master Summary" report in excel that
takes data totals from 6 other database systems including multiple Oracle instances, SQL Server and MySQL databases. The data from each is independently queried, grouped, pivoted, sliced, diced and totaled upstream using dozens of brio reports and sometimes progressively more complex excel spreadsheets. Each brio report connects to the respective database needed and runs custom SQL queries against it to get the totals required for that region/system. The reports (there are literally dozens) must be manually run then the data totals need to either be exported to excel for further processing and hand copied into the Master Summary or eyeballed and typed into the excel spreadsheet! This whole process takes an entire week to complete. My question is: is there a better way to do this? Is there some program or feature that I'm not aware of that can: 1) connect to and query multiple databases to get data totals regardless of vendor 2) perform functions/formulas/manipulation on that data regardless of what database it is coming from 3) combine the data from a many, many sql queries into a single summarized spreadsheet that makes sense ?? Thanks in advance for your feedback. -Deepak |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query multiple database platforms from Excel
Selecting
Data | Import External Data | New Database Query should allow you to connect to any database that you have the (ODBC) drivers for. since you indicate that you are using Brio to create reports then you may have to replicates some of that logic in your spreadsheet... -- HTH... Jim Thomlinson " wrote: Each week I have to prepare a "Master Summary" report in excel that takes data totals from 6 other database systems including multiple Oracle instances, SQL Server and MySQL databases. The data from each is independently queried, grouped, pivoted, sliced, diced and totaled upstream using dozens of brio reports and sometimes progressively more complex excel spreadsheets. Each brio report connects to the respective database needed and runs custom SQL queries against it to get the totals required for that region/system. The reports (there are literally dozens) must be manually run then the data totals need to either be exported to excel for further processing and hand copied into the Master Summary or eyeballed and typed into the excel spreadsheet! This whole process takes an entire week to complete. My question is: is there a better way to do this? Is there some program or feature that I'm not aware of that can: 1) connect to and query multiple databases to get data totals regardless of vendor 2) perform functions/formulas/manipulation on that data regardless of what database it is coming from 3) combine the data from a many, many sql queries into a single summarized spreadsheet that makes sense ?? Thanks in advance for your feedback. -Deepak |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query multiple database platforms from Excel
Selecting
Data | Import External Data | New Database Query should allow you to connect to any database that you have the (ODBC) drivers for. since you indicate that you are using Brio to create reports then you may have to replicates some of that logic in your spreadsheet... -- HTH... Jim Thomlinson " wrote: Each week I have to prepare a "Master Summary" report in excel that takes data totals from 6 other database systems including multiple Oracle instances, SQL Server and MySQL databases. The data from each is independently queried, grouped, pivoted, sliced, diced and totaled upstream using dozens of brio reports and sometimes progressively more complex excel spreadsheets. Each brio report connects to the respective database needed and runs custom SQL queries against it to get the totals required for that region/system. The reports (there are literally dozens) must be manually run then the data totals need to either be exported to excel for further processing and hand copied into the Master Summary or eyeballed and typed into the excel spreadsheet! This whole process takes an entire week to complete. My question is: is there a better way to do this? Is there some program or feature that I'm not aware of that can: 1) connect to and query multiple databases to get data totals regardless of vendor 2) perform functions/formulas/manipulation on that data regardless of what database it is coming from 3) combine the data from a many, many sql queries into a single summarized spreadsheet that makes sense ?? Thanks in advance for your feedback. -Deepak |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query multiple database platforms from Excel
This may be a good situation for the QueryMaster file.
It has examples of ODBC-less connections to various databases. If you already have (or can get) the Data Source information, perhaps you could adapt it to your query needs. It's available at Debra Dalgleish's website: http://www.contextures.com/excelfiles.html#External Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Each week I have to prepare a "Master Summary" report in excel that takes data totals from 6 other database systems including multiple Oracle instances, SQL Server and MySQL databases. The data from each is independently queried, grouped, pivoted, sliced, diced and totaled upstream using dozens of brio reports and sometimes progressively more complex excel spreadsheets. Each brio report connects to the respective database needed and runs custom SQL queries against it to get the totals required for that region/system. The reports (there are literally dozens) must be manually run then the data totals need to either be exported to excel for further processing and hand copied into the Master Summary or eyeballed and typed into the excel spreadsheet! This whole process takes an entire week to complete. My question is: is there a better way to do this? Is there some program or feature that I'm not aware of that can: 1) connect to and query multiple databases to get data totals regardless of vendor 2) perform functions/formulas/manipulation on that data regardless of what database it is coming from 3) combine the data from a many, many sql queries into a single summarized spreadsheet that makes sense ?? Thanks in advance for your feedback. -Deepak |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query multiple database platforms from Excel
thanks for the feedback guys. I came across this dashboard tool and am
having some success with it: http://www.getfirefly.net/ Ron, I will look into the tool you provided as well thank you sir. -Deepak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a count of #of times a result occurred on multiple platforms | Excel Worksheet Functions | |||
Multiple parameter database query | Excel Programming | |||
Multiple ODBC Database lik in one MSQUERY query | Excel Discussion (Misc queries) | |||
Required multiple user name and password logins for database query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |