Creating New Reports at Run Time

One of the features of the Crystal Enterprise Embedded Edition (discussed in Chapter 22) is the ability to create new reports at run time from within web pages. The RDC also offers the capability to create a new report from scratch in a Windows application. The Report Creation API, as Business Objects refers to this feature, allows your VB code to create a new report entirely from scratch, including tables and fields, report sections, and report objects. After you create the base report structure, you can further manipulate report sections or format report objects using code. You can even add the Embeddable Report Designer to your application to allow end users to interactively modify the report, virtually identical to the report design capabilities you have with the RDC s internal ActiveX report designer.

And, once all the code-based design and user manipulation is completed, not only can you view the report in the Report Viewer, or print or export the report, but you can save the completed report as an .RPT file, ready to be opened in Crystal Reports or another custom application. Just be aware that the Crystal Reports license agreement specifically forbids you from using these report design techniques to create competitive products.

Caution

The Report Creation API is only included with Crystal Reports 10 Advanced Developer Edition. Crystal Reports 10 Developer Edition, while offering run-time customization as discussed elsewhere in this chapter, does not provide run-time report creation capabilities. And, if you use these capabilities in your application, the Crystal Reports license agreement requires each organization you distribute the application to also purchase a copy of Crystal Reports 10 Advanced Developer Edition.

There are two general approaches to creating a report at run time:

Creating all report elements entirely within code
Using the Embeddable Report Designer ActiveX control within your VB application to allow an end user to create the report interactively

Creating a New Report with Code

Creating a report with code requires the same basic steps that creating a report interactively in the RDC ActiveX designer or in stand-alone Crystal Reports requires:

Create a new report.
Choose database tables and link them, if necessary.
Add fields, text objects, bitmap images, and other objects to various report sections.
Add any desired groups.
Add subtotals and grand totals.
Format objects for desired appearance.
View the report in the Report Viewer, print it, or save it as an external .RPT file.

The RDC object model exposes methods and properties to accomplish each of these steps. Initially, you must add the RDC Report Creation library to your application, using Project References. Check the Crystal Reports ActiveX Designer Design and Run-Time Library 10.0 (CRAXDDRT.DLL). And, if you will be viewing the completed report in the Report Viewer, add the Crystal ActiveX Report Viewer Library 10.0 with Project Components .

Tip	You may download a Report Creation API sample application from this book s companion web site, www.CrystalBook.com. The sample code in the remainder of this chapter is taken from this sample application.

Declare Application and Report objects as you do with applications that use existing reports. Consider this sample code:

 Public Application As New CRAXDDRT.Application 
 Public Report As CRAXDDRT.Report

Note references to the combined design-time/run-time library (CRAXDDRT). This is in preparation for using the Embeddable Report Designer (described later in the chapter). If you will not be using this designer, but just creating a report entirely within code, you may use the run-time-only library, CRAXDRT.DLL.

The first new step involves assigning an object to the Report object variable. Previously, you ve either assigned an existing ActiveX designer object to this variable or used the Application object s OpenReport method to assign an external .RPT file. Now, however, you will be creating a new report from scratch. The Application object exposes the NewReport method to accomplish this:

 ' Create a new empty report 
 Set Report = Application.NewReport

Tip	This will be the first place where you may encounter an error message if you have not purchased the proper Crystal Reports edition that includes run-time report creation capabilities.

As when creating a new report from scratch in the ActiveX designer or Crystal Reports, you must choose a data connection method and database tables for the report. The Report Creation API requires the same steps. The following sample code illustrates how to assign an ADO connection to the Report object, using the Orders table from the Xtreme Sample Database ODBC data source. The ADO connection string makes this connection (assume that the Microsoft ActiveX Data Objects library has been added to the project, and the ADOConnection and ADOCommand objects have been declared earlier in the Declarations section):

 ' Open the data connection 
 Set ADOConnection = New ADODB.Connection 
 ADOConnection.Open "Provider=MSDASQL;Persist Security Info=False; 
 Data Source=Xtreme Sample Database 10;Mode=Read" 
 
 ' Create a new instance of an ADO command object 
 Set ADOCommand = New ADODB.Command 
 Set ADOCommand.ActiveConnection = ADOConnection 
 ADOCommand.CommandText = "Orders" 
 ADOCommand.CommandType = adCmdTable 
 
 ' Add the data source (the XTREME Orders Table) to the report 
 Report.Database.AddADOCommand ADOConnection, ADOCommand

Note	Width limits of this book require that the argument to the ADOConnection.Open method be broken in the middle of the line. Note that the actual sample application maintains the string argument for the Open method all on one line.

