2239622 - Updating separate Main and Subreport connection info using ClientDocument in CR/BOE .NET SDK

SAP Knowledge Base Article - Public

2239622 - Updating separate Main and Subreport connection info using ClientDocument in CR/BOE .NET SDK

Symptom

Report was created using a connection to a single data source for both main and subreport.

At runtime we need to set the subreport connection to a different data source using only the ClientDocument, Not using RAS.

Environment

  • Crystal Reports for Visual Studio
  • BOE 4.x .NET SDK
  • Microsoft Visual Studio 2010 or above

Cause

The Report ClientDocument ( Engine ) will propagate the conneciton info from the main report to the subreport and use the same connection info. It will not allow using separate connection properties.

 

Resolution

There is a specific work that must be followed when changing a single connection to mulitple connections between main and subreport as follows:

  1. To explain how and why this needs changes is first thing you need to do is create a second DSN to your data base. The reason being the Engine when it detects the same data source being used will propagate the connection info used in the main report to the subreport connection info. To stop this you must use a separate connection source and therefore the reason for the second DSN.
  2. Now the second part of the changes is having to save the updated subreport connection info first, and also note you must set the subreport connection info first and then save the report as a temp report file.
  3. Close the report and now open the temp report.
  4. Now update the connection info for the main report, refresh it and then set the parameter values.

static void Main(string[] args)
{
    ReportDocument rd = new ReportDocument();
    rd.Load(@"D:\Report\ReportDon.rpt");
    //workaround to set sub rpt's connection first, save to disk temp file, load temp file again, then update main rpt's connection
    SetSubConnection(rd);
    rd.SaveAs(@"D:\Report\temp.rpt");
    rd.Close();
    rd.Load(@"D:\Report\temp.rpt");
    SetMainConnection(rd);
    //need refresh report to update data and provide param/db info to export and saveas
    rd.Refresh();
    SetParameters(rd);
    rd.SetDatabaseLogon("sa1", "sybase1");//set main report login to DSN DSN2, userid sa1
    //set sub report login to DSN NYTD_RGSDEV, userid sa
    for (int i = 0; i < rd.Subreports.Count; i++)
    {
        rd.Subreports[i].SetDatabaseLogon("sa", "sybase");
    }
    rd.ExportToDisk(ExportFormatType.PortableDocFormat, @"D:\Report\exported.pdf"); // export the report to PDF
    rd.SaveAs(@"D:\Report\ReportDon_2DSN.rpt", true); // save the report if you want it
}

Log on routines:

private static void SetSubConnection(ReportDocument rd)
{
    for (int i = 0; i < rd.Subreports.Count; i++)
    {
        foreach (Table table in rd.Subreports[i].Database.Tables)
        {
            try
            {
                TableLogOnInfo logonInfo = table.LogOnInfo;
                logonInfo.ConnectionInfo.ServerName = "DSN1";
                logonInfo.ConnectionInfo.UserID = "sa1";
                logonInfo.ConnectionInfo.Password = "sybase1";
                logonInfo.ConnectionInfo.DatabaseName = "don";
                logonInfo.TableName = "dbo" + logonInfo.TableName;
                table.ApplyLogOnInfo(logonInfo);
                table.Location = "dbo" + table.Location;
            }
            catch (Exception e)
            {
                throw;
            }
        }
    }
}
private static void SetMainConnection(ReportDocument rd)
{
    foreach (Table table in rd.Database.Tables)
    {
        try
        {
            TableLogOnInfo logonInfo = table.LogOnInfo;
            logonInfo.ConnectionInfo.ServerName = "DSN2";
            logonInfo.ConnectionInfo.UserID = "sa";
            logonInfo.ConnectionInfo.Password = "sybase";
            logonInfo.ConnectionInfo.DatabaseName = "don";
            logonInfo.TableName = "dbo" + logonInfo.TableName;
            table.ApplyLogOnInfo(logonInfo);
            table.Location = "dbo" + table.Location;
        }
        catch (Exception e)
        {
            throw;
        }
    }
}

Set the parameters if required:

private static void SetParameters(ReportDocument rd)
{
    ParameterFieldDefinitions parameterFields = rd.DataDefinition.ParameterFields;
    parameterFields.Reset();

    for (int i = 0; i < parameterFields.Count; i++)
    {
        ParameterFieldDefinition pf = parameterFields[i];
        if (pf.IsLinked())
        {
            continue;
        }

        if (pf.CurrentValues.Count == 0) //No saved parameter value
        {
            if (pf.DefaultValues.Count > 0)// Set report to use LOV pick list value, index 0
            {
                if (string.IsNullOrEmpty(pf.ReportName))
                    rd.SetParameterValue(pf.Name, pf.DefaultValues[0]);
                else
                    rd.SetParameterValue(pf.Name, pf.DefaultValues[0], pf.ReportName);
            }
            else//Directly set parameter value, value 1 for main report and value 0 for subreport
            {
                if (string.IsNullOrEmpty(pf.ReportName))
                    rd.SetParameterValue(pf.Name, 1);
                else
                    rd.SetParameterValue(pf.Name, 0, pf.ReportName);
            }
        }
        else //use saved parameter value
        {
            if (string.IsNullOrEmpty(pf.ReportName))
                rd.SetParameterValue(pf.Name, pf.CurrentValues[0]);
            else
                rd.SetParameterValue(pf.Name, pf.CurrentValues[0], pf.ReportName);
        }
    }
}

 

Keywords

cr for vs, clientdocument, datasource, .net sdk , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

SAP Crystal Reports, version for Visual Studio all versions