SQL Server Portal

Reports are the backbone for each and every application and most of the reports are designed using SQL Server Reporting or crystal report in dot net applications. But most of these reports are usually exported by end users in excel for further processing and in most cases we spend a lot of time to design and develop those reports in SSRS or Crystal. So is there a short cut ?

Certainly. You can populate the data in excel directly from SQL Server and prepare the same report in few minutes.

Lets do it step by step.

Step 1 :
In the first step you need to create a table or view, that you will be using in the excel for reporting.
For this demonstration, we will use an already created view namely “vEmployee” in the AdventureWorks2012.
You can download this AdventureWorks2012 database from here.

Step 2 :
Open excel 2010 and select Data tab and then click on From Other Sources and select From SQL Server, as shown in the picture below.

Step 3 :
Once you click on From SQL Server, it will open a Data Connection wizard. In this wizard, you need to enter SQL Server credentials and click NEXT, as shown in the picture below.

Step 4 :
Now, you are in the database and table/view selection window. Just select the appropriate database and table or view and click NEXT, as shown in the picture below.

Step 5 :
This is the last window of Data connection wizard, where you need to save the connection and click FINISH.

Step 6 :
After that you need to select format (Table, Pivot Table Report, Pivot Chart and Pivot Table Report) of data in excel and where (Existing sheet, new worksheet) to place the data. Lets select a table and the existing sheet from the options.

Step 7 :
Now, we have the data and beauty is that, whenever you press refresh button, excel refreshes the data from SQL Server and displays the up to date data in excel. Now, you can do pivot and other reporting stuff with this data in few minutes.

Conclusion :

This tool usually saves a lot of time in developing complex reports. I usually recommend people to prepare some templates of these reports and whenever there is a requirement, just refresh the data and send it to end users.

Let me know if you have implemented this technique in real world.