It is a trivial process to use TTX based reports in VS .NET - as long as these reports use only one TTX file. All you need to do is create an ADO
.NET dataset that matches precisely the structure of the TTX file and then use the following code:
crReportDocument.SetDataSource(dataSet)
or
crReportDocument.SetDataSource(dataSet.Tables(0))
If you run into issues (errors, missing data, etc.) at this point see the blog Troubleshooting Issues with VS .NET Datasets and Crystal Reports. Alternatively, point the report at a database table. However, you will need to convert database drivers. This can be done in the Crystal Reports designer (Database | Set Datasource Location), or in code using the inProc Report Application Server SDK for VS .NET. The Crystal reports SDK for VS .NET does not have APIs that allow for database driver conversion. Database driver conversion RAS code is below.
Dim myTables As Tables = reportClientDoc.DatabaseController.Database.Tables
'Loop through each table in the tables collection
For Each myTable As Table In myTables
'Save the old table
Dim oldTable As Table = myTable
'Set the new qualified name of the table
myTable.QualifiedName = "Northwind.dbo." + myTable.Name
Dim innerPropBag As New PropertyBag
'Setup the Logon properties that we will be using
innerPropBag.Add("Data Source", "vancsdb02")
innerPropBag.Add("Initial Catalog", "Northwind")
innerPropBag.Add("Provider", "SQLOLEDB")
Dim myPropBag As New PropertyBag
'Setup all other database properties we will be using
'Set the database dll that we will use
myPropBag.Add("Database DLL", "crdb_ado.dll")
'Set the server name
myPropBag.Add("QE_ServerDescription", "vancsdb02")
'Set the connection type
myPropBag.Add("QE_DatabaseType", "OLE DB (ADO)")
'Pass in the Logon properties we created earlier
myPropBag.Add("QE_LogonProperties", innerPropBag)
'The database is a SQLDB
myPropBag.Add("QE_SQLDB", True)
'We are not using single-sign on
myPropBag.Add("SSO_Enabled", False)
Dim myconn As New ConnectionInfo
'Pass the database properties to a connection info object
myconn.Attributes = myPropBag
'Set the connection kind to CRQE
myconn.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
'Set the user name and password
myconn.UserName = "devtech"
myconn.Password = "devtech"
'Pass the connection information to the table
myTable.ConnectionInfo = myconn
'Set the table in the report to use the modified table
reportClientDoc.DatabaseController.SetTableLocation(oldTable, myTable)
Next
|
For more details see the wiki Report Application Server Database Connectivity. Also see the article How to use the RAS SDK .NET with In-process RAS Server and the Developer Library.
Reports based on multiple TTX files (creation of which was never a good idea) present challenges. The following discusses possible approaches and solutions.
Again, pointing the report at a database that contains tables which exactly match the structure of the TTX file is a good approach. Possibly the best approach. Pointing a report at ADO .NET datasets using more
than one table may actually result in slower performance than connecting to the database directly. See kbase #1218428 - Slow performance when using ADO .NET dataset in .NET application for more details.
If a dataset approach is preferred, there are two options; fully programmatic and partly programmatic.
1) Fully programmatic approach. The code needs to loop through all TTX files used in the report and explicitly point at the correct table name in the ADO .NET Dataset.
crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE_a"))
crReportDocument.Database.Tables(1).SetDataSource(dataSet.Tables("NAME_OF_TABLE_b"))
crReportDocument.Database.Tables(2).SetDataSource(dataSet.Tables("NAME_OF_TABLE_c"))
Again, if you run into issues with this approach, see the blog Troubleshooting Issues with VS .NET Datasets and Crystal Reports(Troubleshooting Issues with VS .NET Datasets and Crystal Reports)
2) Partly programmatic approach. Create an SXD file that matches the structure of the TTX file precisely. Open the report in the Crystal Reports designer. Go to the Database menu and select Set Datasource Location. Use the ADO.NET (XML) driver
to point at the XSD. If this results in a Field Mapping dialog, your XSD does not match the TTX file structure and you will have to modify the XSD.
You should now be able to point the report at the ADO .NET dataset without the need to loop through the tables and explicitly set the table names.
crReportDocument.SetDataSource(dataSet) or crReportDocument.SetDataSource(dataSet.Tables(0))
One other approach may be considered; point the report at a stored procedure. Again, this is trivial if the report is based on one TTX file. Pointing a report based off of multiple TTX files at a stored procedure will result in the error; "Failed to save document". The same error would be produced if a report built off of several database tables is pointed at a stored procedure. In a nut shell, there is no easy way to convert a multi table or multi TTX report to a report that uses a stored procedure. The following is an ugly approach that may be workable in some instances.
Determine which table(s) are used the least in the report. Say, one there is only one field used from Table A, but there are 10 fields used from Table B. In this case, remove Table A from the report. This will result in the field from Table A being stripped out of the report. Now, point the report at the stored procedure. Re-add the stripped out field and ensure that the linking is correct. Cases where this approach will be problematic include reports with many tables and many fields used from each table. Reports that rely heavily of formula calculations. Reports that use the stripped out fields in charts. And others. E.g.; the simpler the report, the easier it will be to use this approach and it may be the some complex reports may have to be recreated from scratch.
- A word about the future of the TTX Drivers
Note that CRVS2010 is the last version that will include crdb_cdo.dll and crdb_fielddef.dll. For more information see the SAP Crystal Reports, Version for Visual Studio 2010 - Supported Platforms document.
More sources of information:
Crystal Reports Guide To ADO.NET
Reporting Off ADO.NET Datasets
In case of further questions, please create a new thread in the forum and don't forget to search the SAP KBase.
