Updating Crystal Report Data Source at runtime

[Former Member]

Hello Everyone,

I am using Microsoft Visual Studio 2010 with SAP Crystal Reports , version for Microsoft Studio (13.0.10) and  InterSystems CACHE 2014  as a back-end database.

I have a simple aspx page with a Crystal Report Viewer on it and I load a crystal report into that viewer using Report Document.

It works OK but I have a small issue.

If I try to load a report into the viewer which was created using a different Datasource than the one using which I try to load the report then it doesn't work. Basically I am unable to change the datasource of the report at runtime.

My crystal reports are created using Crystal Reports 2008 (and I have full version of it installed on my development machine).  I have this issue on both development and production machines.

E.g.  I have a Members.rpt which was created using datasource "ABC"  but when loading that report into Crystal Report viewer on my aspx page, I use datasource "XYZ"   , in this case "Crystal report viewer" doesn't throw any error but  doesn't load data either.

If I had created "Members.rpt" using datasource "XYZ"  then it would load data correctly when loaded into Crystal Report Viewer.

I also tried using  "Dataset"  as a Crystal report datasource.  i.e.  load a Dataset first by running a query and then assign datasource to report document datasource.  In this case it gives me

Failed to open the connection. Failed to open the connection. Members {D07CE43F-7143-40B6-B7BF-DD2749508B4F}.rpt

Here is the sample code, (sample code is not using Dataset)

//assemblies used

using InterSystems.Data.CacheClient;

using InterSystems.Data.CacheTypes;

ViewCrystalReport()

{

          string FullReportPath = "C:\Members.rpt";

           CrystalDecisions.CrystalReports.Engine.ReportDocument  rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

            rpt.Load(FullReportPath);

            ConnectionInfo connectionInfo = new ConnectionInfo();

            connectionInfo = GetConnectionStringDetails();

            for (int i = 0; i < CrystalReportViewer2.LogOnInfo.Count; i++)

            {

                CrystalReportViewer2.LogOnInfo[i].ConnectionInfo = connectionInfo;

            }

            Tables CRepTBLS = rpt.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table repTable in CRepTBLS)

            {

                TableLogOnInfo login = repTable.LogOnInfo;

                login.ConnectionInfo = connectionInfo;

                repTable.ApplyLogOnInfo(login);

            }

            rpt.Refresh();

            //Simply display Report in Report Viewer

            CrystalReportViewer2.ReportSource = rpt;

            CrystalReportViewer2.DataBind();

}

private ConnectionInfo GetConnectionStringDetails()

    {

        string conn = "Server = localhost; Port = 1972; Namespace = XYZ; Password = XXX; User ID = XXXXXX"; // (string)ConfigurationManager.AppSettings["CacheConnectionString"])

        ConnectionInfo connectionInfo = new ConnectionInfo();

        connectionInfo.AllowCustomConnection = true;

        connectionInfo.IntegratedSecurity = false;

        string[] connDetails = conn.Split(';');

        for (int i = 0; i < connDetails.Length; i++)

        {

            string item = connDetails[i].Substring(0, connDetails[i].IndexOf(@"="));

            string value = connDetails[i].Substring(connDetails[i].IndexOf(@"=") + 1);

            switch (item.Trim().ToUpper())

            {

                case "NAMESPACE":

                        connectionInfo.ServerName = value.Trim();

                        break;

                case "USER ID":

                        connectionInfo.UserID = value.Trim();

                         break;

                case "PASSWORD":

                        connectionInfo.Password = value.Trim();

                        break;

                default:

                    break;

              }

        }

        return connectionInfo;

    }

Any help will be really appreciated.

Regards,

Utsavi

[former_member11696 · Accepted solution]

Comment out:

rpt.Refresh();

That uses the existing connection info.

Just preview and it should show the new Datasource.

What happens if you hit the viewers Refresh button?

Don

[Former Member · Accepted solution]

Hi Don,

