Sub Report can't resolve relitave path

[Former Member]

I've been having an issue where a sub report has a TTX file as a database with a relative path (".\\Cert_WorksetSpecialText.ttx""). Unfortunately when I open it in the Viewer I receive the Database connection Dialog for that file. If I use hard coded paths for the file it works fine. Also the Main reports TTX has a relative path, and it does not cause any issues.

simplified code is as follows:

     ReportDocument MainReport = new ReportDocument();

     MainReport .Load(ReportFile);

     MainReport.SetDataSource(DataTable);

     MainReport.VerifyDatabase();

     [...]

     ReportDocument SubReportDocument= MainReport.OpenSubreport(SubReportName);

     SubReportDocument.SetDataSource(SubDataTable);

     SubReportDocument.VerifyDatabase();

When displaying in the viewer I receive:

as a work around I trued changing the Database.ConnectionInfo to a static Path by doing the following prior to setting the DataSource:

TableLogOnInfo T = SubReportDocument.Database.Tables[0].LogOnInfo;

T.ConnectionInfo.LogonProperties[0] = new NameValuePair2("Field Definition File", x);

T.ConnectionInfo.ServerName = x;

T.ConnectionInfo.IntegratedSecurity = true;

SubReportDocument.Database.Tables[0].LogOnInfo.ConnectionInfo.Attributes.Collection[4] = new NameValuePair2("QE_ServerDescription", x);

        SubReportDocument.Database.Tables[0].ApplyLogOnInfo(T);

Though on ApplyLogOnInfo it reset the path back to ".\\Cert_WorksetSpecialText.ttx". Not using the ApplyLogOnInfo did not have any noticeable effect either. I had read somewhere that setting the location after ApplyLogOnInfo would do the trick, so I tried:

     SubReportDocument.Database.Tables[0].Location = T.TableName;

This only created an "Connection not possible" error in VS.

I cannot change the RPT files, since our current system required the dynamic pathing in the existing files, and I am only replacing one part of the system which was based on VB 6.0 and a much older version of CR (which stopped working after a server update).

I am currently using the latest update (13.0.16.1954) and tried an earlier v13 as well.

And suggestions or help would be much appreciated.

Thanks in advance.

[former_member11696 · Accepted solution]

TTX is legacy, convert your reports to XML data source.

You may have to use ReplaceConnection also.

Don

[Former Member · Accepted solution]

Thanks for the information. Unfortunately as the reports are also used by old legacy programs this is not an option.

Any information that I can find for ReplaceConnection that seems to work. key methods seem to be missing...

[former_member11696 · Accepted solution]

I suggest making a copy of all of those reports and load them from your \report folder rather than using the legacy program report source, a little more work to manage but doable.

Don

[Former Member · Accepted solution]

I am not sure what you are suggesting by "make a copy".

As for the background of why we cannot modify the reports themselves would be, that there are around 300 different reports and multiple VB 6.0 programs still need access to them. The reports are accessed from multiple server environments as well as existing multiple times in different environments (not the prettiest solution, but the current one). Environments being for example live, test, development. Basically it is a big mess, but it worked ... until now.

I had found  that link previously, but could not figure out where variable rptClientDoc comes from.

[former_member11696 · Accepted solution]

In your .NET app copy the reports into a new folder and use those rather than mixing the old reports with the new reports. Exporting to RPT format will update the file version to 13.

VB 6 applications have not been supported for 5 + years.

We always recommended updating the RPT files to be the same version as the runtime. In your case the reports should be updated to CR 2011 or 2013.

CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rptClientDoc;

rpt.Load(rptName.ToString(), OpenReportMethod.OpenReportByTempCopy);

rptClientDoc = rpt.ReportClientDocument;

Add the RAS assemblies to your project:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using CrystalDecisions.ReportAppServer.CommonControls;

using CrystalDecisions.ReportAppServer.CommLayer;

using CrystalDecisions.ReportAppServer.CommonObjectModel;

using CrystalDecisions.ReportAppServer.ObjectFactory;

using CrystalDecisions.ReportAppServer.Prompting;

using CrystalDecisions.ReportAppServer.DataSetConversion;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Windows.Forms;

Don

[Former Member · Accepted solution]

We keep the reports separate from the applications for a multitude of reasons. Primarily though it offers more flexibility. but also since the report location itself is on the server even non programmers can modify them without needing to recompile and roll out the software again. also since we user ClickOnce this would mean we would have to build and roll out all the software that uses a changed report, rolling out multiple programs for a single change is not very practical, since we update at least one report weekly if not daily.

