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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s