cancel
Showing results for 
Search instead for 
Did you mean: 

Changing report datasource at runtime

Former Member
0 Kudos

In advance, I've read all of the posts on here about dynamically changing the datasource for your report at runtime and none of it seems to work.

I developed a report against my development database using an ODBC connection. At runtime I want to pull the data from the production database. I've tried clearing the DataSourceConnections. I've tried the SetDatabaseLogon as well as Table.LogOnInfo, Table.ApplyLogOnInfo, SetConnection, everything. When I SetDataSource and feed in my DataSet, it's still the development database data. I've switched connection strings back and forth between the development and production databases and verified that the DataSet is being populated with the correct data.

Anyone have any ideas? I'm using Visual Studio 2010 and Crystal Reports 2011.

Thanks, in advance for any help you can give.

Accepted Solutions (1)

Accepted Solutions (1)

former_member183750
Active Contributor
0 Kudos

Hello Perry

Assumption is that when you say that you are using Crystal 2011 you mean 'SAP Crystal Reports, developer version for Microsoft Visual Studio". That being the case, make sure you are using Service Pack 5:

http://scn.sap.com/docs/DOC-7824

Then:

1) Enable the option "Verify on 1st refresh"

2) You appear to be wanting to use Datasets, so the code should be:

     crReportDocument.SetDataSource(dataSet)

You could also try:

     crReportDocument.SetDataSource(dataSet.Tables(0))

and finally another option:

     crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

3) If you are using multiple tables in your dataset, make sure these are linked.

4) If the report was not created against an XML schema file that represented the dataset, create a schema file to represent the dataset by using the 'WriteXMLSchema' method:

myDataset.WriteXml(xmlPath, XmlWriteMode.WriteSchema)

Then set the datasource location of the report to the newly created schema file.

5) Finally, one last resource to look at:

Crystal Reports Guide To ADO.NET

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

Former Member
0 Kudos

Ludek,

Thanks for the info. Long story but we have to use DataSets. Not a CR expert so this is the only way I've figured out to develop the reports against the dev DB and run them against prod DB. Installed the SP 5 but still having the same issue. Do I need to reboot? Here's the code I'm running:

ReportDocument rd = new ReportDocument();

rd.Load( "VECQtrRpt.rpt" );

rd.SetDataSource(ds);


// Using this call I get the correct server name and user ID but no DB name or password.

rd.DataSourceConnections[0].SetConnection( "SERVER URL", "DBNAME", "userID", "password" );


// Using this call instead of the previous gives me a totally different server name. And

// doesn't display the DB, user ID or password info in the login dialog box.

// Using the previous call PLUS this call I see the correct server, user ID and password but no

// DB name.

rd.SetDatabaseLogon("userID", "password", "SERVER URL", "DBNAME");

// Found this code online but it bombs out on setting the table.Location.

// Commenting out the table.Location call still doesn't show a DB name in

// the login dialog box.

ConnectionInfo ci = new ConnectionInfo();

ci.ServerName = "SERVER URL";

ci.DatabaseName = "DBNAME";

ci.UserID = "userID";

ci.Password = "password";

foreach (Table table in rd.Database.Tables)

{

          TableLogOnInfo tli = table.LogOnInfo;

          tli.ConnectionInfo = ci;

          table.ApplyLogOnInfo(tli);

          table.Location = string.Format("{0}.{1}", ci.ServerName, table.Name);

          //table.Location = table.Location;

}

rd.VerifyDatabase();

ReportViewer.ReportSource = rd;

ReportViewer.Refresh();

I think the issue might be the missing DB name. If I can figure out where that's actually being set (or cleared out) it might work.

Perry

Former Member
0 Kudos

Forgot to add. I tried the rd.SetDataSource(ds.Tables[0]) and the report ran but it only gave me a table structure, no data.

Former Member
0 Kudos

One other possible wrinkle. We're using MySQL

former_member183750
Active Contributor
0 Kudos

Hello Perry

Well, if you are using datasets, than all the logon code is of no consequence as CR does not care about any of that. E.g.; by creating the dataset, you take care of any "logon stuff" when creating the dataset. Crystal has not a clue where the data came from, nor does it care.

The app in it's simplest incarnation should be:

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

crReportDocument.Load("<path to the report>")

crReportDocument.SetDataSource(dataSet)

'Bind the report to the viewer
CrystalReportViewer1.ReportSource = crReportDocument

Do have a close look at the article Crystal Reports Guide To ADO.NET. And also, have a look at this wiki:

Troubleshooting Issues with VS .NET Datasets and Crystal Reports

- Ludek

Former Member
0 Kudos

Lukek,

This is my code from a test project, stripping out everything but the bare essentials:

//string connectionString = "server=***.***.***.***;Database=DevDb;Uid=*****;Pwd=*****";

string connectionString = "server=***.***.***.***;Database=ProdDb;Uid=*****;Pwd=*****";

MySqlConnection connection = new MySqlConnection( connectionString );

connection.Open();

MySqlCommand command = new MySqlCommand();

command.Connection = connection;

command.CommandText = "MyStoredProcedure";

command.CommandType = CommandType.StoredProcedure;

MySqlDataAdapter da = new MySqlDataAdapter();

da.SelectCommand = command;

DataSet ds = new DataSet();

da.Fill( ds );

ReportDocument rd = new ReportDocument();

rd.Load( "MyReport.rpt" );

rd.SetDataSource( ds );

ReportViewer.ViewerCore.ReportSource = rd;

I developed a test report against my development DB and the stored procedure returns one row. When I run the stored procedure against the production DB, it returns 259 rows.