I cant seem to make SetTableLocationByServerDatabaseName or LogonEx work, when setting the ServerName to the absolutist path of the ttx file. it still seems to require a user and password. The alternative would be to connect directly to a database, but there is not table to designate as all data is fed using SQL strings directly into the dataSource.

Is there a way to set up the dataBaseConnection with an SQL string as the table?

[former_member11696 · Accepted solution]

A little history on the TTX driver, We deprecated it... Meaning it is very old technology and therefore we stopped updating it and highly recommend you stop using it. We actually stopped shipping the dll in CR for VS but people need it to update there reports so its back in the install.

I suggest you stop trying to mix the 2 programs and convert completely to a new RPT format. But you can still do this at runtime but its going to cost a lot to create and support it.

You could create a data table with the report names and manage them that way, which ones are updated, need to be updated, user reports, canned reports, etc....

As for using SetLocation or any other DB API not sure which one will work with the ttx driver, you'll have to test them all, the Engine and RAS to see if any work for you.

About your suggestion on updating the reports this may work... Since reports are based on one ttx file we can map up to two tables from a report based on tables to a direct connection to a database/table or Command Object.

A Command object is a way to manually update a reports data SQL statement and allows you to modify it at runtime. May not be needed in your case.

To test this open one of your reports in CR Designer, not the built in one in VS, it has limited abilities.

Click on Database, Set Location... and then Log onto the SQL DB.

Now click on the Data Name you just used, OLE DB or DSN, and the ttx location and click Update. CR should then indicate you need to update the table info as well so click on the ttx file and then the table the ttx is based on and click the update button again.

Should look something like this:

Now the report is based on a direct connection to the DB.

Click on Database, and Then Verify and it should now work, you can hit the refresh button to view.

This is ideal and if this works for you then I suggest leaving it at that.

Now the problem comes to how to do this at runtime... ReplaceConnection should work, you will likely need to call VerfiyDatabase so the engine can update all the formula references and other objects to the new connection info.

If you want to convert to a Command, you can now get the SQL from the Report and use that in a Command SQL.

This all assumes the TTX files are using one table, if more than one that will not work so you'll ahve to rewrite your reports, and the customers custom reports as well.

If I get a chance today I'll test it in code also....

Don

[former_member11696 · Accepted solution]

Oh, there is another way also and that is to dump the data into a Dataset, the SDK should just work also. But be warned, there are memory limitations when using DataSets so it there is a lot of data then don't use that option.

Don

[former_member11696 · Accepted solution]

Hi Marcus,

I figured it out....

This converts the data source to ADO.NET driver, you need to load the data into a DataSet:

#region TTX

if (oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_fielddef.dll")

{

    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;

    CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;

    btnSQLStatement.Text = "";

    try

    {

        dtStart = DateTime.Now;

        string connString = "Provider=SQLOLEDB;Data Source=SQLServer;Database=xtreme;User ID=sa;Password=PW";

        string sqlString = "SELECT * FROM Customer";

        OleDbConnection oleConn = new OleDbConnection(connString);

        OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlString, oleConn);

        DataTable dt1 = new DataTable("ado_ttx");

        oleAdapter.Fill(dt1);

        System.Data.DataSet ds = new System.Data.DataSet();

        ds.DataSetName = "ado";

        ds.Tables.Add(dt1);

        rpt.SetDataSource(ds.Tables[0]);

        rpt.SetDataSource(ds);

        difference = DateTime.Now.Subtract(dtStart);

        btnSQLStatement.Text += /*rptTable.Name.ToString() +*/ " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";

        MessageBox.Show("Data Source Set", "RAS", MessageBoxButtons.OK, MessageBoxIcon.Information);

        ds.Clear();

        ds.Dispose();

        //IsRpt = false;

    }

    catch (Exception ex)

    {

        MessageBox.Show("ERROR: " + ex.Message);

        //return;

    }

Don

[Former Member · Accepted solution]

Hi Don,

Thanks, Ill try that today

Marcus

[Former Member · Accepted solution]

Hi Don,

I just looked over your code. This is exactly how we are trying to fill the Reports currenty. Since this is what is causing the issue in the sub report under the condition that the TTX file has a .\ path. This is the reason why I wanted to change the path (Main reports work though).

Regards,

Marcus

[Former Member · Accepted solution]

I found following code somewhere in order to replace the TTX with XML using code, so I figured it may be an alternative to generate a new file during runtime (if needed) and filling it the same way. unfortunately it the viewer just hangs.

public void convertTTXToXML(string fileName)

        {

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

            CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rcd = default(CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument);

            rcd = rpt.ReportClientDocument;

            rpt.Load(fileName);

            PropertyBag QE_Details = new PropertyBag();

            PropertyBag logonDetails = new PropertyBag();

            logonDetails.Add("Internal Connection ID", "{e22acfc2-a213-46b5-bc8a-048b4f6d4e0c}");

            //

            var _with1 = QE_Details;

            _with1.Add("Database DLL", "crdb_adoplus.dll");

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

            _with1.Add("QE_DatabaseType", "ADO.NET (XML)");

            _with1.Add("QE_LogonProperties", logonDetails);

            _with1.Add("QE_ServerDescription", "NewDataSet");

            _with1.Add("QE_SQLDB", "False");

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

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo = default(CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo);

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos = default(CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos);

            oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);

            for (int I = 0; I <= oldConnInfos.Count - 1; I++)

            {

                oldConnInfo = oldConnInfos[I];

                string DatabaseDLL = oldConnInfo.Attributes.get_StringValue("Database DLL");

                if (DatabaseDLL != null && DatabaseDLL.ToLower() == "crdb_fielddef.dll")

                {

                    newConnInfo.Attributes = QE_Details;

                    newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

                    rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

                }

            }

            rpt.SaveAs(fileName + ".new");

        }

