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 selectedindexch anged 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 crystalreportvi ewer toolbar it re-load the initial report.
Here is my code:
How do I refresh the whole report on selectedindexch ange?
How do I export the current report to excel?
Thanking you in advance for your help
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 selectedindexch anged 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 crystalreportvi ewer toolbar it re-load the initial report.
Here is my code:
Code:
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine.ReportDocument
Partial Class RptTraining
Inherits System.Web.UI.Page
Dim oRpt As New ReportDocument()
Dim sqlConn As New SqlConnection
Dim strConnection As String
Dim MyCommand As New SqlClient.SqlCommand()
Dim MyDA As New SqlClient.SqlDataAdapter()
Dim myDS As New DataSet3()
Dim x As Integer
Dim AppID As Integer
Dim yr As String
Dim c As String
Dim comp As String
Dim ParaFields As New ParameterFields()
Dim p1 As New ParameterField()
Dim pd1 As New ParameterDiscreteValue()
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
BindReport()
End Sub
Sub BindReport()
x = ddlTraining.SelectedIndex
If x = 1 Then
AppID = 15
ElseIf x = 2 Then
AppID = 16
ElseIf x = 3 Then
AppID = 17
ElseIf x = 4 Then
AppID = 18
End If
strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
sqlConn = New SqlConnection(strConnection)
MyCommand.Connection = sqlConn
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"
MyDA.SelectCommand = MyCommand
MyDA.Fill(myDS, "training")
oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
oRpt.SetDataSource(myDS)
CrystalReportViewer1.ReportSource = oRpt
p1 = New ParameterField()
pd1 = New ParameterDiscreteValue()
p1.ParameterFieldName = "year"
pd1.Value = "2008"
p1.CurrentValues.Add(pd1)
ParaFields.Add(p1)
CrystalReportViewer1.ParameterFieldInfo = ParaFields
End Sub
Protected Sub ddlTraining_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTraining.SelectedIndexChanged
ConfigureReport()
End Sub
Sub ConfigureReport()
x = ddlTraining.SelectedIndex
If x = 1 Then
AppID = 15
ElseIf x = 2 Then
AppID = 16
ElseIf x = 3 Then
AppID = 17
ElseIf x = 4 Then
AppID = 18
End If
strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
sqlConn = New SqlConnection(strConnection)
MyCommand.Connection = sqlConn
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"
MyDA.SelectCommand = MyCommand
myDS.Clear()
MyDA.Fill(myDS, "training")
oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
oRpt.SetDataSource(myDS)
CrystalReportViewer1.ReportSource = oRpt
p1 = New ParameterField()
pd1 = New ParameterDiscreteValue()
p1.ParameterFieldName = "year"
If x = 1 Then
pd1.Value = "2008"
ElseIf x = 2 Then
pd1.Value = "2009"
ElseIf x = 3 Then
pd1.Value = "2010"
ElseIf x = 4 Then
pd1.Value = "2011"
End If
p1.CurrentValues.Add(pd1)
ParaFields.Add(p1)
CrystalReportViewer1.ParameterFieldInfo = ParaFields
End Sub
Sub RefreshReport()
x = ddlTraining.SelectedIndex
If x = 1 Then
AppID = 15
yr = 2008
ElseIf x = 2 Then
AppID = 16
yr = 2009
ElseIf x = 3 Then
AppID = 17
yr = 2010
ElseIf x = 4 Then
AppID = 18
yr = 2011
End If
c = ddlsbu.SelectedIndex
If c = 1 Then
comp = "ACTIVELINE LTD"
ElseIf c = 2 Then
comp = "HAREL MALLAC CO LTD (MAN.)"
ElseIf c = 3 Then
comp = "HM ENGINEERING LTD (MAN)"
ElseIf c = 4 Then
comp = "HM BUREAUTIQUE (MAN)"
ElseIf c = 5 Then
comp = "HAREL MALLAC CO LTD"
ElseIf c = 6 Then
comp = "HAREL MALLAC TRAVEL & LEISURE"
ElseIf c = 7 Then
comp = "HAREL MALLAC AVIATION LTD"
ElseIf c = 8 Then
comp = "HM ENGINEERING LTD."
ElseIf c = 9 Then
comp = "HM BUREAUTIQUE LTD."
ElseIf c = 10 Then
comp = "MCS EXECUTIVES"
ElseIf c = 11 Then
comp = "MAURITIUS COMPUTING SERVICES"
ElseIf c = 12 Then
comp = "HM TECHNOLOGIES (MANAGEMENT)"
ElseIf c = 13 Then
comp = "HM TECHNOLOGIES"
End If
strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
sqlConn = New SqlConnection(strConnection)
MyCommand.Connection = sqlConn
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"
MyDA.SelectCommand = MyCommand
myDS.Tables("finalrating").Clear()
MyDA.Fill(myDS, "finalrating")
oRpt.Load("C:\Inetpub\wwwroot\HR\reports\FinalRating.rpt")
oRpt.SetDataSource(myDS)
CrystalReportViewer1.ReportSource = oRpt
p1 = New ParameterField()
pd1 = New ParameterDiscreteValue()
p1.ParameterFieldName = "year"
If x = 1 Then
pd1.Value = "2008"
ElseIf x = 2 Then
pd1.Value = "2009"
ElseIf x = 3 Then
pd1.Value = "2010"
ElseIf x = 4 Then
pd1.Value = "2011"
End If
p1.CurrentValues.Add(pd1)
ParaFields.Add(p1)
CrystalReportViewer1.ParameterFieldInfo = ParaFields
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
oRpt.Close()
oRpt.Dispose()
End Sub
Protected Sub ddlsbu_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlsbu.SelectedIndexChanged
RefreshReport()
End Sub
End Class
How do I export the current report to excel?
Thanking you in advance for your help