You now have the beginnings of a report, including an assigned Report object with a data connection. Were you to open this report in the Crystal Report designer or supply the Report object to the Embeddable Report Designer at this point, you d find an empty report, but the Field Explorer would be populated by fields from the Xtreme Sample Database Orders table. You would then drag and drop desired fields into various sections of the report. The next requirement of your Report Creation application is to add objects to different report sections. Examine the following code fragments from the RDC Report Creation sample application:

 Report.Sections(1).AddTextObject "FedEx Order Detail", 6000, 400

This line adds a text object containing the text FedEx Order Detail to the report header, at position 6,000/400. This code uses methods several levels deep in the RDC object hierarchy. The AddTextObject method is available below an individual Section object within the Sections collection (in this case, the first member in the Sections collection) below the Report object.

Virtually all aspects of report formatting can be undertaken with RCAPI calls. Here s sample code to format the report title object just added:

 'Format the title 
 With ReportTitle 
 .HorAlignment = crHorCenterAlign 
 .LeftLineStyle = crLSSingleLine 
 .RightLineStyle = crLSSingleLine 
 .TopLineStyle = crLSSingleLine 
 .BottomLineStyle = crLSSingleLine 
 .HasDropShadow = True 
 .Font.Size = 18 
 .Font.Bold = True 
 .TextColor = &H808000 
 .Height = 500 
 .Width = 4500 
 End With

The sample application proceeds to add four database fields to the details section of the report:

 ' Add fields to details section 
 ' Note that the Sections collection can be accessed via 
 ' number or string index 
 Report.Sections("D").AddFieldObject "{ado.Order ID}", 750, 5 
 Report.Sections("D").AddFieldObject "{ado.Order Date}", 3150, 5 
 Report.Sections("D").AddFieldObject "{ado.Order Amount}", 5500, 5 
 Report.Sections("D").AddFieldObject "{ado.Ship Via}", 8850, 5

RDC object-hierarchy navigation in the details section is similar to that used to add objects to the report header. The AddFieldObject method used adds a database field, requiring the database field, left position, and top position as arguments. Note that the Sections collection can be accessed via either a numeric or a string index. Should you wish, you could also execute code to format each of these objects.

If you wish to create groups, there are methods exposed in the object hierarchy to allow complete flexibility with group creation. There are also options to create both group summary fields and report grand totals. The RDC Report Creation sample application creates a grand total of Order Amount in the report footer (Section 4) with the following code:

 ' Create and format Order Amount Grand Total in report footer 
 Dim AmountTotal As CRAXDDRT.FieldObject 
 ' Creating a separate object variable avoids deep navigation 
 ' down the hierarchy when formatting 
 Set AmountTotal = Report.Sections(4).AddSummaryFieldObject _ 
 Report.Sections(3).ReportObjects(3).Field.Name, crSTSum, 6000, 750) 
 With AmountTotal 
 .Width = 1500 
 .Font.Bold = True 
 End With    'AmountTotal

Note several important points about the preceding code fragment:

A separate object to hold the grand total field was declared and assigned. Note that this wasn t done previously for the text object or field object. While it is possible to create lots of objects, as shown in the preceding example, this requires extra coding and keeping track of all the additional objects. The purpose in this example is to make formatting easier by eliminating navigation into the object hierarchy to set the Width and Font.Bold properties.
You can apply every conceivable kind of formatting to objects once they have been added to the report. Note that the grand total has been widened from its default size and given a bold formatting attribute. A glance through the sample application reveals the UseOneSymbolPerPage formatting attribute being applied to the Order Amount field, so only one dollar sign will appear at the top of each page:
```
 ' Format the Order Amount to have one dollar sign per page 
 Report.Sections(3).ReportObjects(3).UseOneSymbolPerPage = True 
```

At this point, a basic report design has been created and is contained in the Report object. Additional RDC methods and properties that you are probably more familiar with can be applied to the Report object as well. For example, the sample application limits the report to orders shipped via FedEx with the following, now-familiar approach:

 Report.RecordSelectionFormula = "{ado.Ship Via} = 'FedEx'"

Now, you re ready to proceed to view, print, export, or perform other familiar functions with the Report object. You can also now supply the partially designed Report object to the Embeddable Report Designer to allow the end user to interactively modify the report you ve already started to design in code.

Assuming you ve added the Report Viewer to a form, the following code will now display your report in the viewer:

 CRViewer1.ReportSource = Report 
 CRViewer1.ViewReport

