Crystal Reports: Dynamic data – Static schema

With crystal reports, the design of the report is strongly tied to underlying table’s schema. The schema can not be attached to the report or altered during runtime. The crystal report needs to know the table structure , field names to display, statically. Then during runtime , those fields can be populated with data from any source — say a  table (having same schema) residing in database, or objects like DataSet/DataTable (having same structure/schema) or from any .xsd file, or whatever available options are. The point is, the data can be fetched from any source, — only the structure/names of table/fields (ie schema) needs to be exact-match with the report’s static schema, finalized during report’s design phase. Hence things like, binding a dynamically generated dataset (having dynamic field-names), to a blank crystal report is not possible. Below are 2 examples, one with DataTable and another with a regular table present in SQL Server database. In both the cases, the report schema being same, when I switch the environment from ‘dev’ to ‘test’ and vice-versa. the report fetches data from the corresponding database/source.

With DataTable

protected void btnTest_Click(object sender, EventArgs e)
{
    getReport(false);
}
protected void btnDev_Click(object sender, EventArgs e)
{
    getReport(true);
}
public void getReport(bool nDevEnv)
{
    ReportDocument cryRpt = new ReportDocument();
    DataTable dt = getAllOrders(nDevEnv);
    cryRpt.Load(Server.MapPath("~/MyReports/CrystalReportDevTest.rpt"));
    cryRpt.SetDataSource(dt);
    CrystalReportViewer1.ReportSource = cryRpt;
    CrystalReportViewer1.RefreshReport();
}
public DataTable getAllOrders(bool nDevEnv)
{
 string sqlCon = "";
 sqlCon = nDevEnv ? "DEV CONNECTION STRING" :
                    "TEST CONNECTION STRING";
 using (SqlConnection c = new SqlConnection(sqlCon))
 {
    c.Open();
    using (SqlDataAdapter a = new SqlDataAdapter("SELECT Product_name,Product_price FROM Product", c))
    {
        DataTable t = new DataTable();
        a.Fill(t);
        return t;
    }
 }
}

With Database

protected void btnTest_Click(object sender, EventArgs e)
{
    GenerateReport(false);
}
protected void btnDev_Click(object sender, EventArgs e)
{
    GenerateReport(true);
}
private void GenerateReport(bool IsDev)
{
    ReportDocument cryRpt = new ReportDocument();
    cryRpt.Load(Server.MapPath("~/MyReports/CrystalReportDevTestDB.rpt"));
    ConnectToEnv(cryRpt, IsDev);
    CrystalReportViewer1.ReportSource = cryRpt;
    CrystalReportViewer1.RefreshReport();
}
private ReportDocument ConnectToEnv(ReportDocument cryRpt, bool IsDev)
{
    TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
    ConnectionInfo crConnectionInfo = new ConnectionInfo();
    Tables CrTables;

    if (IsDev)
    {
        crConnectionInfo.ServerName = "DEV Server name";
        crConnectionInfo.DatabaseName = "DEV DB Name";
        crConnectionInfo.UserID = "gTest1";
        crConnectionInfo.Password = "pwd1";
    }
    else
    {
        crConnectionInfo.ServerName = "TEST Server name";
        crConnectionInfo.DatabaseName = "TEST DB Name";
        crConnectionInfo.UserID = "gTest2";
        crConnectionInfo.Password = "pwd1";
    }
    CrTables = cryRpt.Database.Tables;

    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = crConnectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
    }
    return cryRpt;
}

Thanks for reading.

Advertisements
This entry was posted in Crystal Reports. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s