cancel
Showing results for 
Search instead for 
Did you mean: 

Can I edit the rpt file & change the datasource before loading the report?

Former Member
0 Kudos

We are an ISV and our application has a lot of reports. Our reports use a SQL Server database as the data source and each SQL Server at our customer sites has a different name.

In our testing we have determined that ReportDocument.Load tries to connect to the SQL Server that is saved in the RPT. If it can't fine the SQL Server saved in the RPT the load take about 60 seconds while it is waiting for the SQL Server Connection to time out.

We are using the Visual Studio 2008 version of Crystal Reports. This did not seem to be a problem with VB6/CR8.5.

We would like to edit the RPT and change the data source to the appropriate SQL Server before we call ReportDocument.Load.

Is it possible to edit the rpt file and change the data source before loading the report?

Or is there some way to tell Crystal not to try connecting to the DB

during the report.load?

In our case we will NEVER use the data source that is saved in the RPT, we will always change the data source using ApplyLogOnInfo.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

HI Todd,

You Can Not edit the report document before ReportDocument.Load() because if you dont load the report then you dont have anything to Edit

But as far as changing the datasource is concerned you can change that at runtime.

For changing the datasource following code will help you if both databases have a same schema :


Code for changing the database

ConnectionInfo crConnectionInfo = new ConnectionInfo();

crConnectionInfo.ServerName = "SERVER";

crConnectionInfo.DatabaseName = "DATABASE";

crConnectionInfo.UserID = "USERID";

crConnectionInfo.Password = "PASSWORD";

// Loop through the ReportObjects in a report and find all the subreports

foreach(ReportObject crReportObject in crReportDocument.ReportDefinition.ReportObjects)

{

// Check the kind of the ReportObject, if it is a subreport

// proceed. If not skip.

if(crReportObject.Kind == ReportObjectKind.SubreportObject)

{

// Get the SubReport in the form of a ReportDocument

string sSubreportName = ((SubreportObject)crReportObject).SubreportName;

ReportDocument crSubReportDocument = crReportDocument.OpenSubreport(sSubreportName);

// Use a loop to go through all the tables in the main report

foreach(Table crTable in crSubReportDocument.Database.Tables)

{

// Get the TableLogOnInfo from the Table and then set the new

// ConnectionInfo values.

TableLogOnInfo crLogOnInfo = crTable.LogOnInfo;

crLogOnInfo.ConnectionInfo = crConnectionInfo;

// Apply the TableLogOnInfo

crTable.ApplyLogOnInfo(crLogOnInfo);

// Set the location of the database. This value will vary from database to

// database.

crTable.Location = "DATABASE.OWNER.TABLENAME" or crTable.Locations;

}

}

}

The sample for doing this is available on support site.

Thanks,

Prasad

Former Member
0 Kudos

Thank you for the reply, but I don't think you understand our problem. We are using code that is very similar to what you posted to change the logon information after we load the rpt using ReportDocument.Load.

The problem is that ReportDocument.Load takes over a minute because Crystal cannot find the server that is saved in the rpt. In development, ReportDocument.Load takes less than a second because the server saved in the rpt is visible. We have to figure out some way to make ReportDocument.Load faster in the production environments at our customers sites.

If we could open the rpt as a byte stream and change the data source before we use ReportDocument.Load to load it, we could make the ReportDocument.Load much faster.

Thanks

former_member184995
Active Contributor
0 Kudos

Hi Todd,

On the report, go to the report options and make sure all the Verify options are unchecked (stuff like verify database, stored proc, etc).

Jason

Former Member
0 Kudos

I am having an issue with the datasource connection in Crystal Reports that is part of Visual Studio 2008. I have coded for the database connection as you have said in your post:

Code for changing the database

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim rpt As New ReportDocument

rpt = New ESPReport

rpt.SetParameterValue("@SarID", SessionDataLib.SarCommonPlus.SarId)

Connect(rpt)

Me.CrystalReportViewer1.ReportSource = rpt

End Sub

Private Sub Connect(ByRef rpt As ReportDocument)

Dim crConnectionInfo As ConnectionInfo = New ConnectionInfo

crConnectionInfo.ServerName = ApplicationDataLib.db_Server

crConnectionInfo.DatabaseName = ApplicationDataLib.db_Name

crConnectionInfo.UserID = ApplicationDataLib.db_UserId

crConnectionInfo.Password = ApplicationDataLib.db_Password

End Sub

When I step through the code it is placing the correct values into the ServerName, DatabaseName, UserId, and Password. The values are all being stored in the WebConfig file, but are being brought back correctly. The problem is that the datasource set on the .rpt file is still set to a different database. How do I make it so that it is looking at the datasource in the webconfig and not what I created the .rpt file with???

former_member183750
Active Contributor
0 Kudos

First place to start is with the latest SP:

https://smpdl.sap-ag.de/~sapidp/012002523100009351512008E/crbasic2008sp1.exe

Next, set the database logon parameters before SetParameterValue. If that does not help;