Commenting out  "rpt.Refresh()";  doesn't make any difference.

I also tried hitting CrystalReportViewer.RefreshReport() but no change.

Utsavi

[Former Member · Accepted solution]

Hi Don,

Did you have any more information on this?  I still haven't been able to find a fix for this.

Thanks,

Utsavi

[Former Member · Accepted solution]

Does anyone else have any information on this?  I am still struggling to get this to work. 

Regards.

[former_member183750 · Accepted solution]

How about the utility attached to the following KBA. It will write out the code for you:

1553921 - Is there a utility that would help in writing database logon code?

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Thank you so much Ludek.  You are a legend.   

That utility helped solve my problem.  It generated a piece of code which is a bit different approach than what I had written , it correctly updates the ODBC datasource at runtime.  (I have modified the code a bit. The utility generates code specific to report  (with table details etc), I have modified it because in my case, I don't know what reports User will be uploading and running. 

I will paste my code here in case it helps anyone else.

It all works well now on my machine, will try it on client's soon. 

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

private void ViewCrystalReport()

    {

        try

        {

           CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

            rpt.Load(FullReportPath);

            //Create a new Database Table to replace the reports current table.

            CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

            //boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

            PropertyBag boMainPropertyBag = new PropertyBag();

            //boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

            //In the main property bag (boMainPropertyBag)

            PropertyBag boInnerPropertyBag = new PropertyBag();

            //Set the attributes for the boInnerPropertyBag

            boInnerPropertyBag.Add("DSN", "TEST");

            boInnerPropertyBag.Add("UseDSNProperties", "False");

            //Set the attributes for the boMainPropertyBag

            boMainPropertyBag.Add("Database DLL", "crdb_odbc.dll");

            boMainPropertyBag.Add("QE_DatabaseName", "");

            boMainPropertyBag.Add("QE_DatabaseType", "ODBC (RDO)");

            //Add the QE_LogonProperties we set in the boInnerPropertyBag Object

            boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

            boMainPropertyBag.Add("QE_ServerDescription", "TEST");

            boMainPropertyBag.Add("QE_SQLDB", "True");

            boMainPropertyBag.Add("SSO Enabled", "False");

            //Create a new ConnectionInfo object

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

            new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

            //Pass the database properties to a connection info object

            boConnectionInfo.Attributes = boMainPropertyBag;

            //Set the connection kind

            boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

            //**EDIT** Set the User Name and Password if required.

            boConnectionInfo.UserName = "TEST";

            boConnectionInfo.Password = "TEST";

            //Pass the connection information to the table

            boTable.ConnectionInfo = boConnectionInfo;

            //Get the Database Tables Collection for your report

            CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

            boTables = rpt.ReportClientDocument.DatabaseController.Database.Tables;

            //For each table in the report:

            // - Set the Table Name properties.

            // - Set the table location in the report to use the new modified table

             for (int i = 0; i < boTables.Count; i++)

            {

                boTable.Name = rpt.ReportClientDocument.DatabaseController.Database.Tables[i].Name;

                boTable.QualifiedName = rpt.ReportClientDocument.DatabaseController.Database.Tables[i].QualifiedName;

                boTable.Alias = rpt.ReportClientDocument.DatabaseController.Database.Tables[i].Alias;

                rpt.ReportClientDocument.DatabaseController.SetTableLocation(boTables[i], boTable);

            }

            //Verify the database after adding substituting the new table.

            //To ensure that the table updates properly when adding Command tables or Stored Procedures.

            rpt.VerifyDatabase();

            //Simply display Report in Report Viewer

            CrystalReportViewer2.ReportSource = rpt;

        }

        catch (Exception ex)

        {

             ShowMessage(ex.Message);

        }

    }

[former_member183750 · Accepted solution]

This is just perfect and exactly how SCN is supposed to work . Sharing the knowledge to resolve our common issues. Many thanks for putting your solution here.

- Ludek