Saving the Report

As discussed earlier in the chapter, the ultimate result of your application can be a saved .RPT file that can be opened in stand-alone Crystal Reports or other applications or custom programs that utilize .RPT files. The RDC exposes a Report object method to accomplish this:

 Report.SaveAs "Sample.RPT", crDefaultFileFormat

Executing the SaveAs method will save the contents of the Report object to the filename supplied as the first argument. The second argument uses an RDC-provided constant to determine what Crystal Reports file format the file is saved in. If the file already exists when SaveAs is executed, it will be overwritten without a warning or any Visual Basic error being thrown. If this is of concern to you, execute additional code prior to SaveAs to check for the existence of the file.

Caution

Online help indicates that the file format argument can be either a Crystal Reports 8 or Crystal Reports 7 format. This is an error in the document. While these arguments will not throw an error (for backward code compatibility), the Version 10 RDC will always save a file in Version 10 format, unable to be opened by Crystal Reports 8.5 or earlier. The crDefaultFileFormat constant will also save the report in Version 10 format.

The Report Creation Wizard

In addition to the RDC Report Creation API, Business Objects has provided an additional COM library to assist you in developing custom report creation applications. The Report Creation Wizard is available to lead your application user step-by-step through a report creation process. In fact, the Report Creation Wizard object library is the same library used by Crystal Reports 10 s Microsoft Access and Microsoft Excel add-ins.

This tool allows an end user to create a report with an expert-type interface, complete with Next and Back buttons . If you re comparing the Report Creation Wizard to the Embeddable Report Designer (described later in the chapter) from a Crystal Reports perspective, you might consider the Report Creation Wizard to be a Report Wizard and the Embeddable Report Designer to be the Blank Report option.

Since the Report Creation Wizard is a separate library, you must add the library to your VB project. Choose Project References and check the Crystal Reports 10 Standard Wizard Library option. And as with other COM components, you must declare an object variable to hold the wizard object:

 Dim CRWizard As New CrystalReportWizard.CRStandardWizard

If you view the CrystalReportWizard library in the Object Browser, you ll notice that it exposes only the single CRStandardWizard object with one property and one method. The CrystalReport property is used to assign the wizard a partially defined Report object for the wizard to use as a starting point:

 Set CRWizard.CrystalReport = Report

For the wizard to be of any use, the Report object you supply must already have a data source and tables defined, because the wizard doesn t include any capabilities for the user to choose a data source or choose and link tables. This must be done prior to running the Report Creation Wizard. Also, the wizard does not contain any record selection capabilities. You need to provide your own user interface for record selection and set the RecordSelectionFormula property prior to or after running the wizard. If the Report object already has fields and groups created, the wizard will recognize them and place them in their appropriate locations in the wizard dialog boxes.

Once you ve supplied the Report object, display the Report Creation Wizard to the user by executing the DisplayReportWizard method:

 CRWizard.DisplayReportWizard

The Report Creation Wizard appears as an application-model dialog box on top of any existing forms your application may be displaying.

Your user may now progress step-by-step through the report creation process, clicking the Next button to move to each successive step. These steps can include choosing fields to include on the report; adding groups; creating subtotals, summaries, and grand totals; choosing TopN grouping; and choosing from the usual predefined report styles. When the user clicks the Next button in the Style box, they will be given a choice of what to do next.

Because the Report Creation Wizard is a completely self-contained object, it will launch Crystal Reports with no further coding required on your part. It will also view the resulting report in the Report Viewer, even if you haven t added it to your project. And, it will save an external .RPT file with no further coding required. Although you might prefer that the wizard merely return the modified Report object to your code to allow you to control what happens next, you have no choice ”the options to edit and save are built into the wizard and can t be disabled.

But, once the wizard does finish what it s doing, it will return the modified Report object to your application, where you may print, save, or export the report, or supply the Report object to the Report Viewer to be viewed .

Using the Embeddable Report Designer

One of the more usable and unique features of the RDC is the Embeddable Report Designer. This ActiveX control, when added to a VB form, allows an end user complete interactive report design and modification capabilities, virtually identical to the capabilities provided by the ActiveX report designer that appears in the Visual Basic IDE when the RDC is first added to a VB project. The end result from the Embeddable Report Designer is a Report object, identical to the Report object that s been discussed earlier in the chapter. It can be further manipulated from within your VB code, supplied to the Report Viewer, exported with the Export method, printed with the PrintOut method, or saved to an .RPT file with the SaveAs method.

