Working with the RDC

Chapter 19 - Building Windows Applications with the Report Designer Component
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Refer back to Figure 19.9 and notice that the PostalCode field is selected. When you select a field in the Report Designer, the properties of that field are displayed in the Properties window. In the Properties window of Figure 19.9, the Name property is highlighted. This is the name of the object, and it enables you to access the object from VB code via its name. The other properties of the report are listed in the Properties window; for example, DecimalPlaces identifies how many decimal places to display in the field, and BackColor identifies the background color of the field. The rest of the properties should look familiar to you because they are report properties that you set in Crystal Reports; however, they are now available in VB. Using the Properties window, you can change the properties of the objects at design time, or you can modify the properties at runtime using code.

CrystalReport1.PostalCode1.BackColor = vbRed CrystalReport1.PostalCode1.Left = 1000

If we place these two lines of code in the Form_Load event, shown in Listing 19.1 above, before we display the report, we will change the background color of the PostalCode field to Red and move it 1,000 twips from the left of the form.

Note 

vbRed is a predefined color in Visual Basic. You can also supply the hex value for the color, such as .backcolor = &HFFFF00, which will give you teal. The easiest way to determine the color is to click the BackColor property in the Properties window, select a color, and then cut and paste the hex value from the Properties window into your program.

Refer again to Figure 19.9. At the top of the Properties window in the drop-down list is the name of the object you selected, PostalCode1 in our example. Beside the name of the object is the object type, FieldObject in our case. The object type identifies what type of object you are working with and what method and properties are available. Refer back to Figure 19.10, and you can see that the Field object is part of the Report object. In the Developers help file (CrystalDevHelp.chm) search for "Report Designer Component Object Model" to get a complete list of objects and their methods, properties, and events. Figure 19.12 shows the table of methods and properties for the Field object from the help file and a description of each. The Read/Write column indicates whether you can change the property or just read what value it contains from your program.

Note 

The figure shows IfieldObject, the “I” indicating that this is the interface for the FieldObject. Interface is a fancy object-oriented term that indicates the methods and properties available in the object.

Figure 19.12. Field object properties

Crystal Report Events

In general, properties in Visual Basic are accessible at design time or runtime, and you can read, write, or read and write the properties. Crystal Reports adds one more level of accessibility. Because the report engine in Crystal Reports goes through multiple passes, a particular field may not always accessible to your VB code. The Restriction In Event Handler column shown in Figure 19.12 indicates when you can use these events. The Automation Server sends an event to your program to notify you when formatting for a particular area is occurring, and at this time you can modify the properties for that object if it has restrictions. To access the event, from the Project window in Visual Basic, right-click CrystalReport1 in the Designers folder in the project window and choose View Code. This will take you to the code behind the Report Designer. From there, click the Object drop-down list box (top left) to choose a section of the Crystal report, as shown in Figure 19.13. Visual Basic will automatically create a Format event for that section. You can also access the Initialize and Terminate events for the entire report. These events indicate the beginning and end of the report processing.

Figure 19.13. Report section events

From our previous example, the Background and the Left properties are accessible from the event; when formatting is idle, you can access these properties from both places. The code below modifies the properties as described above. However, remember that when you are working with objects and properties on a report, if you try to access a variable that is accessible only during event processing, you will receive an "Access denied" error.

Private Sub DetailSection1_Format(ByVal pFormattingInfo As Object)     CrystalReport1.PostalCode1.BackColor = vbRed     CrystalReport1.PostalCode1.Left = 1000 End Sub

If you have a large report that contains many sections and Report objects, the default naming conventions for Crystal Reports, such as Text1, DetailSection1, etc., will make your code difficult to follow. You can select each section or object that you want to work with and change the name so your code is easier to follow. This is similar to how we changed the default names of objects in VB from command1 to cmdPrint, for example.

Adding a “Continued” Message on Repeating Groups through Events

The Format event for each section receives as input a pFormattingInfo object, as shown in Figure 19.13. This object contains three read-only properties: IsEndOfGroup, IsRepeatedGroupHeader, and IsStartOfGroup. The properties can contain a True or False to indicate group formatting information. You can use these properties to help you create formatting logic in your program. For example, if your report is designed so that groups repeat on a new page, you can check IsRepeatedGroupHeader Is True and update the Text object beside the group name to read "Continued…." To demonstrate this, follow these steps:

1. Insert a Text object beside the Group #1 Name field in the Group section of a report, change the Name property to txtContinuedMsg, and leave the text in the Text object blank.

