It’s a known fact that in Crystal Reports, if you try to display details, using data in a SQL Server database other than the one that you’ve used to design the report, either you have to set the database location or refresh the report. This is a common issue that development teams face when reports are being deployed to the production environment.
But this can be prevented using the following method.
The following namespaces are required:
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
And we have to assign the required SQL Server information to each table/view of the report, sub reports, and the report viewer.
Use the following code:
SQLReport report = new SQLReport();
string zServer = @"SERVER_NAME";
string zDatabase = @"DATABASE";
string zUsername = @"USER";
string zPassword = @"PASSWORD";
ConnectionInfo ciReportConnection = new ConnectionInfo();
ciReportConnection.ServerName = zServer;
ciReportConnection.DatabaseName = zDatabase;
ciReportConnection.UserID = zUsername;
ciReportConnection.Password = zPassword;
foreach (Table table in report.Database.Tables) {
table.LogOnInfo.ConnectionInfo = ciReportConnection;
table.ApplyLogOnInfo(table.LogOnInfo);
}
foreach (ReportDocument subrep in report.Subreports) {
foreach (Table table in subrep.Database.Tables) {
table.LogOnInfo.ConnectionInfo = ciReportConnection;
table.ApplyLogOnInfo(table.LogOnInfo);
}
}
if (this.crystalReportViewer1.LogOnInfo != null) {
TableLogOnInfos tlInfo = this.crystalReportViewer1.LogOnInfo;
foreach (TableLogOnInfo tbloginfo in tlInfo) {
tbloginfo.ConnectionInfo = ciReportConnection;
}
}
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.Refresh();
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports