Other RDC Properties and Methods

This chapter has covered many RDC procedures for handling most common report- customization requirements, but several other areas of RDC functionality deserve some discussion. In particular, RDC functions that pertain to handling SQL database reporting, as well as dealing with subreports, should be addressed. In addition, the RDC DiscardSavedData method for resetting or clearing the contents of properties that may have been set in previous VB code should be explored.

The DiscardSavedData Method

If you find yourself displaying a Report object in a viewer, or performing some other output mechanism with the same Report object over and over, you will want to take steps to ensure that any left over settings from a previous report process are reset when you run the next report. While you can set the Report object to Nothing and instantiate it again, you may instead want to use the Report object s DiscardSavedData method if you will be running a report multiple times without the Nothing setting.

The Xtreme Orders sample application provides a good example of where this is helpful. The Report object is declared in a global module, and the Print Xtreme Orders form stays loaded during the entire scope of the application. Every time the OK button is clicked, the application sets Report properties based on the controls on the form and then either prints or exports the report, or else displays the form containing the Report Viewer.

The issue of clearing previous settings comes into play when a user runs the report more than once without ending and restarting the application. Because the Report object is never released and reinstantiated, all of its property settings remain intact when the user clicks OK subsequent times. If the Visual Basic code doesn t explicitly set some of these previously set properties, the report may exhibit behavior from the previous time it was run that you don t expect. Executing the Report object s DiscardSavedData method clears many properties of settings from any previous activity, returning them to the state they were in when designed.

Simply execute the DiscardSavedData method for any Report objects that you want to clean up, as shown in the following sample from the Xtreme Orders sample application:

 Report.DiscardSavedData 'required for consistent results

You may wonder about the significance of DiscardSavedData with the RDC, particularly when you re integrating a report that you designed in the ActiveX designer that does not have a Save Data with Report option. If you are using an external .RPT file that has been saved with File Save Data with Report turned on, you ll probably want Visual Basic to discard the saved data at run time and refresh the report with new data from the database. Because the RDC is integrating a report that is contained entirely within the VB IDE (even if an .RPT file was imported, its saved data is not imported with it), you may be confused as to how DiscardSavedData applies. The RDC, in essence, saves data in the Report object when it is viewed in the Report Viewer, printed, or exported (or when the ReadRecords method is executed). That saved data remains a part of the Report object until it s discarded or the object reference is destroyed .

SQL Database Control

Many corporate databases are kept on client/server SQL database systems, such as Microsoft SQL Server, Oracle, and Informix. Many Visual Basic applications provide front-end interfaces to these database systems and need to handle SQL reporting as well. The RDC contains several properties and methods that help when integrating reports based on SQL databases.

Probably the most common interaction with SQL databases is providing login credentials. This is discussed earlier in this chapter, under Providing Database Login Credentials.

Retrieving or Setting the SQL Query

When you submit a record selection formula, as discussed earlier in the chapter, the RDC automatically generates a SQL statement to submit to the database server. However, if the record selection formula contains a Crystal Reports formula or other complex characteristics that prevent the RDC from generating a WHERE clause in the SQL statement, report performance can be dramatically and negatively affected. You may, therefore, want to create the SQL statement the report uses directly in your VB application. As part of this process, you may find it helpful to retrieve the SQL statement that the RDC is generating automatically.

The Report object provides a read/write SQLQueryString property that can be read or written to at run time. By examining the contents of this property, you can see what the SQL query is that the RDC is generating. You can then make necessary modifications to the query, depending on the state of your application and the database on which the report is based. Then, pass the modified query to the SQLQueryString property to be submitted to the server when the report runs.

It s important to note that you can t modify the SELECT clause in the SQL query. Only FROM, WHERE, ORDER BY, and GROUP BY can be modified. However, you must still include the SELECT clause when you return an updated SQL statement to the report, and it must not be changed from the original clause the RDC created. Also, if you create ORDER BY or GROUP BY clauses, you must separate them from the end of the WHERE clause with a carriage return/line feed character sequence. Use the vbCRLF constant to add the CR/LF sequence inside the SQL query.

Tip	Crystal Reports 10 s SQL Command capabilities are included in the RDC. One option you have for complete database control is to base your report entirely or partially on SQL commands, rather than manipulating the SQL Query from within your code.

Reading or Setting Stored Procedure Parameters

If your report is based on a parameterized SQL stored procedure, you will probably want to supply parameter values to the stored procedure from within your code, much as you will want to respond to any RDC parameter fields that may have been created.

With the RDC, SQL stored procedure parameters are treated identically to Report parameter fields. The Report object contains the ParameterFieldDefinitions collection, containing one ParameterFieldDefinition object for each Report parameter field or stored procedure parameter contained in the report. The ParameterFieldDefinition object s ParameterType property will indicate whether the parameter is a Report parameter or a stored procedure parameter.

By executing the ParameterFieldDefinition object s AddCurrentValue method, you can pass a value to a stored procedure parameter from within your code. More detailed information on interacting with parameter fields can be found earlier in this chapter.

RDC Subreports

The RDC brings all the flexibility of Crystal Reports subreports to Visual Basic. If you import an .RPT file that contains subreports into the RDC, the subreports will be imported along with the main report. And, you can add your own subreports to the RDC design window at any time by right-clicking a blank area of the report and choosing Insert Subreport from the pop-up menu (see Chapter 13 for more information on creating subreports).

The unique part of the RDC is the way a subreport fits into the object model. When you are displaying the main report in the RDC design window, a subreport will appear as an object outline, just as it does in regular Crystal Reports. However, if you click the subreport object, it has a set of properties that appears in the Properties box, just like other objects on the report. Figure 27-6 illustrates this.

Figure 27-6: Subreport in the ActiveX Designer

You can set several design-time properties for the Subreport object, the same way you can for other objects on the report. One of the properties you may want to set as a matter of habit is the subreport name. By giving the subreport a descriptive name , you ll be able to more easily identify and work with it as you write code for your application.

After you add the Subreport object to the main report, it s treated just like a text object, field, line, box, or other object that appears on the main report in the main Report object model. But because the subreport contains its own set of fields and objects that are separate from the main report, the object model can expose them in a number of ways. Subreport objects (fields, formulas, sections, lines, and others) actually appear as part of the main Report object. The RDC precedes the object names with the name of the subreport and an underscore .

Consider a main report that contains several database fields that haven t been renamed since the report was created (Field1, Field2, and so on). The main report also contains a subreport that you ve named subTop5Products. The Subreport objects actually become part of the main report. You can now set properties for the Report object named Field1, which will refer to the first field on the main report. If you then set Report properties for an object named subTop5Products_Field1, you are setting properties for the first field on the subreport.

Also, the main report exposes a SubreportObject object for every subreport placeholder contained on the main report. This object exposes a variety of properties that can be read and written to at run time to control the appearance and behavior of the placeholder (although not the actual subreport itself). Consider the following code:

 'Set subreport object borders to blank 
 Report.subTop5Products.TopLineStyle = 0 
 Report.subTop5Products.LeftLineStyle = 0 
 Report.subTop5Products.BottomLineStyle = 0 
 Report.subTop5Products.RightLineStyle = 0

Here, the SubreportObject object is being manipulated from the Report object (referred to with the subreport name subTop5Products). The four line-style properties are all being set to 0, indicating that the border around the Subreport object will be invisible at run time.

Despite this tricky way of sharing objects and setting properties for the subreport placeholder, not all subreport properties, methods, and events are available from the main Report object. And if you re using an external .RPT file, these options won t be available either. Because the subreport is, in essence, an additional report that appears only on the main report, you need a way to control the behavior of the subreport using the same object model as the main report.

To accomplish this, you must declare an object of type Report from the CRAXDRT or CRAXDDRT libraries (the libraries the RDC exposes). You then use the main report s OpenSubreport method to create another Report object variable to contain the subreport. You can then set properties, execute methods, and trap events for the main report using its original object variable, and for the subreport using its own object variable. The following code fragment demonstrates this approach:

 Dim Subreport as CRAXDRT.Report 
 ... 
 ... 
 'Put subreport in its own object 
 Set Subreport = Report.subTop5Products.OpenSubreport 
 
 'Set record selection formula for subreport 
 Subreport.RecordSelectionFormula = "{ado.Unit Price} > 1000"

Note	You may also have to supply login credentials for databases in any subreports, separate from databases in the main report. Examples of how to do this appear earlier in the chapter, under Providing Database Login Credentials.