Visual Studio 2010 & Crystal Reports - change server at runtime

What? This isn't a SharePoint topic! You're right...every so often have to stretch a little bit.

So I have an ASP.Net web app and needed to add in a report. I didn't want to hand code it, and I had looked at using Crystal Reports for a different project & liked how nicely it integrates with Visual Studio. EZ-PZ to deploy it to the server, just install the runtime, deploy the project and we have reports!

Then came time to get the project up to production. I installed the runtime for Crystal Reports, clicked the Report link,and got the dreaded Database Login failed. Hmmm - I had updated the web.config appsettings to point to the Production SQL Server instead of my dev box.

That wasn't enough. Found a few entries suggesting to use the CrystalDecisions ConnectionInfo class, then another page saying to apply this change to all of the tables & subtables on the report, but that didn't help me - still got that logon error.

I ended up going the route of populating a data table with my report data, then using this table in the report - made things easier.

One other twist, I then needed to export this as a PDF, but I have SSL enabled; had to add in a few more lines of code. Take a look! And yes, this is VB.Net - it's an inherited project, too many lines to convert over!

Dim dt As DataTable

dt = BindReport()

rpt.SetDataSource(dt)

Dim reportDate As DateTime = Session("CurrentPayPeriod")

' Tell the browser this is a PDF document so it will use an appropriate viewer.
Response.Buffer = True
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"


rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, False, "Timesheet Report")

Dim conString As String = System.Configuration.ConfigurationSettings.AppSettings("connectString")
Dim con As SqlConnection = New SqlConnection(conString)
con.Open()
Dim cmd As New SqlCommand("usp_GetReportData", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@SelectedDate", SqlDbType.Date).Value = CDate(Session("ReportPeriod"))
cmd.Prepare()

Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter(cmd)

Dim dtReport As DataTable = New DataTable()
sqlAdapter.Fill(dtReport)
con.Close()

Return dtReport
End Function