How to Create SQL-Embedded Excel Reports

[synapse88]

I want be able to send out excel workbooks with SQL queries embedded.

For example I want to send out a monthly sales report for the month of June, have the user open the report and have the SQL query populate the appropriate cells with June's sales data. I would want the user to be easily change a data value in the query a month later, re-execute the query and have July's sales data.

How would I set something like this up? I'm using Excel 2003 and would be querying a Microsoft SQL Server.

 

[tusharm]

Use MS Query to create a parameterized database query (Data | Import External Data > New database query...)

In the Query UI specify a parameterized critierion. To do so, in the criterion field specify something within square brackets (same principle as in MS Access).

Now, when you return to XL first MS Query will ask you to specify a value for the parameter. Specify any value you like. Then, back in XL, XL will ask you how the parameter should be managed. Your choices will be (a) some literal value, (b) have XL ask the user for a value each time the query is executed, or (c) take the value from a cell. In the last case, an additional option will be to rerun the query whenever the value in the cell changes. If it isn't obvious what you should use, use option (c).

Now, use this data table as the source for whatever further analysis you want to carry out.