Dynamic Source

[venerable bede]

How the hell do I make the source for a CrystalReportViewer control Dynamic.

When I tie a front-end to the back-end dfatabase I write the connection string away to a text file and use that everytime the program is run.

How do I do this for a crystal report?

[Lee Dale]

your database in your report has to be an active data source

you then set the datasource of the report viewer to a recordset object that matches your active datasource in your report

here is how i created a dynamic report

Dim oContract As ChurSMS.clsContract
Dim rptJPM As New rptJobsPerMonth
Dim rptHPM As New rptHoursPerMonth
Dim rstStatsPerMonth As ADODB.Recordset
Dim rstMonths As ADODB.Recordset
Dim iCount As Integer, iSelectedYear As Integer, fJobs As Single
Dim vntFields As Variant
Dim sMonthName As String

iSelectedYear = iYear
Set oContract = CreateObject("ChurSMS.clsContract")
Select Case iType
Case Is = 0
Set rstStatsPerMonth = oContract.GetJobsPerMonth(txtStaffID, "1/1/" & iYear, "31/12/" & iYear)
Case Is = 1
Set rstStatsPerMonth = oContract.GetHoursPerMonth(txtStaffID, "1/1/" & iYear, "31/12/" & iYear)
End Select
Set rstMonths = CreateObject("ADODB.Recordset")
rstMonths.Fields.Append "JobMonth", adInteger
rstMonths.Fields.Append "Jobs", adInteger
rstMonths.Fields.Append "MonthName", adVarChar, 50
rstMonths.Open
iCount = 1
vntFields = Array("JobMonth", "Jobs", "MonthName")
If Not rstStatsPerMonth.BOF Then
Do Until iCount = 13
fJobs = 0
rstStatsPerMonth.MoveFirst
Do Until rstStatsPerMonth.EOF
If iCount = rstStatsPerMonth.Fields("JobMonth").Value Then
Select Case iType
Case Is = 0
fJobs = rstStatsPerMonth.Fields("CountJobs").Value
Case Is = 1
fJobs = rstStatsPerMonth.Fields("Hours").Value
End Select
End If
rstStatsPerMonth.MoveNext
Loop
Select Case iCount
Case Is = 1
sMonthName = "Jan"
Case Is = 2
sMonthName = "Feb"
Case Is = 3
sMonthName = "Mar"
Case Is = 4
sMonthName = "Apr"
Case Is = 5
sMonthName = "May"
Case Is = 6
sMonthName = "Jun"
Case Is = 7
sMonthName = "Jul"
Case Is = 8
sMonthName = "Aug"
Case Is = 9
sMonthName = "Sep"
Case Is = 10
sMonthName = "Oct"
Case Is = 11
sMonthName = "Nov"
Case Is = 12
sMonthName = "Dec"
End Select
rstMonths.AddNew vntFields, Array(iCount, fJobs, sMonthName)
iCount = iCount + 1
Loop
End If
Select Case iType
Case Is = 0
rptJPM.Database.SetDatasource rstMonths
crvContract.ReportSource = rptJPM
rptJPM.ReadRecords
Case Is = 1
rptHPM.Database.SetDatasource rstMonths
crvContract.ReportSource = rptHPM
rptHPM.ReadRecords
End Select
crvContract.ViewReport