By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,542 Members | 1,740 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,542 IT Pros & Developers. It's quick & easy.

How do i export crystal report in asp.net using the export button?

P: 1
Hi All,

I have a crystal report that loads default values from the database, using SQL server 2005. On my web page, I have 2 databound dropdownlist and upon selectedindexchanged the report must reload data based on selectedindex.

However only the first page of the report refreshes while the other pages remain with the initial data. When I try to export the report to excel using the export button from the crystalreportviewer toolbar it re-load the initial report.

Here is my code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Imports System
  3. Imports System.Web
  4. Imports System.Web.UI
  5. Imports System.Web.UI.WebControls
  6. Imports System.Data
  7. Imports System.Data.SqlClient
  8. Imports System.Configuration
  9. Imports CrystalDecisions.CrystalReports.Engine
  10. Imports CrystalDecisions.Shared
  11. Imports CrystalDecisions.CrystalReports.Engine.ReportDocument
  12.  
  13. Partial Class RptTraining
  14.     Inherits System.Web.UI.Page
  15.     Dim oRpt As New ReportDocument()
  16.     Dim sqlConn As New SqlConnection
  17.     Dim strConnection As String
  18.     Dim MyCommand As New SqlClient.SqlCommand()
  19.     Dim MyDA As New SqlClient.SqlDataAdapter()
  20.     Dim myDS As New DataSet3()
  21.     Dim x As Integer
  22.     Dim AppID As Integer
  23.     Dim yr As String
  24.     Dim c As String
  25.     Dim comp As String
  26.     Dim ParaFields As New ParameterFields()
  27.     Dim p1 As New ParameterField()
  28.     Dim pd1 As New ParameterDiscreteValue()
  29.  
  30.     Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
  31.  
  32.         BindReport()
  33.  
  34.     End Sub
  35.  
  36.     Sub BindReport()
  37.  
  38.         x = ddlTraining.SelectedIndex
  39.         If x = 1 Then
  40.             AppID = 15
  41.         ElseIf x = 2 Then
  42.             AppID = 16
  43.         ElseIf x = 3 Then
  44.             AppID = 17
  45.         ElseIf x = 4 Then
  46.             AppID = 18
  47.         End If
  48.  
  49.         strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
  50.         sqlConn = New SqlConnection(strConnection)
  51.         MyCommand.Connection = sqlConn
  52.         MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = 16)ORDER BY dbo.HR_Master.SurName, dbo.HR_Master.ForeName, Department"
  53.         MyDA.SelectCommand = MyCommand  
  54.         MyDA.Fill(myDS, "training")
  55.         oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
  56.         oRpt.SetDataSource(myDS)
  57.         CrystalReportViewer1.ReportSource = oRpt
  58.  
  59.  
  60.         p1 = New ParameterField()
  61.         pd1 = New ParameterDiscreteValue()
  62.         p1.ParameterFieldName = "year"
  63.         pd1.Value = "2008"
  64.         p1.CurrentValues.Add(pd1)
  65.         ParaFields.Add(p1)
  66.         CrystalReportViewer1.ParameterFieldInfo = ParaFields
  67.  
  68.  
  69.  
  70.     End Sub
  71.  
  72.     Protected Sub ddlTraining_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTraining.SelectedIndexChanged
  73.  
  74.         ConfigureReport()
  75.  
  76.     End Sub
  77.  
  78.     Sub ConfigureReport()
  79.  
  80.         x = ddlTraining.SelectedIndex
  81.         If x = 1 Then
  82.             AppID = 15
  83.         ElseIf x = 2 Then
  84.             AppID = 16
  85.         ElseIf x = 3 Then
  86.             AppID = 17
  87.         ElseIf x = 4 Then
  88.             AppID = 18
  89.         End If
  90.  
  91.         strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
  92.         sqlConn = New SqlConnection(strConnection)
  93.         MyCommand.Connection = sqlConn
  94.         MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = '" & AppID & "')ORDER BY dbo.HR_Master.SurName, dbo.HR_Master.ForeName, Department"
  95.         MyDA.SelectCommand = MyCommand
  96.         myDS.Clear()
  97.         MyDA.Fill(myDS, "training")
  98.         oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
  99.         oRpt.SetDataSource(myDS)
  100.         CrystalReportViewer1.ReportSource = oRpt
  101.  
  102.  
  103.         p1 = New ParameterField()
  104.         pd1 = New ParameterDiscreteValue()
  105.         p1.ParameterFieldName = "year"
  106.         If x = 1 Then
  107.             pd1.Value = "2008"
  108.         ElseIf x = 2 Then
  109.             pd1.Value = "2009"
  110.         ElseIf x = 3 Then
  111.             pd1.Value = "2010"
  112.         ElseIf x = 4 Then
  113.             pd1.Value = "2011"
  114.         End If
  115.         p1.CurrentValues.Add(pd1)
  116.         ParaFields.Add(p1)
  117.         CrystalReportViewer1.ParameterFieldInfo = ParaFields
  118.  
  119.  
  120.     End Sub
  121.     Sub RefreshReport()
  122.  
  123.         x = ddlTraining.SelectedIndex
  124.         If x = 1 Then
  125.             AppID = 15
  126.             yr = 2008
  127.         ElseIf x = 2 Then
  128.             AppID = 16
  129.             yr = 2009
  130.         ElseIf x = 3 Then
  131.             AppID = 17
  132.             yr = 2010
  133.         ElseIf x = 4 Then
  134.             AppID = 18
  135.             yr = 2011
  136.         End If
  137.  
  138.         c = ddlsbu.SelectedIndex
  139.         If c = 1 Then
  140.             comp = "ACTIVELINE LTD"
  141.         ElseIf c = 2 Then
  142.             comp = "HAREL MALLAC CO LTD (MAN.)"
  143.         ElseIf c = 3 Then
  144.             comp = "HM ENGINEERING LTD (MAN)"
  145.         ElseIf c = 4 Then
  146.             comp = "HM BUREAUTIQUE (MAN)"
  147.         ElseIf c = 5 Then
  148.             comp = "HAREL MALLAC CO LTD"
  149.         ElseIf c = 6 Then
  150.             comp = "HAREL MALLAC TRAVEL & LEISURE"
  151.         ElseIf c = 7 Then
  152.             comp = "HAREL MALLAC AVIATION LTD"
  153.         ElseIf c = 8 Then
  154.             comp = "HM ENGINEERING LTD."
  155.         ElseIf c = 9 Then
  156.             comp = "HM BUREAUTIQUE LTD."
  157.         ElseIf c = 10 Then
  158.             comp = "MCS EXECUTIVES"
  159.         ElseIf c = 11 Then
  160.             comp = "MAURITIUS COMPUTING SERVICES"
  161.         ElseIf c = 12 Then
  162.             comp = "HM TECHNOLOGIES (MANAGEMENT)"
  163.         ElseIf c = 13 Then
  164.             comp = "HM TECHNOLOGIES"
  165.         End If
  166.         strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
  167.         sqlConn = New SqlConnection(strConnection)
  168.         MyCommand.Connection = sqlConn
  169.         MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = '" & AppID & "' and HR_company.CmpName = '" & comp & "')ORDER BY HR_company.CmpName, HR_Master.SurName, HR_Master.ForeName, Department"
  170.         MyDA.SelectCommand = MyCommand
  171.         myDS.Tables("finalrating").Clear()
  172.         MyDA.Fill(myDS, "finalrating")
  173.         oRpt.Load("C:\Inetpub\wwwroot\HR\reports\FinalRating.rpt")
  174.         oRpt.SetDataSource(myDS)
  175.         CrystalReportViewer1.ReportSource = oRpt
  176.  
  177.  
  178.         p1 = New ParameterField()
  179.         pd1 = New ParameterDiscreteValue()
  180.         p1.ParameterFieldName = "year"
  181.         If x = 1 Then
  182.             pd1.Value = "2008"
  183.         ElseIf x = 2 Then
  184.             pd1.Value = "2009"
  185.         ElseIf x = 3 Then
  186.             pd1.Value = "2010"
  187.         ElseIf x = 4 Then
  188.             pd1.Value = "2011"
  189.         End If
  190.         p1.CurrentValues.Add(pd1)
  191.         ParaFields.Add(p1)
  192.         CrystalReportViewer1.ParameterFieldInfo = ParaFields
  193.  
  194.  
  195.     End Sub
  196.     Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
  197.  
  198.         oRpt.Close()
  199.         oRpt.Dispose()
  200.     End Sub
  201.  
  202.     Protected Sub ddlsbu_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlsbu.SelectedIndexChanged
  203.  
  204.         RefreshReport()
  205.  
  206.     End Sub
  207.  
  208. End Class
  209.  
  210.  
How do I refresh the whole report on selectedindexchange?

How do I export the current report to excel?

Thanking you in advance for your help
Oct 26 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.