When I switch my connection string to the production DB, I step through the code and right before I call rd.SetDataSource( ds ) I verify that there are 259 rows in ds.Tables[0].Rows.Count. When I hit F5 to run the rest of the code the report shows the one row from the development DB.

I'm wondering if part of the problem is that I'm not using a report created inside Visual Studio or a DataSet created by Visual Studio's wizard.

At this point I don't have time to keep pouring into chasing this problem. I'm just going to develop my reports against the production database and be done with it. Thanks for your assistance, advice and, most of all, your patience.

Perry

former_member183750
Active Contributor
0 Kudos

Hello Perry

Where the report was created is really of no consequence. However, in addition to the testing you have done, I really would like you to have a look at the following blog:

http://scn.sap.com/community/crystal-reports-for-visual-studio/blog/2009/03/02/troubleshooting-issue...

I am pretty sure it will provide valuable info towards resolving the issue.

- Ludek

Former Member
0 Kudos

Hi Ludek,

I am also facing similar problem (populating dataset and using setdatasource() method to patch the data to report), but it is slightly different.

1. I have report A (individual report) : If I use setdatasource(), it works perfectly without any issues because it doesn't consider connection as you mentioned in other posts. - I am fine here.

2. B with subreports. I can't use dataset for subreports, so subreports are responsible to fetch data as per configuration; but I do use dataset to patch data on B using setdatasource as well as applying LogOnInfo(with same connection details which were used at report design time) of each table in ReportDocument. But now if I try to change the servername (connection) in apply log on info or serdatasourceconnection() methods; nothing works and crystal dlls throw DataSource exception..

In short if your report have subreports and if we try to change connection -  doesn't work.

I hope I have presented my issue correctly.

Let me know if you can help me. Thanks.


former_member183750
Active Contributor
0 Kudos

Hi Jaikumar

With apologies but I need this explained a bit more:


2. B with subreports. I can't use dataset for subreports, so subreports are responsible to fetch data as per configuration; but I do use dataset to patch data on B using setdatasource as well as applying LogOnInfo(with same connection details which were used at report design time) of each table in ReportDocument. But now if I try to change the servername (connection) in apply log on info or serdatasourceconnection() methods; nothing works and crystal dlls throw DataSource exception..

E.g.; How do I square these statements:

I can't use dataset for subreports,

and

but I do use dataset to patch data on B

Sorry. Sometimes things said another way clears things up.

- Ludek

Former Member
0 Kudos

Hi Ludek,

Thanks for reply. I apologise for not specifying enough details.

I have report A and B.

Report A doesn't not have any subreports.
While designing report A, I have specified servername as "SERVER01".
Now, in code I am using SetDataSource() method to set DataSet as datasource for report A.
As we know if we use SetDataSource() method, crystal ignores connection details.
It just try to map the table name from connection or datasource.

E.g. if report A had table name "TABLE_01", crystal will try to  locate "TABLE01" either from database or datasource. In above details I have used SetDataSource() method by passing a DataSet, so crystal tries to find "TABLE01" in this DataSet. If it finds it will read the data and display in report.

For Report A I also tried using ApplyLogonInfo() method with different server on database.tables; it worked. In short Report A worked for me in both the scenarios.
1. Using SetDataSource()
2. Using ApplyLogOnInfo() of reportA.Database.Table[0];
So far I am fine.
--------------------------------------------------------------------------------------------------------------------------------------------------
Now next case:
Report B with some subreports B1,B2,B3.
While designing B,B1,B2,B3; I have specified servername as "SERVER01".
Report B having table name "TABLE02"
Now, here I am trying to use same 2 techniques as I did above for Report A.

Instead of relying on connection in B, I will use SetDataSource() method to pass DataSet 'ds' only for B not for B1,B2,B3.
DataSet ds = GetDataFromService();
reportB.SetDataSource(ds);

Here I am applying different logon info with SERVER02 for subreports B1,B2,B3 as shown below:
ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.ServerName = "SERVER02";
            connectionInfo.UserID = "ABC";
            connectionInfo.Password = "HELLOWORLD";
            TableLogOnInfo info = new TableLogOnInfo();
            info.ConnectionInfo = connectionInfo;    
for (int i = 0; i < reportB.Subreports.Count; i++)
            {
                var subReport = reportB.Subreports[i];
                for (int j = 0; j < subReport.Database.Tables.Count; j++)
                {
                    subReport.Database.Tables[j].ApplyLogOnInfo(info);
                }
            }
As I am not going to call SetDataSource() method of subreports then those reports will try to read data through connection.
Finally, I am trying to export reportB to pdf as:
doc.ExportToDisk(ExportFormatType.PortableDocFormat, "C:\\rpt_2.pdf");
This doesn't work. It throws an exception DataSourceException.
An unhandled exception of type 'CrystalDecisions.CrystalReports.Engine.DataSourceException' occurred in CrystalDecisions.ReportAppServer.DataSetConversion.dll
Additional information: Failed to load database information.
Error in File report_b{5E579755-5CC4-4F00-9EE9-B878D685EB9F}.rpt:

The error happens for reportB which is parent for B1,B2,B3.
Can you help me to solve it? Thanks.

former_member183750
Active Contributor
0 Kudos

Perfect. Beautiful explanation .

There is a number of ways of testing this. Maybe the easiest will be as follows:

1) Back up your B report

2) Remove all subreports from report B

3) Save

4) In your code, pass the dataset to report B (comment out subreport logon code)

Does that works? If it does not, see this wiki.

If that works, let's go back to the original B report (with all the subreports).

Follow the above wiki now. If you follow the wiki you will be able to view the report in the CR designer. Can you?

- Ludek

Answers (0)