2. Make sure the group name will repeat in the report. From the Report Designer in VB, right-click Group #1, select Group Expert, click the Options button, and then choose the Options tab.

3. Select Repeat Group Header On Each Page. Now the Group Header will repeat for each page if the whole group can’t print on one page.

4. In our code we need to test whether the group is repeated and change the message in our Text object if it is. Add the following code to the GroupHeaderSection1 Format event:

   Private Sub GroupHeaderSection3_Format(ByVal [AU: Line of code wraps. Insert a break where desired. LSR As is okay]pFormattingInfo As Object)              If pFormattingInfo.IsRepeatedGroupHeader Then         txtContinuedMsg.SetText ".... Continued"     Else         txtContinuedMsg.SetText ""     End If End Sub

   Note 

   The Text object we added is a TextObject object type; it has a SetText method that allows you to change the text that it displays.

Using Program Counters

It is common in programs to create counters such as counter = counter + 1 to keep track of events or items in our code. If you need to add a counter to your program, keep in mind that formatting events may be called multiple times by the Crystal Report Engine. If you place program counters in the section event that depends on the event being called once, your counters will not always be correct. If you need to keep track of counters, the Report object has three methods you can use: AddReportVariable, GetReportVariable, and SetReportVariable, so you can work with counters in the events. The special variables are called Report variables. The Report Engine keeps track of the variables so they don’t get incremented many times if the formatting event has to be called multiple times.

The AddReportVariable method creates the Report variable and assigns it a data type. Similarly to the Dim statement in VB, SetReportVariableValue and GetReportVariableValue allow you to read and write the variable, as demonstrated in Listing 19.2.

Listing 19.2: Implementing Counters in a Format Event

Private Sub Report_Initialize()     CrystalReport1.AddReportVariable crRVNumber, "Counter" End Sub Private Sub GroupHeaderSection2_Format(ByVal pFormattingInfo As Object)          CrystalReport1.SetReportVariableValue "Counter", _     CrystalReport1.GetReportVariableValue("Counter") + 1 End Sub

The AddReportVariable must be declared in the Report Initialize event of the report. In GroupHeaderSection2 we read and increment the "Counter" variable. This is an abstract code sample that does nothing with the counter, its purpose is to demonstrate setting up counters; in your program you will need to add code to do something with the counter.

Changing Record and Group Selection

We have created a sample application that will allow users to select whether they want to see only five-star resorts and to change the countries they want to see in the report. Figure 19.14 displays the user interface. We populated the list box with countries from the same data source as the report so the list would match. Let’s examine the code in Listing 19.3.

Figure 19.14. Resort VB application

Listing 19.3: Changing Record and Group Selection

 Private Sub cboCountries_Click()     CrystalReport1.GroupSelectionFormula = _              "{Resorts.Country} = '" & cboCountries.Text & "'"     End Sub Private Sub chk5Star_Click()     If chk5Star.Value = 1 Then 'checked         CrystalReport1.RecordSelectionFormula = _                      " {Resorts.FiveStarRating}"     Else         CrystalReport1.RecordSelectionFormula = _            " not {Resorts.FiveStarRating}"     End If End Sub Private Sub cmdRefresh_Click()     CrystalReport1.DiscardSavedData         CRViewer91.Refresh End Sub

In this application we are demonstrating how to set the group selection formula and the record selection formula in a report from Visual Basic. When the user selects a country from the list of countries in the Country combo box, the cboCountries_Click event will be generated. In that event we update the GroupSelectionFormula property of the Report object. When you set a formula in Crystal Reports via Visual Basic, you must set the formula exactly the same way Crystal Reports would set it. For example, if the user selected Canada in the combo box, the group selection formula would be set to {Resorts.Country} = 'Canada'. Notice that we must use single and double quotes in Visual Basic to build the exact formula that Crystal Reports requires.

When a user clicks the 5 Star Only check box, the chk5Star_Click event is generated and we do the same thing, except this time we set the record selection formula instead of the group selection formula. In this example we did not have to append any text from VB to build the group selection formula and we simply created a Crystal Report formula. If we did have to add append text, we would have to mix single and double quotes and append strings together as in the group selection formula.

Tip 

The easiest way to build Crystal Reports formulas for Visual Basic is to use Crystal Reports and build the formulas using the Formula Workshop. You can then cut and paste the formulas into VB and modify them as required.

When a user clicks the Refresh button, the report will be redisplayed with the new selection criteria. The CRViewer91.Refresh method refreshes the report.