Create a one table one field report. No parameter, no subreports, keep it simple. Do not use WebConfig file, but hard code the database logon parameters. Does this work? If it does, start increasing the complexity (add the parameter, don't code the value, let the report prompt. If that works, now code the parameter value, etc.,, etc.).

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

rod_weir
Explorer
0 Kudos

Has anyone worked this one out?

We are also an ISV who ship and application bundled with hundreds of Crystal Reports. When then load, they are trying to connect to the development database.

Unfortunately, you have to load the report before you can change its connection string to the production database?

This is madness!

Using CR2008

Any ideas?

Rod

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Todd,

By Adding ReportDoc.Load() method to Pre-Init event, I mean Pre-Init Event of .Net Page in case of .Net Web application.

There is no document available for this.

This is just a testing what i have done at my end .

Thanks,

Prasad

Former Member
0 Kudos

Thanks again Prasad.

Our application is a Windows Forms application. I am loading the rpt as early as I can (after the user has selected it).

former_member183750
Active Contributor
0 Kudos

Todd, I've read the thread and noted that as far as you are concerned, the issue is that the original db source is not available to the report;

"The problem is that ReportDocument.Load takes over a minute because Crystal cannot find the server that is saved in the rpt."

This used to be an issue with the first release of CR 10, so if you are using CR XI r2, that should not be an issue.

However, slow loads may be due to any number of factors:

1) Linked OLE Objects. Particularly if you move the report to another computer and the path changes

2) Subreports with the option "Re-import When Opening" set. Again, this will particularly get worse if the report is moved to another computer.

3) The options, Verify on First refresh and Verify Stored Procedure on first refresh will also slow down the load of the report.

One good way to test this issue further, will be to install the CR Designer on that computer (just as a test). Open the report, set the new database connection and compare the load time there. You could even download and install just an eval version from here:

http://www.businessobjects.com/products/reporting/crystalreports/eval.asp

Other than the above, if you still think it's a database connection issue, you'd need to run some type of utility that tracks database connections (E.G.; SQL Server Profiler) and see if that is indeed the case.

Former Member
0 Kudos

Thanks for the reply Ludek.

I am downloading the eval now. We are using the version of Crystal that is integrated with Visual Studio 2008 do you know if this version of Crystal has the CR10 issue that you describe.

Is there an update for the Visual Studio 2008 version of Crystal Reports?

Thanks again

former_member183750
Active Contributor
0 Kudos

Oops, sorry - I remember reading that you were using CR 10.5, but forgot when I did the post. Anyhow, download of the CR XI r2 will still be a good test. There is an upgrade to CR 10.5; CR 2008, with SP0. CR 2008 Eval can be downloaded from here:

http://www.businessobjects.com/forms/default.asp?id=701600000009BXP

Former Member
0 Kudos

OK now I am confused again! Which version of CR am I using and which version should I be evaluating?

I am currently using the version of Crystal Reports "Basic" that comes with Visual Studio 2008. The version of the assemblies is 10.5.3700. I am guessing that this is really a version of CR 10.5.

One of the links I was given in the posts above is for the eval of Crystal Reports XI (which I am guessing is CR 11).

The other link is for the eval of Crystal Reports 2008 (which I am guessing is CR 12).

I tried to "Check for Updates" from the Crystal Reports menu in VS2008 and got a message that the FlexNet update server was not available.

So here is my question again: Which version of CR should I be evaluating?

Will either of these evaluation versions "break" the CR Basic that is currently installed as a part of VS 2008?

Is there (or will there be) an update to the CR Basic version that is a part of VS 2008?

former_member183750
Active Contributor
0 Kudos

Apologies Todd. My bad.

You are using CR 10.5 which bundled with .NET 2008.

An upgrade to that would be CR 2008, SP0.

The eval link for CR 2008 SP 0 is here:

http://www.businessobjects.com/forms/default.asp?id=701600000009BXP

As to which version of CR you install on the problem box, is of no consequence as both will do similar task; test the speed of the report once we set the new db connection.

One thing I was going to eventually suggest, was to save the report with the eval version of CR (Be it XI r2 or 2008) and then run it in the app. But let's cross that bridge when we get to it.

Trying CR 2008 in your app may be a good idea, but for now, way too much work as we'd have to worry about assembly versions and so on.

Hope I cleared things up a bit....

Former Member
0 Kudos

Thanks Ludek.

Unfortunately CR 12 has not fixed my problem. Here are the things I tried.

I installed CR 12 on a different machine, open the rpt with CR 12 and saved the rpt in the CR12 format. (The rpt would originally created in CR 8.5.) The rpt still took over a minute to load because it was trying to connect to a DB Server that it could not see.

I installed CR 12 on my development machine, upgraded my references to use the CR 12 assemblies and still have the problem.

Here is an easy way for anyone to reproduce this behavior:

1. Create a simple report pointing to a SQL Server DB (your SQL Server will need to be on a machine separate from the machine you are running CR on).

2. Turn off the SQL Server Machine.

3. Try to click on the Database menu in CR. It will take about a minute to dropdown. This is because CR is looking for the database machine but cannot find it.

One interesting note: CR is just looking for the machine. It is not looking for SQL, the DB, or the table used in the report. To verify this you can restart the SQL Machine but leave SQL Server stopped.

At this point the only "work around" that I have been able to figure out is to load the rpt, change the LogOn info, and export the report as an rpt (replacing the original rpt). This will save the customers Data Source information in the rpt so that subsequent loads of the rpt will be quick.

Any other ideas? It seems like there would be a lot of ISVs who run reports against SQL Server and embed Crystal in their applications. It seems like all of them would have this problem.

Thanks.

Former Member
0 Kudos

Hi ,

One way to fasten the loading of report is adding the load method in Pre-Init Event of life cycle.

Thanks,

Prasad

Former Member
0 Kudos

Thanks for the reply Prasad,

Can you point me to documentation on the Pre-Init Event?

Thanks