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:
- Prepare the report you want to sent and deploy Report Server
- 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
- 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
- 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 - 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. - Use Windows Task Scheduler to register your Ashx file in order to run automatically in specific time
- Done