The Crystal Viewer has two refresh methods, Refresh and RefreshEx. The Refresh method loads and redisplays the report; the RefreshEx method accepts a Boolean parameter, so you specify whether or not to refresh the report from the database. For example, CRViewer91.RefreshEx False will refresh the report but not from the data source.

The RefreshEx method is not documented in the Crystal Reports Developers help file. Occasionally, the documentation and code are out of sync, and this may be one of those times. The best way to get a list of all the methods and properties for any object you are working with is to view the object with VB’s Object Browser (click the F2 button), or select View > Object Browser from the menu. Once the Object Browser opens, select the library you want to view from the top-left drop-down list. The Object Browser interrogates the selected libraries’ objects for all the publicly accessible methods, properties, and events and displays them and provides limited help. Figure 19.15 shows the methods, properties, and events for the Crystal Report Viewer via the Object Browser.

Tip 

Methods are indicated with a thrown block, events with a lightning bolt, and properties with a pointing finger. Sometimes methods properties, and events are collectively known as members of a class. A class is the program that created the object.

Figure 19.15. Methods, properties, and events of the Crystal Report Viewer with VB’s Object Browser

No Data Event

When you provide a user the ability to modify selection criteria for a report, it is possible that the user will pick criteria such that no data is returned to the report. The Report object in Crystal Report can intercept a no data condition and call a NoData event, we can write code to deal with that scenario.

To access the NoData event, right-click the Crystal report from the Designers folder in Project window of Visual Basic and choose View Code. In the Code window, select Report from the Object drop-down list on the left; the NoData event should be displayed in the Procedure drop-down list on the right. Listing 19.4 shows the code for displaying a message if a report has no data.

Listing 19.4: Displaying a Message If a Report Has No Data

Private Sub Report_NoData(pCancel As Boolean)          MsgBox "no data in report"     pCancel = True End Sub

For our report we have simply added a message that will be displayed if the report has no data. The NoData event has a pCancel argument. If you set this argument to True, as we have done, the report will stop processing and nothing will be displayed in the Crystal Viewer. Otherwise, a blank report will be displayed with report headings and titles.

Working with Pictures in a Report

Crystal Reports has the ability to display pictures that have been stored in a database in a report. Storing pictures in a database is okay for small amounts of data, but it is very inefficient to store many pictures. Pictures are stored as binary large objects (BLOBs) in a database. BLOBs are very inefficient, and some database administrators will not even allow BLOBs to be stored in a database. A better approach would be to store the name and path of the picture in the database and change the picture using code in the report.

To set up the report we added three database fields to the Details section of a report: First Name, Last Name, and File Name. The File Name field contains the name and path of the picture. We added a picture from the hard drive by choosing Insert > Picture from the Crystal Reports menu in the Report Designer. The picture we added was one of the pictures we want to modify via code. This way the Picture object added to the Crystal report will be the same size so we can format our report properly. From the Report Designer we suppressed the File Name field so it is not displayed on the report and changed the Picture Name property to Employee.

For the code, select the Format event for the Details section of the report as we did above in the section "Crystal Report Events". The FormattedPicture property of the Report object contains the picture to be displayed in the report. You set the picture by using the LoadPicture method in VB. The app.path statement gets the current path of the application and appends it to the name of the picture. Listing 19.5 shows show to change a picture at runtime.

Warning 

Modifying the picture must be done during the Format event; otherwise, the application will not work.

Listing 19.5: Changing a Picture at Runtime

