Excel: Database Reporting

Using excel to query the database to produce reports.

Using the ubiquitous Office application Excel, users may easily perform queries against your database to display reports within the worksheet and then utilize Excel’s robust tools to customize their reports.
For this example, I’ll begin with a simple customers tables from AdventureWorks db.

1-article-excel-query-db

First, I’ll open Excel and create a new connection “From Microsoft Query.”

2-article-excel-query-db

When prompted, create a “data source.”
Give the data source an intuitive name for ease of use and select the appropriate driver, then “Connect” and configure the “Login.”
Also, you may select a “default table” for the data source. the data source is now listed, click “OK.”

5-article-excel-query-db

6-article-excel-query-db

Next you will be prompted to select which fields to display, give the option to reorder them, and see a preview of each field’s data. Click “Next.”

7-article-excel-query-db

Next, you will be given the opportunity to apply filters.

8-article-excel-query-db

Next, I will configure sorting on three of the fields, then “Next” and “Finish.”

9-article-excel-query-db

Finally, I will prompted for a location to place the result set.
Upon completing the final step, Excel displays the result set within the spreadsheet along with filters for each column.

10-article-excel-query-db

11-article-excel-query-db

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s