Setting subreport connection info at runtime

[Former Member]

I'm using the code generated by the RAS Connection Info utility () to set the connection info at runtime. This works fine for simple reports, but it does not work for reports that contain subreports. The utility does not generate any code to set the subreport connection info.

How do I set the connection info for subreports?

I have tried using the same method of looping through ReportClientDocument.DatabaseController.Database.Tables and calling SetTableLocation to update the connection info for each one, but i get a "Not supported within subreports" exception.

[Former Member · Accepted solution]

Hello Colin

Your solution is exactly what I was missing for sub reports

After .. no joke ... a week lost on trying to set connection on a report using 2 databases with ApplyLogOnInfo() I finaly switch to the CrystalDecisions.ReportAppServer.DataDefModel model instead of the CrystalDecisions.CrystalReports.Engine model.

That CrystalDecisions.ReportAppServer.DataDefModel does not make use of the buggy ApplyLogOnInfo (even in VS2010 SP2)

Buggy in the sense it is incompatible with 2 database in the same rerport.

So my code works very well now when thare were no sub reports.

With you suggestion I was able to set ONE subreport but when I loop the the second, it seems that something change in the sub report collection since a get a System.NullReferenceException: Object reference not set to an instance of an object

The loop I am making ran well with the CrystalDecisions.CrystalReports.Engine model.

Console.WriteLine("Setting connection info of main report");
            Console.WriteLine("======================================");
            SetTablesLogonInfo(Report);

            
            // Setting connection info of all sub-reports
            CrystalDecisions.CrystalReports.Engine.Sections crSections = Report.ReportDefinition.Sections;
            CrystalDecisions.CrystalReports.Engine.Tables crTables;
            CrystalDecisions.CrystalReports.Engine.Database crDatabase = Report.Database;
            CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
            CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
            CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
            crTables = crDatabase.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
            {
                crReportObjects = crSection.ReportObjects;
                foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
                {
                    if (crReportObject.Kind == ReportObjectKind.SubreportObject)
                    {
                        crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
                        crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

                        if (crSubreportDocument.Database.Tables.Count > 0)
                        {
                            Console.WriteLine("");
                            Console.WriteLine("Setting connection info of sub-report '" + crSubreportDocument.Name + "'");
                            Console.WriteLine("====================================================================");
                            SetTablesLogonInfo(crSubreportDocument);
                        }
                    }
                }
            }           

       ... later

                CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
                if (currentReport.IsSubreport)
                    boTables = Report.ReportClientDocument.SubreportController.GetSubreportDatabase(currentReport.Name).Tables;
                else
                    boTables = currentReport.ReportClientDocument.DatabaseController.Database.Tables;

     ... later

          if (currentReport.IsSubreport)
                 Report.ReportClientDocument.SubreportController.SetTableLocation(currentReport.Name,boTables[iBoTable], boTable);
         else
                 currentReport.ReportClientDocument.DatabaseController.SetTableLocation(boTables[iBoTable], boTable);

Can you supply me with the loop you make to iterate through each sub report.?

I should probably not mix the CrystalDecisions.ReportAppServer.DataDefModel model AND the CrystalDecisions.CrystalReports.Engine model.

Thank you

[former_member11696 · Accepted solution]

Hi Guys,

This just rung a bell, I never mentioned it before because it hasn't come up since SP1. Try logging onto your subreports first and then log onto the main report.

And to confirm, use the Engine to open the report and then use RAS to make changes. You can use both, just verify the change are accepted.

Here's code to do it all, just change the order to get the subreport objects, set location and then set log on for the main report. This assumes all tables in each subreport is the same.

private void SetLogonInfo_Click(object sender, EventArgs e)
{
    rptClientDoc.DatabaseController.SetTableLocationByServerDatabaseName("Xtreme", "192.168.43.128", "TestDB", "sb", "PW");


    CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
    CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
    CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
    CrystalDecisions.CrystalReports.Engine.Database crDatabase;
    CrystalDecisions.CrystalReports.Engine.Tables crTables;
    TableLogOnInfo crTableLogOnInfo;

    CrystalDecisions.Shared.ConnectionInfo crConnectioninfo = new CrystalDecisions.Shared.ConnectionInfo();

    //pass the necessary parameters to the connectionInfo object
    crConnectioninfo.ServerName = "192.168.43.128";
    crConnectioninfo.UserID = "sb";
    crConnectioninfo.Password = "pw";
    crConnectioninfo.DatabaseName = "Xtreme";

    //set up the database and tables objects to refer to the current report
    crDatabase = rpt.Database;
    crTables = crDatabase.Tables;

    //loop through all the tables and pass in the connection info
    foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
    {
        crTableLogOnInfo = crTable.LogOnInfo;
        crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
        crTable.ApplyLogOnInfo(crTableLogOnInfo);
    }

    //set the crSections object to the current report's sections
    CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;

    //loop through all the sections to find all the report objects
    foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
    {
        crReportObjects = crSection.ReportObjects;
        //loop through all the report objects to find all the subreports
        foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
        {
            if (crReportObject.Kind == ReportObjectKind.SubreportObject)
            {
                //you will need to typecast the reportobject to a subreport 
                //object once you find it
                crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;

                //open the subreport object
                crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

                //set the database and tables objects to work with the subreport
                crDatabase = crSubreportDocument.Database;
                crTables = crDatabase.Tables;

                //loop through all the tables in the subreport and 
                //set up the connection info and apply it to the tables
                foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
                {
                    crConnectioninfo.ServerName = "192.168.43.128";
                    crConnectioninfo.UserID = "sb";
                    crConnectioninfo.Password = "pw";
                    crConnectioninfo.DatabaseName = "Xtreme";

                    crTableLogOnInfo = crTable.LogOnInfo;
                    crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
                    crTable.ApplyLogOnInfo(crTableLogOnInfo);
                }
            }
        }
    }

    //bool myTEst = rptClientDoc.DatabaseController.VerifyTableConnectivity("Orders");

    GroupPath gp = new GroupPath();
    string tmp = String.Empty;
    try
    {
        rptClientDoc.RowsetController.GetSQLStatement(gp, out tmp);
        btnSQLStatement.Text = tmp;
    }
    catch (Exception ex)
    {
        btnSQLStatement.Text = "ERROR: " + ex.Message;
        return;
    }
}

Thanks

Don

[former_member183750]

Export / save out the subreport so that you have it on it's own. Run that report through the utility. That will give you the connection code. Then in your app, use that code for the subreport.

- Ludek

[Former Member]

I figured this out through trial & error. Here's the solution...

You cannot use

boReportDocument.ReportClientDocument.Subreports[x].ReportClientDocument

because this throws the "Not supported with subreports" exception.

Instead, you have to use the corresponding objects & methods in

boReportDocument.ReportClientDocument.SubreportController

So to set the subreport connections I used the same code that I used for the main report, except that I replaced

boReportDocument.ReportClientDocument.DatabaseController.Database.Tables

with

boReportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(boReportDocument.Subreports[x].Name).Tables

and replaced

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(...)

with

boReportDocument.ReportClientDocument.SubreportController.SetTableLocation(...)

If your subreport uses different connection info than the main report, you can use Ludek Uher's suggestion of running the subreport through that handy RAS utility.