Private Sub DetailSection1_Format(ByVal pFormattingInfo As Object)         Set CrystalReport1.Employee.FormattedPicture = _    LoadPicture(app.path & "\" & _     CrystalReport1.FileName.Value)      End Sub

Tip 

Crystal Reports can also display Word and Excel documents inside a report. You are not limited to just changing pictures as we demonstrated here. Refer to C:\Program Files\Crystal Decisions\Crystal Reports 9\ Samples\En\Code\Visual Basic\Change Runtime Location of OLE Object for sample code that Crystal Reports provides for manipulating Excel and Word. Your directory structure might be different if you did not install Crystal Reports in the default directory.

Changing Sort Criteria

We have modified the application we created in Figure 19.14 and added four option buttons (sometimes called radio buttons) so the users can choose the sort order of the report, and we have modified the Refresh command button to change the sort of the report when the report is refreshed.

The property that we need to modify on the Report object to change the sort sequence is the RecordSortFields property. However, working with this property is not as straightforward as working with the properties we have used so far. The RecordSortFields property is a Collection object that contains a group of fields on which you want the report be sorted. Think back to working with Crystal Reports; when you choose the sort order using the Record Sort Order dialog box, you can choose multiple fields that the report will be sorted on, for example, Sort by Country, then by Region. Each of the fields that you want to sort on is contained in the RecordSortFields collection. Listing 19.6 shows how to change the sort criteria.

Note 

By convention, the names given to Collection objects are usually plural so they can be more easily identified as collections, but this is not always the case.

Listing 19.6: Changing Sort Criteria

Private Sub cmdRefresh_Click()     Dim fd As CRAXDRT.DatabaseFieldDefinition          If optName.Value = True Then         Set fd = _ CrystalReport1.Database.Tables.Item(1).Fields(3)     ElseIf optCity.Value = True Then         Set fd = _ CrystalReport1.Database.Tables.Item(1).Fields(5)     ElseIf optState.Value = True Then         Set fd =  CrystalReport1.Database.Tables.Item(1).Fields(6)     ElseIf optCountry.Value = True Then         Set fd = CrystalReport1.Database.Tables.Item(1).Fields(7)     End If       CrystalReport1.RecordSortFields.Item(1).Field = fd     CRViewer91.Refresh      End Sub

We tell Crystal Reports on what field we want the report to be sorted in the line before we refresh report viewer, near the end.

CrystalReport1.RecordSortFields.Item(1).Field = fd

The RecordSortFields collection contains the group of Database field objects on which you want the report to be sorted. In our report we have only one sort criteria, so there is only one item in the collection. We access it though the Item property of the collection. Collections start at one item. The item in the collection represents a Database object on which you want to sort the report.

You would think you could set an item equal to a database field name such as CrystalReport1 .RecordSortFields.Item(1).Field = "{emp.name}" but this will not work. You have to set the item equal to a Database Field object because that item collection expects a DatabaseFieldDefinition object. At the top of the program we declare a variable called fd that will reference a DatabaseFieldDefinition object.

We will assign to the fd field a database field on which we want the report sorted. Crystal Reports keeps database fields in the Database object. The Database object contains a collection of tables, and the tables contain a collection of fields that are used in the report.

Note 

This list of tables and fields is not a complete list of tables and fields in the database; it is all the fields and tables you selected for the report.

In our program, we check to see which option button the user selected, such as Name or City, and then assign that field to the fd variable. Notice that we access the Tables collection the same way we access the RecordSortFields collection—through the item number in the report:

Set fd = CrystalReport1.Database.Tables.Item(1).Fields(3)

You might be wondering how we know that Item(1).Fields(3) represent the Name field in the database. Fortunately there is an easy way to figure it out. The Fields object contains a Name property, and you can write a loop to go through all the fields in the collection and determine which field is holding which database field, as we have done in Listing 19.7.

Listing 19.7: Techniques for Looping through a Collection

Private Sub cmdShowNames_Click()     Dim fds As CRAXDRT.DatabaseFieldDefinitions     Dim fd As CRAXDRT.DatabaseFieldDefinition          Dim counter As Integer          Set fds = CrystalReport1.Database.Tables(1).Fields          For Each fd In fds         counter = counter + 1         MsgBox counter & " " & fd.Name     Next               ' or the same using the For loop          Dim x As Integer          For x = 1 To fds.Count         MsgBox x & " " & _       CrystalReport1.Database.Tables(1).Fields(x).Name          Next x      End Sub

The VB language lets you loop through a collection of objects in two ways: using the For Each or For Next construct. Both constructs do the same thing; however, we find that some people are more comfortable with one or the other, so we are showing you both.

In the For Each construct, we have to declare a variable to represent the collection, fds in our case, and a variable to represent each item in the collection, fd in our case. We assign the collection to our fds variable:

Set fds = CrystalReport1.Database.Tables(1).Fields

The For Each loop will go through each object in the fds collection and assign each object to fd as it goes through the loop. We then can access the methods and properties of that item in the collection. The fd object has a Name property, and we simply display the name with a message to see which database field each object is holding.

In the For Each loop, we read the Count property of the collection (all collections have a Count property), to determine how many items are in the collection, and access each field through the loop variable X.

If we had multiple tables in our report, we could write similar code to loop through each of the tables.

Note 

When working with collections in Visual Basic, you can refer to the object by name or position in the collection. Unfortunately, the Crystal Reports Automation Server allows you to access the objects in a collection by position only.

We have demonstrated how to change the sort criteria at runtime in VB. However, we also demonstrated the concepts of working with collections and objects in the collection in the RDC. These techniques of working with collections can be used in all places in the RDC where there are collections. If you think about a Crystal report from an object-oriented point of view, you will realize that it is filled with collections, objects, sections, tables, fields, etc.

Working with Parameters

In this section we are going to work with a report that contains parameters and see how to manipulate it with code. The ParameterFieldDefinitions collection and the ParameterFieldDefinition object contain the parameters in the report. Refer back to the discussion on changing sort criteria to see how to work with collections and objects in a collection.

If the Report Designer contains a report with parameters, and you refresh the report, the report will prompt you for the required parameters. This might be how you would like the report to behave; however, you might want to set the parameters for the report via code and avoid prompting the user for the parameters. In that case, you can suppress the prompting for parameters by setting the EnableParameterPrompting property of the Report object to False:

CrystalReport1.EnableParameterPrompting = False

If we disable parameter prompting, we need to set the parameter values in code, as shown in Listing 19.8.

Listing 19.8: Setting Report Parameters

Private Sub Form_Load()     Screen.MousePointer = vbHourglass     CRViewer91.ReportSource = CrystalReport1     CrystalReport1.ParameterFields(1).SetCurrentValue "UK"     CrystalReport1.EnableParameterPrompting = False     Form2.CRViewer91.ViewReport          Screen.MousePointer = vbDefault End Sub

The setCurrentValue method sets the parameter of the report. In our case we are hard-coding the string "UK" for the parameter value. We then turn off parameter prompting and display the report.

Notice in this example that we are setting the parameters in the report when the form is being loaded, in the Form_Load event. Parameters must be set before you view the report in the Report Viewer. If you reset the report parameters after you view the report and refresh the report, the report will not display. The workaround for this is to add another Crystal Report Viewer to the form and set its Visible property to False. Then when you want to redisplay the report with the new parameters, you need to assign the new parameter values to the report, hide the first viewer, and show the new viewer, which will display the report with the new parameters. In our example we have two viewers, one on Form1 and another on Form2. Listing 19.9 redisplays a report after changing parameter values.

Note 

To add another viewer to a form, add a form to your VB project and then add the Crystal Report Viewer from the toolbox to the new form.

Listing 19.9: Redisplaying a Report after Changing Parameter Values

CrystalReport1.ParameterFields(1).SetCurrentValue "CN" Form2.CRViewer91.ReportSource = CrystalReport1 Form2.Show Form2.CRViewer91.ViewReport Unload Form1

By referring to the ParameterField object you can access properties of the parameter, such as its MaximumValue, MinimumValue, etc. In the example below, we display the name of the parameter; refer to the Report Designer Component Object Model in the help file for a list of all the methods and properties for the ParameterFieldDefinition object.

Dim p As CRAXDRT.ParameterFieldDefinition Set p = CrystalReport1.ParameterFields(1) MsgBox p.Name 

As you can see, setting and refreshing a report with parameters is not as straightforward as changing the sort or selection criteria. Report parameters were designed to give Crystal Reports the ability to mimic a simple application by prompting the user for data before displaying the report. If you are designing a report that is going to be used in an application, then you can design the report in such a way as to make working with the report easier in code. Since Visual Basic, or any development environment that supports Crystal Reports, has a much richer interface to get and receive input data from a user, you may want to avoid using parameters in a report for an application and dynamically set the selection criteria by setting record group selection formulas.

While we are on the topic of designing reports for an application, the easiest way to incorporate Crystal Reports into an application is to design the report to include components, such as sorting, grouping, parameters, etc., and modify the objects as we show them. The Automation Server allows you to start with a blank report and add sort criteria or groups to a report using the methods and properties of the various collections. However, it is much easier to work with a prebuilt report from Crystal Reports. Also, if you don’t have the Advanced Developer license, you may be unable to add certain objects to your report and you may need to work with a base report.

Exporting a Report

In your application you can give the user the ability to export your report to the various formats that Crystal Reports supports. The simplest approach is to provide the Crystal Report Export dialog box and let the user choose the export format. The following code displays the Crystal Reports Export dialog box, shown in Figure 19.16, and allows the user to choose the export format and the parts of the report to export:

Private Sub cmdExportUserDialog_Click()     CrystalReport1.Export End Sub

Figure 19.16. User choosing export format and destination

Alternatively, you can programmatically set the options for export in your program and suppress the Export dialog box. In Listing 19.10 we export the entire report to PDF format without prompting the user.

Listing 19.10: Setting Export Options in Code to Export a Report to PDF Format

 Private Sub cmdExportPDF_Click()     Dim ex As CRAXDRT.ExportOptions          Set ex = CrystalReport1.ExportOptions          ex.DestinationType = crEDTDiskFile     ex.DiskFileName = "c:\aExport.pdf"     ex.FormatType = crEFTPortableDocFormat     ex.PDFExportAllPages = True     CrystalReport1.Export (False)      End Sub

Export options in Crystal Reports are contained in the ExportOption object. To access this object, create a variable that will hold a reference to the ExportOptions object, ex in our case. The ExportOptions property of the Report object contains the reference to the ExportOptions object. Assign the ExportOptions property to the ex variable. Then assign the export options required. You need to set at minimum the Destination, the Export Format Type, and the number of pages to export. Once you set these properties, you pass a False to the Export method to indicate that you do not want to see the dialog box. Listing 19.11 exports the first page of the report to Excel.

Warning 

If you don’t specify all the required parameters, the application will prompt you for the missing parameter, or you will get a runtime error.

Listing 19.11: Setting Report Parameters

Private Sub cmdExportExcelPage1_Click()     Dim ex As CRAXDRT.ExportOptions          Set ex = CrystalReport1.ExportOptions          ex.DestinationType = crEDTDiskFile     ex.DiskFileName = "c:\aExport.xls"     ex.FormatType = crEFTExcel70     ex.PDFExportAllPages = False     ex.ExcelFirstPageNumber = 1     ex.ExcelLastPageNumber = 1     CrystalReport1.Export (False) End Sub

Setting other export destinations and formats is very similar; refer to the Export Options object in the help file for the additional formats.

Working with Formulas

Previously in this chapter we worked with Database Field Definition objects to change the sort criteria in a report. Working with formulas is very similar. The formulas in a report are contained in a FormulaFieldDefinitions collection, and you can access the formulas via the FormulaFieldDefinition object. In this example, our report has two formulas: one created using Crystal syntax and the other created using Basic syntax. Listing 19.12 shows how to change report formulas at runtime.

Listing 19.12: Changing Report Formuals at Runtime

Private Sub cmdFormula_Click()     Dim frs As CRAXDRT.FormulaFieldDefinitions     Dim fs As CRAXDRT.FormulaFieldDefinition          Set frs = CrystalReport1.FormulaFields          'figure out the location of each formula, remove this          'code when we go to production     dim x as integer      x = x + 1     For Each fs In frs         MsgBox x & " = " & fs.Name         X = x + 1     Next          'set the syntax to Crystal     CrystalReport1.FormulaSyntax = crCrystalSyntaxFormula          Set fs = frs.Item(1)     fs.Text = "'Owner = ' + {Owners.OwnerLastName} + ' ' + {Owners.OwnerFirstName}"          'Set the syntax to Basic     CrystalReport1.FormulaSyntax = crBasicSyntaxFormula          Set fs = frs.Item(2)     fs.Text = "formula =  {Owners.OwnerLastName}"          CRViewer91.Refresh End Sub

Each formula added to a report is added to the FormulaFieldDefinitions collection. As with sort fields, we need to reference the formula by its position in the collection. To determine which formula is in which position, we create a loop to go through all the formulas and view their names.

We then assign the formula from the collection to a Field Definition object and modify the formula by setting the Text property. Notice that we need to identify the syntax we are using by setting the FormulaSyntax property of the report before setting the formula. Make sure your formula syntax is correct or your report will have runtime errors.

Working with Graphs

Giving the user the ability to dynamically change a graph in an application at runtime is a very powerful feature. In this sample we have a report that was created with a bar graph in the Report Header. The user will be able to click a button in VB to change the graph from a bar graph to a pie graph. Listing 19.13 shows how to change a graph at runtime.

Listing 19.13: Changing a Graph at Runtime

Private Sub cmdPieGraph_Click()     CrystalReport1.Graph1.GraphType = crRegularPieGraph     CRViewer91.RefreshEx False End Sub

Notice in the code that we simply reference the Graph object in the Crystal Designer by name and change the graph type. We then refresh the report without hitting the database.

The Graph object contains a large number of properties that allow you to configure the graph’s appearance. Figure 19.17 shows the properties in the Properties window of VB; you can also refer to the project Graph object in the developer’s help file. In our example we changed only one of the properties. As with working with graphs in Crystal Reports, you may need to experiment with the various options available before you find the right settings to make your graph look good.

Figure 19.17. Graph properties

Working with Sections

A Crystal report contains various sections: Page Header, Report Footer, etc. Each of these sections in the Automation Server is an object that can be manipulated to control the appearance of your report. When working with sections in Crystal Reports, typical requirements would be to suppress a section or to go to a new page after a section. The following code sample demonstrates working with the Page Header section:

Private Sub cmdModifyPageHeader_Click()     CrystalReport1.ReportHeaderSection1.Suppress = True     CrystalReport1.ReportHeaderSection1.NewPageAfter = True     CRViewer91.RefreshEx False End Sub 

You can treat a report section as an individual object as we did; however, a section is also a collection. A section contains Crystal Reports objects, such as database fields, text objects, or graphs that make up the contents of that section. In the sample code in Listing 19.14 we are going to change the graph from a bar graph to a pie graph as we did previously. However, we will access chart by looping though the objects in the sections. You may be wondering why we would do this if we can simply change the graph in one line of code as we did above. As your reports become more complex, having the ability to loop through objects in a section will prove to be very useful. This technique allows you to also work with different objects in a section without having to access them by name in the Report Designer.

Listing 19.14: Looping through the Objects in a Section

Private Sub cmdLineGraph_Click()    Dim sc As CRAXDRT.Section    Dim gr As CRAXDRT.GraphObject    Dim obj As Object     Set sc = CrystalReport1.Sections(1)    For Each obj In sc.ReportObjects        If obj.Kind = crGraphObject Then            Set gr = obj            Exit For        End If    Next obj         gr.GraphType = crStackedLineGraph    CRViewer91.RefreshEx False End Sub

In this example we declare a Report Section object, a Graph object, and a generic object. We need to declare a generic object because a section can contain many different kinds of objects. We then assign one of the sections to the Section object so we can work with it.

   Set sc = CrystalReport1.Sections(1)

We then build a loop to go through all the objects in that section. Since obj is declared as a generic object, we can assign any object to it. All objects in the Automation Server have a Kind property that allows us to identify what type of object we are looking at so that we then know which methods and properties are available to use. In our case we are looking for a Graph object. When we find the Graph object, we assign it to the gr object variable and manipulate its methods and properties.

Note 

You can argue that assigning the generic obj object to gr is not necessary and that we also manipulate the graph with the obj variable. Yes, this is correct, but if we assign a generic object to a specific object, it makes our code easier to read and leads to fewer bugs.

Working with Subreports

Subreports are similar to sections when you deal with them in the Automation Server. A subreport can be considered another object in the report. However, a subreport is also a full report on its own, with all the objects that we have been working with so far.

In this example we are treating the report as single object and suppressing it.

Private Sub cmdSupressSubreport_Click()     CrystalReport1.Subreport1.Suppress = True End Sub

In the following example in Listing 19.15, we are accessing the subreport as a full report. Notice how we declare sRpt as a report variable that will reference a Report object. We then assign the subreport to sRpt by using the OpenSubreport method of the Report object. At this point, the sRpt object has all the methods and properties of the regular report, and we can manipulate the subreport’s methods and properties. To demonstrate this, we change the subreport’s RecordSelectionFormula using the same techniques previously used in the main report earlier in the chapter, and refresh the report.

Listing 19.15: Accessing a Subreport

Private Sub cmdChangeSubReport_Click()     Dim sRpt As Report     Set sRpt = CrystalReport1.Subreport1.OpenSubreport          sRpt.RecordSelectionFormula = "{Regions.RegionCode} = 'R02'"     CRViewer91.Refresh End Sub

So far in the chapter we have been working with our report through the Report Designer, CrystalReport1. However, in this case we have created an object variable that represents a report. The Crystal Report Designer is an object that represents a report, and it has a visual interface. In this example we still have an object that represents the report, but sRpt does not have a visual interface. We still have access to all the methods, properties, and collections of the report, but we need to access them via code instead of clicking them in the Report Designer. In working with sections we demonstrated how to loop through the objects in a section of a report to identify what kind of object it is and then manipulate the object via code.

That technique would be very useful when working with a subreport because we don’t have a visual interface to work with and would need a way to access the objects.

The example in Listing 19.16 is a continuation of the code above. In this example we loop through all the sections of the report and all the objects in the sections.

Listing 19.16: Looping through Sections in a Report

Private Sub cmdSubReportSections_Click()     Dim sRpt As Report     Dim obj As Object     Dim oSec As CRAXDRT.Section          Set sRpt = CrystalReport1.Subreport1.OpenSubreport     For Each oSec In sRpt.Sections         MsgBox oSec.Name                  For Each obj In oSec.ReportObjects                      MsgBox obj.Kind & " " & obj.Name               Next obj     Next oSec End Sub

Using this technique we are able to identify which section we are working with and what kind of object we are referencing in a section. This code is very powerful if you need to access or change many objects in a report.

Logging into a Database

Crystal Reports can connect to multiple tables for a report, and those tables can each have different connection information. To be able to deal with multiple connections, each table has a Connection property and Connection property collection that holds all the required information for a database connection. Think back to establishing a connection to a data source such as Microsoft’s SQL Server or Oracle. You needed to provide Crystal Reports information, the name of the database server, the name of the database to connect to on the server, the protocol used to connect to the database, your user ID, your password, etc. All of this information is contained in the Connection property. In Listing 19.17 we have a report that is connected to one table, and we print the connection information for that report.

Listing 19.17: Displaying Connection Information

Private Sub cmdViewConnectionInfo_Click()          Dim tables As CRAXDRT.DatabaseTables     Dim csProp As CRAXDRT.ConnectionProperties     Dim cs As CRAXDRT.ConnectionProperty          Set tables = CrystalReport1.Database.tables     Set csProp = tables.Item(1).ConnectionProperties          'if you try to print the password you will get a write only error.     'move the debug pointer to next to continue      For Each cs In csProp     Debug.Print cs.Name & " = " & cs.Value     Next End Sub

Figure 19.18 displays the output of the connection information. In this example we are connecting to Microsoft’s SQL Server and the sample pubs database. You can see all the connection information to the database except the password. The password is a write-only property, and if you try to print it you will get an error.

Figure 19.18. Connection information to SQL Server

The Connection property object is called a property bag type object. This means that it contains a set of name and value pairs for each connection. Look back to Figure 19.19, and you can see the name and the value for each item. Because each data source requires different connection information, the Connection Property object was designed as a property bag so it can contain different name and value pairs for different data sources.

You can access each of the values in the Connection object through its name. In the sample in Listing 19.18, we are prompting the user for a user ID and password before displaying the report.

Listing 19.18: Displaying a Report Only after a User Enters a User ID and Password

Private Sub cmdSetLogon_Click()     Dim tables As CRAXDRT.DatabaseTables     Dim csProp As CRAXDRT.ConnectionProperties     Dim cs As CRAXDRT.ConnectionProperty          Set tables = CrystalReport1.Database.tables          Set csProp = tables.Item(1).ConnectionProperties               csProp.Item("User ID") = InputBox("ID")     csProp.Item("Password") = InputBox("Password")     CRViewer91.ReportSource = CrystalReport1     CRViewer91.ViewReport End Sub

Switching Data Sources

You can also modify other properties for the Connection properties. In Listing 19.19, we are modifying the Datasource property, which identifies the database server, and the Initial Catalog property, which identifies the database to report from. This allows us to switch to a different SQL Server, perhaps from a test server to a production server.

Listing 19.19: Switching Databases

Private Sub cmdSwitchDB_Click()     Dim tables As CRAXDRT.DatabaseTables     Dim csProp As CRAXDRT.ConnectionProperties     Dim cs As CRAXDRT.ConnectionProperty          Set tables = CrystalReport1.Database.tables          Set csProp = tables.Item(1).ConnectionProperties          csProp.Item("Data Source") = "gmaric"     csProp.Item("Initial Catalog") = "pubs1"     csProp.Item("User ID") = InputBox("ID")     csProp.Item("Password") = InputBox("Password")          CRViewer91.ReportSource = CrystalReport1     CRViewer91.ViewReport      End Sub

In this example we worked with Microsoft’s SQL Server; different databases will keep different connection information in the connection property bag. We find that the easiest way to work with and identify this information is to create a Crystal report and establish all the connection information in the report through the Designer. Crystal Reports will add all the necessary information to the Connection property, and we can then loop through the names and value pairs as demonstrated above and modify them as required.

Note 

The example above will work only if you are switching databases for the same server type, for example, from one Oracle database to another Oracle database, which is the most common requirement. If you need to switch from SQL Server to Oracle, for example, then you will need to build the appropriate connection information and identify to Crystal Reports which driver and DLL to use. Refer to the Developer help file and search for “How to connect” in the Developer documentation.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback