Scheduling Excel Report With SSRS 2008 R2 And ASP.Net

As default SQL Server Reporting Service 2008 R2 has Scheduled Report Generation named Subscription. You can choose what report and when to generate it also delivery method.

However this feature is quite standard. We must supply report’s Parameter manually to Subscription. So that in this blog I will show you how to make more flexible to this kind of schedule using Visual Basic Asp.Net

The generated report will be on Excel format.

Here are step by step:

1. Prepare the report you want to sent and deploy Report Server
2. Browse the report via http://Your_serverip/ReportServer and get its exact Url for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render
3. Add report’s parameters to Url query string to make Report runs directly via that Url, for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render&Your_parameter_Name1=Your_parameter_Value1&Your_parameter_Name2=Your_parameter_Value2
4. In order to have direct download to Excel add rs:format=EXCEL to Url Query string, for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render&rs:format=EXCEL&Your_parameter_Name1=Your_parameter_Value1&Your_parameter_Name2=Your_parameter_Value2
5. Write Asp.Net code. I prefer using Generic Handler Ashx file:

   ....
   
   context.Response.Clear()
   Dim strReportUser As String = "Your_UserName"
   Dim strReportUserPwd As String = "Your_Passwd"
   Dim strReporUserDomain As String = "Your_Domain"
   
   Dim sTargetUrl As String = "http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render&rs:format=EXCEL&Your_parameter_Name1=Your_parameter_Value1&Your_parameter_Name2=Your_parameter_Value2"
   
   If (File.Exists("C:/Your_Report_name.xls")) Then
   	File.Delete("C:/Your_Report_name.xls")
   End If
   
   Dim req As HttpWebRequest = WebRequest.Create(sTargetUrl)
   req.PreAuthenticate = False
   req.Credentials = New NetworkCredential(strReportUser, strReportUserPwd, strReporUserDomain)
   req.Timeout = 120000
   req.ReadWriteTimeout = 120000
   
   Dim HttpWebResp As HttpWebResponse = req.GetResponse()
   Dim fstream As Stream = HttpWebResp.GetResponseStream()
   Dim Buffer(4096) As Byte, BlockSize As Integer
   
   'Memory stream to store data
   Dim TempStream As New MemoryStream
   Do
   	BlockSize = fstream.Read(Buffer, 0, 4096)
   	If BlockSize > 0 Then TempStream.Write(Buffer, 0, BlockSize)
   Loop While BlockSize > 0
   
   'return the document binary data
   'Return TempStream.ToArray()
   
   Dim filebytes As Byte() = TempStream.ToArray()
   HttpWebResp.Close()
   
   File.WriteAllBytes("C:/Your_Report_name.xls", filebytes)
   
   Dim smtpserver As New SmtpClient()
   
   smtpserver.Credentials = Net.CredentialCache.DefaultNetworkCredentials
   smtpserver.Port = 25
   smtpserver.Host = "Your_SMTP"
   
   Dim mail As New MailMessage()
   mail.IsBodyHtml = True
   
   mail.From = New MailAddress(Your_From_Email)
   mail.To.Add(Your_Destination_Email)
   
   mail.Subject = Your_Subject
   mail.Body = Your_Messg
   mail.Attachments.Add(New Attachment("C:/Your_Report_name.xls"))
   
   smtpserver.Send(mail)
   mail.Dispose()
   
   context.Response.Write("<script language='javascript'>")
   context.Response.Write("window.open('','_self','');")
   context.Response.Write("window.setTimeout('window.close();', 100);")
   context.Response.Write("</script>")
   context.Response.End()
   
   ....
   

   Please change verbs ‘Your_xxx’ with your specific information respectively.
   The idea in above source is to get web content with specific Url which produces Excel file by Visual Basic HttpWebRequest and HttpWebRespose class. After the Excel saved and then I sent it via email.
   I also use little javascript to close the browser automatically after sending report.

6. Use Windows Task Scheduler to register your Ashx file in order to run automatically in specific time
7. Done