Changing the Data Source at Run Time

Obviously, one of the main benefits of integrating a Crystal Report in a Visual Basic program is the ability to tie the report contents to a data set or result set that the program is manipulating. This allows a user to interact with your application and then run a report that contains the same set of data that the user has selected.

One way to accomplish this is to create a Crystal Reports record selection formula or a SQL statement based on your user interface. The formula or SQL statement can then be passed to the report, as discussed elsewhere in this chapter. This allows the user to interact with a data grid, form, or other visual interface in the application and create a report based on its contents.

However, you may encounter situations in which you prefer to have the contents of an actual Visual Basic record set from one of Visual Basic s intrinsic data models act as the data source for a report. The RDC provides for this with the SetDataSource method. Using SetDataSource, you can initially design the report using a particular database or data source, or using a field-definition file (discussed in the online Help). Then, you can pass a Visual Basic DAO, RDO, or ADO record set, snapshot, or result set to the report at run time. The RDC will use the current contents of the VB data set to populate the report.

The syntax for SetDataSource is as follows :

 [  form  ]  Object  .SetDataSource  data  ,  DataTag  ,  tableIndex  

In this statement, Object is the RDC Database object (residing below the Report object) that you want to supply data to; data is the DAO, ADO, or RDO data set that you want to supply data to the report; and DataTag and tableIndex are parameters indicating, respectively, the type of data and the index of the table that you want to change the data source for. The DataTag parameter should always be specified as 3, with tableIndex being set according to the table you wish to point to the new data source.

Thus, if you have created an RDC report and named the Report object Report, and you have identified an ADO Recordset object elsewhere in your VB application with the name ADOrs, the following code will pass the contents of the result set to the report. When the report is printed, exported, or passed to the Report Viewer, the contents of the result set will be used by the report s Database object to populate the report.

 Report.Database.SetDataSource ADOrs,3,1 

Notice that the last two parameters to the SetDataSource method are 3 and 1, indicating the only allowable data tag (3), and the first table in the database (1).

There are actually two forms of the SetDataSource method available with the RDC. The method described previously applies to the entire Database object, requiring the third argument indicating a table index. There is also a SetDataSource method available for the DatabaseTable object. The syntax is almost the same ”the only difference is that a table-index parameter is not required, because the method applies only to a single Table object.

If you use SetDataSource to change the data source for a database table, make sure the data source you pass to the report remains in scope whenever the report runs. For example, you may define a Report object that s global to your application (by declaring it at the module level). But if you pass it a data source that was declared at the form level, and then try to run the report in another form, the data source will be out of scope and the report will fail. The ReadRecords method is available for reading the contents of the data source into the Report object, if maintaining scope for the data source isn t practical. Search the Developer s Help for information on ReadRecords.

Tip  

The RDC provides two other methods available from the Database object (underneath the Report object) that are similar to SetDataSource. AddOLEDBSource will add an OLE DB table and make its fields available to the report. AddADOCommand will add and pass an ADO record set as a table to the report through an ADO connection and command.

Unbound Fields

Depending on the reporting application you are designing, you may choose to create a very basic generic report layout that doesn t connect to any particular database at the time you design it. This might be appropriate, for example, for an application that allows a user to choose from a list of database fields and drag and drop them to a pseudoreport outline in your application. When the report is actually printed, your code would need to match the chosen fields to actual field locations on a Crystal Report. Or, you may design one report that a user can choose to connect to either a test database or a production database. Although the SetDataSource method, described previously, may be a good alternative for this example, you may also want to manually assign database values to fields on the report.

The RDC provides capabilities to make this type of customized reporting much easier. Unbound Fields can be added to your report in the ActiveX designer just like database fields, formula fields, and so forth. The only difference is that the fields don t actually connect to any particular database table or field. This assignment is made at run time, using automatic or manual binding (discussed later in the chapter).

Adding Unbound Fields to a Report

The first step in using Unbound Fields is to add them to the report in the RDC designer. You ll notice the additional Unbound Fields category in the Field Explorer that you don t see in the regular Crystal Reports designer. When you click the plus sign next to this category, you ll see a list of Unbound Field data types. Drag and drop the desired data types to the report just as you would other Field Explorer objects. Figure 27-4 shows Unbound Fields being placed on a report.

Figure 27-4: Adding Unbound Fields to a report

The RDC will apply default object names to the Unbound Fields you drag and drop. Depending on how you re planning ultimately to bind the fields to your data source, you may leave the default names if you so choose. But, it probably makes sense to at least give the Unbound Fields meaningful names, to help you remember what you re binding when you begin to write the binding code. And, if you plan to use the RDC s automatic binding method (discussed in the next section), you need to give them names identical to the field names in the data source that you will be binding to them.

Binding Fields to the Data Source

Once you ve created the report with your complement of Unbound Fields, it s time to bind them at run time. First, declare and assign any necessary objects. In the following sample code, a Report object is defined to contain the ActiveX designer report, and an ADO recordset is created. Fields from this recordset will be bound to the Unbound Fields created in the report.

 Dim Report As New CrystalReport1 
 Dim rs As New ADOR.Recordset 
 rs.Open "select * from orders", "xtreme sample database 10" 
 Report.Database.AddADOCommand rs.ActiveConnection, rs.ActiveCommand 

Remember that even though the Unbound Fields you added to the report aren t connected to any particular database fields, the Report object itself must be connected to your desired data source before you can bind any fields to it. This is accomplished with AddADOCommand, AddOLEDBSource, or some similar method.

After you make the data connection, there are two ways of actually binding the Unbound Fields to a database field: automatic binding and manual binding.

Automatic Binding

Automatic binding uses the Unbound Field s data type and, optionally , object name to bind to actual data fields in the data source. To use automatic binding, execute a single line of code similar to this:

 Report.AutoSetUnboundFieldSource crBMTNameAndValue 

By supplying the crBMTNameAndValue constant to the AutoSetUnboundFieldSource method, you assure that the RDC will use both the name and the data type of the Unbound Field to try to match to a data source field. If you want to base this object-to-field matching on name only, supply the constant crBMTName. The RDC will then attempt to match Unbound Fields to data source fields solely on the basis of name, regardless of data type.

Manual Binding

If you have disparate data sources that won t always match field names with the names you ve given your Unbound Fields, you can assign data source fields to the Unbound Fields one by one. Examine the following code fragment:

 Report.OrderID.SetUnboundFieldSource "{ado.Order ID}" 
 Report.OrderDate.SetUnboundFieldSource "{ado.Order Date}" 
 Report.OrderAmount.SetUnboundFieldSource "{ado.Order Amount}" 
 Report.ShipVia.SetUnboundFieldSource "{ado.Ship Via}" 

In this example, individual Unbound Fields are matched to data source fields using the field object s SetUnboundFieldSource method.