Tags

, , , ,

Recently I was required to provide an excel report related to Microsoft Team Foundation Server workitems, but the requirement is kind of hard to get from Microsoft Visual Studio 2012 Queries directly, which means it’s also not possible to export the report to excel as well.

After a little search, I found out there is a functionality of excel that allow you to create report from a external data source by using Microsoft Query.

You need to have the ‘Read’ Access to the SQL Server Database you like to generate the report

Here is a simple tutorial that how it works:

  1. Open Excel -> Navigate to “Data” tab -> Navigate to “From Other Sources” tool at toolbar -> Select “From Microsoft Query”
  2. From the “Choose Data Source” window, Select “<New Data Source>” and then click on “OK” buttonCreate an Excel Report from Microsoft Query by execute the SQL query directly against SQL SERVER 2012 - 01
  3. Which leads another popup window “Create New Data Source”, fill the data source name you want, select driver as ‘SQL Server’, then click on “Connect…” button, which popup another window for authentication, you can enter/select your DB Server Name, if you like to use windows authentication, you can check “Use Trusted Connection” or fill the DB username/password if you want to use SQL Server DB authentication.Create an Excel Report from Microsoft Query by execute the SQL query directly against SQL SERVER 2012 - 02
  4. Click “OK” button will pop up the Microsoft Query window with Query Wizard, which we gonna cancel it since we’re going to add SQL query directlyCreate an Excel Report from Microsoft Query by execute the SQL query directly against SQL SERVER 2012 - 03Then click “Close” in “Add Tables” popupCreate an Excel Report from Microsoft Query by execute the SQL query directly against SQL SERVER 2012 - 04
  5. The next step is enter the SQL query you like to execute for your report by clicking the ‘SQL’ tool button from Microsoft Query toolbar.Create an Excel Report from Microsoft Query by execute the SQL query directly against SQL SERVER 2012 - 05Paste or Write your statement in the popup, then click on “OK” button, Microsoft Query will execute the statement and return the result, and if the SQL query cannot be represented graphically, you’ll need to confirm to run it anyway.Create an Excel Report from Microsoft Query by execute the SQL statement directly against SQL SERVER 2012 - 06
  6. After you get the result, and verify is what you want, you can click “Return Data to Microsoft Excel” sub-menu and return to Excel,Create an Excel Report from Microsoft Query by execute the SQL statement directly against SQL SERVER 2012 - 07and choose where you like to put the result data in your excel sheet, or you can add/modify the report type as you like in excel.Create an Excel Report from Microsoft Query by execute the SQL statement directly against SQL SERVER 2012 - 08

After you save the excel file, you should be able to update the report by choosing “Refresh” button from “Data” tab, and no need to repeat this complex procedure again. 😛

Advertisements