For all the interactive power it gives to your end users, the Embeddable Report Designer is surprisingly simple to implement inside your VB application. To enable the Embeddable Report Designer in your VB application, several general steps are required:

Add proper references and components from the Project menu.
Add the Embeddable Designer to a form.
Supply a Report object to the Embeddable Designer.
Do any necessary further manipulation to the Report object, such as printing, exporting, saving, or showing in the Report Viewer.

Begin by choosing Project Components from the VB pull-down menus . You ll see the list of all registered ActiveX controls on your computer. In particular, you ll want to check the Embeddable Crystal Reports Designer Control Library 10.0. This will place a Crystal Reports icon on the VB toolbar. You may optionally want to choose the Crystal ActiveX Report Viewer Library 10.0 as well, if you will be providing an online viewer for your reports within your application.

You must also add a reference to the Crystal Reports 10 combined design-time/run-time library. Choose Project References from the VB pull-down menus and check the Crystal Reports ActiveX Designer Design and Run-Time Library 10.0.

Note

This combined design- and run-time library (CRAXDDRT.DLL) is one of two RDC libraries you can use. The run-time-only RDC library (CRAXDRT.DLL) is available as well. When you distribute applications, you ll want to include the combined library if and only if you will be including the Embeddable Report Designer in your application. If you will not require this designer, distribute the run-time-only library (CRAXDRT.DLL).

Once you ve added the proper components and references from the Project menu, you ll notice a new Crystal Reports icon for the Embeddable Report Designer in the VB toolbox. Simply add this control to a form as you would any other VB control. When you do so, you ll see the outline of the designer in your form, along with the designer s design-time properties in the VB Properties box.

Enabling the designer is as simple as supplying an existing Report object to the designer s ReportObject property, as in the following code:

 CRDesignerCtrl101.ReportObject = Report 
 CRDesignerCtrl101.DisplayToolbar = True 
 CRDesignerCtrl101.EnableHelp = False

This code assumes that the Embeddable Designer object has been left with its default name of CRDesignerCtrl101 and that an already declared Report object named Report is in scope within the current procedure. If you wish the end user to modify an existing report, you will have obtained this Report object by using the OpenReport method of an application object, or perhaps used the application object s NewReport method and programmatically added a database connection, fields, text objects, and other items (as in the sample application from this book s accompanying web site). Note, however, that the Embeddable Report Designer features the capability of displaying the Database Expert with a right-click menu option, so you may provide a completely empty report object to the designer if you choose. Your end user will be able to add tables and fields as they wish.

You ll also notice that the designer s EnableHelp property has been set to false. This property setting will hide the Help button from the designer s toolbar, as well as from pop-up menus. In fact, the Embeddable Report Designer exposes an entire set of properties, a single method, and a few Windows-standard events as part of its own library. Most of the report-specific customization of the designer is accomplished by setting properties at design time in the Properties box or at run time in code. A complete description of these properties can be found in the developer s Help file by opening the Embeddable Crystal Reports Designer Control Object Model category from the table of contents.

Tip

If you don t disable the online Help button in the Embeddable Designer by setting EnableHelp to false (either in the Properties box or at run time), users will receive a Help File not found error message if they attempt to get online help. Information for creating a Windows .HLP file and using it with the Embeddable Designer is available in CrystalDevHelp.chm. Look in the Help file contents and open the Embeddable Crystal Reports Designer Control Object Model category. Then, choose Distributing the Embeddable Designer.

Once this code has been executed and the form is displayed, you ll note a report designer identical to the designer that appears in the VB IDE when you first begin using the RDC. Users may use the Field Explorer on the left to add fields, create formulas, and so on. They may select objects and format them with right-click pop-up menus or toolbar buttons. The main differences between the Embeddable Report Designer and actual Crystal Reports is that all interaction must be with toolbar buttons, the Field Explorer, and pop-up menus ”there are no pull-down menus in the designer. Figure 27-7 ( opposite page) shows the Embeddable Report Designer and some of its interface elements.

Figure 27-7: The Embeddable Designer in a VB form

Once the user has finished interacting with the report, he or she may simply close the window containing the designer, or use some other user interface element you ve built into the form, such as a command button or tab control. However, the designer itself doesn t contain the Preview tab familiar to Crystal Reports users ”you ll have to facilitate viewing of the modified report in your own code.

The designer simply returns the same Report object to your VB code that you initially supplied with any changes the end user made. You may now pass the modified Report object to a Report Viewer control, export it to a file, e-mail it, or execute the Report object s PrintOut method to print it to a printer. These capabilities are discussed earlier in this chapter.