I generate an SQL statement, fill a dataset with the results, then run an write XML doc from the dataset, then bind the XML to the Crystal report. When someone changes a parameter and runs the procedure again, it recreates everything again. Sounds slow. Please see my procedure below and comment. I let the user build their reports requirements from a form, then send the values to this Function. This is my first Dot Net try, so Im sure it isnt pretty. Thanks All, Eric. Code below
Public Function ProcessExpense(ByVal StartDate As Date, ByVal EndDate As Date, ByVal XMLPath As String, ByVal GroupOnOffice As Boolean, ByVal Officeselected As String
Dim sqlExpense As Strin
Dim adoOleDbConnection As OleDbConnectio
Dim adoOleDbDataAdapter As OleDbDataAdapte
Dim dataSet As DataSe
Dim connectionString As String = "
Dim crDTEReportOffice As New DTEExpenseOffic
Dim crDTEReportName As New DTEExpenseNam
StartDate = Format(StartDate, "MM/dd/yyyy"
EndDate = Format(EndDate, "MM/dd/yyyy"
connectionString = "Provider=SQLOLEDB;
connectionString += "Server=dt0101;Database=dte2002;
connectionString += "User ID=sa;Password=
''Create and open a connection using the connection strin
adoOleDbConnection = New OleDbConnection(connectionString
'Expense(SQL
sqlExpense = "SELECT '" & StartDate & "' as StartDate,'" & EndDate & "' as EndDate,timekeepers.name AS Name, dtetime.timekeeper AS TimeKeeper#, (CASE timekeepers.office
sqlExpense = sqlExpense & "WHEN '01' THEN 'Omaha'
sqlExpense = sqlExpense & "WHEN '02' THEN 'Denver'
sqlExpense = sqlExpense & "WHEN '03' THEN 'Washington DC'
sqlExpense = sqlExpense & "WHEN '04' THEN 'Atlanta'
sqlExpense = sqlExpense & "WHEN '05' THEN 'Scottsdale'
sqlExpense = sqlExpense & "WHEN '06' THEN 'Pasadena'
sqlExpense = sqlExpense & "WHEN '09' THEN 'Des Moines'
sqlExpense = sqlExpense & "WHEN '10' THEN 'Arkansas'
sqlExpense = sqlExpense & "WHEN '11' THEN 'Kansas City'
sqlExpense = sqlExpense & "WHEN '12' THEN 'Wichita'
sqlExpense = sqlExpense & "WHEN '14' THEN 'Richmond'
sqlExpense = sqlExpense & "WHEN '18' THEN 'Chicago'
sqlExpense = sqlExpense & "WHEN '17' THEN 'Irvine'
sqlExpense = sqlExpense & "WHEN '19' THEN 'Lincoln'
sqlExpense = sqlExpense & "WHEN '21' THEN 'Oklahoma City'
sqlExpense = sqlExpense & "WHEN '97' THEN 'National'
sqlExpense = sqlExpense & "ELSE timekeepers.office end)AS Office,
sqlExpense = sqlExpense & "dtetime.isexpense, CONVERT(decimal(10, 2), dtetime.hours) AS Amount, dtetime.client AS Client, dtetime.matter AS Matter,
sqlExpense = sqlExpense & "convert(varchar(50),dtetime.workdate,101) AS WorkDate
sqlExpense = sqlExpense & "FROM dtetime INNER JOIN
sqlExpense = sqlExpense & "timekeepers ON dtetime.timekeeper = timekeepers.timekeeper
sqlExpense = sqlExpense & "WHERE (dtetime.isexpense = 'Y') and timekeepers.timekeeper <> '22222' and timekeepers.timekeeper <> '22223' and timekeepers.timekeeper <> '22224'and timekeepers.timekeeper <> '22225'and timekeepers.timekeeper <> '22226'and timekeepers.timekeeper <> '22227'and timekeepers.timekeeper <> '22231'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22230'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22228'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22229'
If Officeselected = "timekeepers.office" The
sqlExpense = sqlExpense & "and timekeepers.office = timekeepers.office
Els
sqlExpense = sqlExpense & "and timekeepers.office = '" & Officeselected & "'
End I
sqlExpense = sqlExpense & " and dtetime.released = 'N'
sqlExpense = sqlExpense & "and workdate between '" & StartDate & "' and '" & EndDate & "' ORDER BY DATEPART(yyyy, workdate), DATEPART(mm, workdate), DATEPART(dd, workdate)
''Retrieve the data using the SQL statement and existing connection
adoOleDbDataAdapter = New OleDbDataAdapter(sqlExpense, adoOleDbConnection)
''Create a instance of a Dataset
dataSet = New DataSet
''Fill the dataset with the data retrieved. The name of the table
''in the dataset must be the same as the table name in the report.
Try
adoOleDbDataAdapter.Fill(dataSet, "DTEExpense")
Catch objectDataFill As Exception
'Throw objectDBConnect
MsgBox(objectDataFill.Message)
End Try
''Create an instance of the strongly-typed report object
dataSet.WriteXml(XMLPath, XmlWriteMode.WriteSchema)
''Pass the populated dataset to the report
If GroupOnOffice = True Then
crDTEReportOffice.SetDataSource(dataSet)
ProcessExpense = crDTEReportOffice
Else
crDTEReportName.SetDataSource(dataSet)
ProcessExpense = crDTEReportName
End If
''Set the viewer to the report object to be previewed.
adoOleDbConnection.Close()
adoOleDbConnection = Nothing