Excel File as Email Attachment for Reporting Services

The scenario is data are pulled out from SQL Server through a rather complicated query without use temporary table, and then copy the dataset to Excel. There are some presentation formats needed in Excel. This setting is the result from requirement since the report readers want it in Excel with live data.

First, using SQL Server Management Studio to arrange SQL Server side work, though it is not necessary. Create a SQL file, organize the SQL query, make sure no temporary table used, and the result should be a single dataset. Complicated formatting as well as query logic should be arranged within a single query by using inner queries as well as WITH AS statement.

If one dimension of original dataset from SQL Server is dynamic, make sure it would be the records dimension (vertical) rather than field dimension (horizontal). This is particularly important because if you have a dynamic horizontal dimension, any change would need human intervention by changing SELECT CASE part in query. You would need to make it as automation as possible. For details how this is work, please refer to http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html. If the dynamic dimension is to be at vertical direction in final report, you can write a special stored procedure to do the transpose transformation, or in Excel, using its built function. By adding CREATE VIEW AS, create a view which sits inside of SQL Server. SQL Server side work has been done.

In Excel, organize the presentation worksheets and dedicate a special worksheet for internal use. All live data including report covering period and report generating date time are to be organized in this internal sheet. Next step is to arrange the automation get external data directly from the view in SQL Server. Click Data in menu, Get External Data, From Other Sources, From Data Connection Wizard, Other/Advanced, A popup window will show. In Provider tab, select SQL Server Native Client 10.0, Next, make a right selection in Select or enter a server name, click Use Windows NT Integrated security, make a right selection in Select the database, then click OK. Finish it. Make sure the dataset from SQL Server will be arrived at right position of that internal worksheet.

Next is to create a button and write a VBA script to complete following tasks:
1. Make a transpose copy of dataset within the internal sheet, if it is necessary.
2. Copy whole or partial dataset to presentation worksheet by using Paste Values. Make sure there is no function in presentation worksheet at all. This does not only show internal works to the end users, but also make readers easily use the data as static.
3. Copy header and footer dynamic data to presentation worksheet by using Paste Values. Make sure this part is also of static data.
4. Hide the internal worksheet. If it is necessary you can also protect the hiden sheet by set password. All you may wish to completely delete the internal worksheet.
5. Automatically send email and attach this Excel file.

VBA Example:

Sub Process()
Sheets("Internal Use").Select
ActiveWorkbook.RefreshAll

Range("A5:AW68").Select
Selection.Copy
Range("B70").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True

Range("D1").Select
Selection.Copy
Sheets("Totals").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

Sheets("Internal Use").Select
Range("A3").Select
ActiveWindow.SelectedSheets.Visible = False

ActiveWorkbook.SendMail
Recipients:="someone@somewhere.com", _
Subject:="Weekly Report "
End Sub