[former_member11696 · Accepted solution]

You need to update the subreport in code also, it won't propagate as it used to...

Search all report object for subreport and then use the same code as you do for the main report.

//set the crSections object to the current report's sections

CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;

int flcnt = 0;

bool SecureDB;

//loop through all the sections to find all the subreport 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)

        {

            try

            {

Don

[Former Member · Accepted solution]

so I tried looping through the subreports with:

foreach (string n in rcd.SubreportController.GetSubreportNames())

            {

                CrystalDecisions.ReportAppServer.Controllers.SubreportClientDocument scd = rcd.SubreportController.GetSubreport(n);

                oldConnInfos = scd.DatabaseController.GetConnectionInfos(null);

                for (int I = 0; I <= oldConnInfos.Count - 1; I++)

                {

                    oldConnInfo = oldConnInfos[I];

                    string DatabaseDLL = oldConnInfo.Attributes.get_StringValue("Database DLL");

                    if (DatabaseDLL != null && DatabaseDLL.ToLower() == "crdb_fielddef.dll")

                    {

                        newConnInfo.Attributes = QE_Details;

                        newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

                        scd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

                    }

                }

            }

unfortunately this also did not help the crystalReportViewer still freezes. Though the rpt file seems to be fine (with or without changing the subreports).

[former_member11696 · Accepted solution]

Once you update the report use the SaveAs() and save it as a new report. then open that report in CR Designer and have a look at the connection info for main and each sub. Possibly one is not getting updated.

Don

[Former Member · Accepted solution]

Hi Don,

I made a new rpt with one sub rpt, both using xsd files instead of ttx. it turns out using the results were exactly the same. A relative path cannot be resolved in a sub report, regardless of the definition file type. So this would mean I am back to trying to figure out how to replace the relative path with a static on in the code.

[former_member11696 · Accepted solution]

Hi Marcus,

I've ask DEV if we can alter/add a new open method to use the Original Location.

By default CR opens a the report and makes a copy of it in the Users \temp folder:

C:\Users\%USER%\AppData\Local\Temp

Using the feature "Same As report" removes the fully qualified path to the data file:

But since CR now uses the Users \temp folder as the Report location it expects the data files to be in the same place, the users \temp folder.

Work around for now is to copy the data files to that location.

I tested and it works for both main and subreports.

I'll create a case and add this Enhancement request for SP 17, not sure if they can do it that quick but we'll see.

Same as this case also -

Incident 238245 / 2016 / ER - OpenReportByDefault -same as Report

KBA - 2298839

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

This works. Its not pretty, but it gets the job done.

I was actually not aware of the contextmenu option, and had been typing the '.\' manually when creating the connection to the file.

Thanks

[former_member11696 · Accepted solution]

Hi Marcus,

DEV Agreed to this ER and think it's a good idea to use the OpenByDefault property to use the original location of the report to look for the data files.

They just need to look into what is required to change/add the code behind it and they will try to get it into SP 17.

Thanks again

Don