Info

the database is located

UserID

Returns or sets a user name for logging on to the data source

We looked briefly at these properties and methods in Chapter 3, "Designing Reports," but we didn't tackle looping through the database. We'll look at that now.

Drag another button onto your Form, and call it Database_Button. Change the Text property to Northwind Report. We'll create a new Form with this button so right-click the project name, select Add ^ Add New Item and then, out of the dialog that pops up, select Windows Form. The default name will be Form2.vb, which is as good as any.

Double-click our new button, and insert the following code:

Private Sub Database_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Database_Button.Click Dim Form2 As New Form2() Form2.Show() End Sub

Now, drag a CrystalReportViewer onto Form2 in the Design mode, right-click the project to Add ^ Add Existing Item..., Browse to C:\Crystal.NET2 003\Chapter09\worldsales_northwind.rpt (this location will vary depending on where you have downloaded the sample code to), and add this report to the project.

Next, drag a ReportDocument component onto the Form and, when the dialog opens, select engine_basic.worldsales_northwind.

The next step is to add some additional code to set our Connectionlnfo class.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim myReport As New worldsales_northwind() CrystalReportViewerl.ReportSource = myReport myReport.Load()

Dim myDBConnectionlnfo As New CrystalDecisions.Shared.ConnectionInfo()

With myDBConnectionlnfo .ServerName = "localhost" .DatabaseName = "Northwind" .UserlD = "sa" .Password = "" End With

If you are using a secured Microsoft Access, Paradox, or other PC-type database, the same method can be used, except the .ServerName and .DatabaseName are left blank.

Then, we can apply this Connectionlnfo by looping through all of the tables that appear in our report:

Dim myTableLogOnlnfo As New CrystalDecisions.Shared.TableLogOnInfo()

Dim myDatabase = myReport.Database Dim myTables = myDatabase.Tables

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

For Each myTable In myTables myTableLogOnlnfo = myTable.LogOnlnfo myTableLogOnlnfo.Connectionlnfo = myDBConnectionlnfo myTable.ApplyLogOnInfo(myTableLogOnInfo) Next End Sub

In this instance, we are looping through the tables using the table object. You can also loop through the tables through the item and the table name or index.

For instance:

myReport.Database.Tables.Item(i).ApplyLogOnInfo() But, it's up to you.

Setting a Table Location

Another handy trick that the Report Engine provides is the ability to set the location for tables that appear in our report. (This is the equivalent of going into the Report Designer, right-clicking, and selecting Database ^ Set Location.)

This can be useful for occasions when you have to put historical data into another table or want to separate out data in different tables for different business units, but the structure of the "source" and "target" table have to be the same, or you will get errors when the report is run.

When working with the location of a table, the Location property will both return and set where the table resides.

The example that we are now going to build demonstrates how the location of a table in a report could be changed to point to a "current" employee table. In your project, right-click the project name, select Add ^ Add New Item..., and choose Windows Form. The default name should be Form3.vb. Click Open. Drag a button onto the Design view of Forml.vb, call the button Location_Button, and change the Text property to Set Database Location. Double-click this button, and insert the following code:

Private Sub Location_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Location_Button.Click Dim Form3 As New Form3() Form3.Show() End Sub

We shall use employee_listing.rpt to demonstrate the point. This report is already attached to the project so we do not need to add it. However, what we do need to do is to go into our Xtreme database and create a copy of the Employee table in Access. This copy should be named Employee_Current. Add a few more employee rows onto the end of the table (just so that the information is slightly different), and save it.

There are several versions of Xtreme supplied from various sources, including the ones included with both Crystal Enterprise and Microsoft Visual Studio .NET. Make sure that the version you alter and the data source the report is referencing are the same!

The next thing to do is prepare Form3.vb. In the Design view of this Form, drag on a CrystalReport Viewer and a ReportDocument component. When the dialog for the ReportDocument comes up, select engine_basic.employee_listing.

All that remains is to insert the following code:

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load MsgBox("Note: To make this sample work, open the Xtreme sample database in Access and copy the Employee table to Employee_Current and change some values. You should see these changes when you view the report, indicating the set location worked correctly")

Dim myReport As New employee_listing() CrystalReportViewerl.ReportSource = myReport myReport.Load()

Dim myDatabase = myReport.Database

Dim myConnectionlnfo As New CrystalDecisions.Shared.ConnectionInfo() Dim myTableLogonlnfo As New CrystalDecisions.Shared.TableLogOnInfo()

Dim myTables = myDatabase.Tables

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

For Each myTable In myTables

MsgBox("Before: " & myTable.Location.ToString())

If myTable.Location.ToString() = "Employee" Then myTable.Location = "Employee_Current" End If myTable.ApplyLogOnInfo(myTableLogonInfo) MsgBox("After: " & myTable.Location.ToString()) Next

CrystalReportViewerl.ReportSource = myReport CrystalReportViewer1.Refresh() End Sub

We're good to go. Run the application, and click the Set Database Location button. Various dialogs should appear that advise you on the changes in the location since the tables cycle through the For... loop. Eventually, the report will load, showing the changes you have made.

You could also use this feature to point to a table that resides on a completely different database platform (from SQL Server to Oracle, for example), as long as the table definitions are compatible.

If you want to ensure that your report has the most recent instance of the data you are reporting from, prior to your export, you can use the Refresh method to refresh your report against the database.

Setting the Join Type

For reports that are based on more than one table, Crystal Reports .NET has a visual linking tool that allows you to specify the links or joins between these tables, as shown in Figure 9-4:

To see this dialog, open the Report Designer, right-click your report, and select Database ^ Visual Linking Expert

When working with these tables and links at run time, it can be confusing when working with all of the different elements involved so we'll break it down.

Similarly with Tables, there is a TableLink object that is contained in a TableLinks collection, which has one TableLink object for every link that appears in your report.

Keep in mind that tables can have multiple links between them. For example, you may have only two tables, but there may be three key fields that are linked together between those two tables.

Figure 9-4

A TableLink has the following properties:

Property

Description

DestinationFields

Returns a reference to table link destination Database-FieldDefinitions collection

DestinationTable

Returns a reference to the table link destination Table object

JoinType

Returns a summary of the linking used by the table

SourceFields

Returns a reference to table link source

SourceTable

Returns a reference to the table link source Table object

So, to determine the tables and database fields used in linking our tables together, we can loop through all of the links used in our report. We'll look at how we do this now.

Drag another button onto Form1 in the Design view, and name it Links_Button. Change the Text property to Show Links. Double-click the button, and add the following code:

Private Sub Links_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Links_Button.Click Dim myReport As New employee_listing() myReport.Load()

Dim myDatabase = myReport.Database Dim myTables = myDatabase.Tables

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table Dim myLinks = myDatabase.Links

Dim myLink As CrystalDecisions.CrystalReports.Engine.TableLink

For Each myLink In myLinks

MsgBox("Destination Table: " & myLink.DestinationTable.Name.ToString &

"." & myLink.DestinationFields.Item(1).Name.ToString()) MsgBox("Source Table: " & myLink.SourceTable.Name.ToString & "." &

myLink.SourceFields.Item(l).Name.ToString) MsgBox("Join Type: " & myLink.JoinType.ToString)

Next End Sub

Compile and run. The dialogs should now appear, one after the other, bearing the name of the source and target links and also the join type, as shown in Figure 9-5.

engine hasic

dresses Employee ID

Figure 9-5

Figure 9-5

Keep in mind that these properties are read-only. You will not be able to set the table linking using these properties. If you do want to change the database linking that is used, you may want to consider pushing the data into the report using a dataset.

Pushing Data into a Report

Earlier in our discussion of the different ways you could deploy a report in Chapter 1, "Crystal Reports .NET Overview," we looked at "Push" and "Pull" type reports. Up until this point, we have been working exclusively with "Pull" reports in which we pull the information from the database and display it in our report.

For "Push" reports, you actually create the report the same way, except that, when the report is run, you can "Push" a dataset to the report, as we did in Chapter 6, "Creating SML Report Web Services." This works in a similar manner to actually setting the data source for an individual table, but, instead of setting the property equal to another table, we are going to set it equal to another data source. In the following example, we are using our sample report that we have been working with, but, instead of data from the Xtreme sample database, we are actually connecting to the Northwind database on SQL Server to get the data we need.

Dim query = "select * from Customer"

Dim MyOleConn As New System.Data.OleDb.OleDbConnection(conn) Dim MyOleAdapter As New System.Data.OleDb.OleDbDataAdapter() Dim MyDataSet As Data.DataSet

MyOleAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(query, MyOleConn) MyOleAdapter.Fill(MyDataSet, "Customer")

myReport.Database.Tables.Item("Customer").SetDataSource(MyDataSet)

So, instead of actually changing the links and tables that are used within the report, we are actually just pushing another set of data into those structures. As you work with this feature, you are going to pick up some tricks along the way, and one of the handiest tricks is to use a SQL command as the basis of your report. This makes pushing data into the report easier since a report based on SQL command treats the resulting data as if it were one big table.

From your application, you can then get a dataset that matches the fields in your SQL command and then push the data into that one table (instead of having to loop through multiple tables).

Working with Report Options

Another basic task when working with data sources and Crystal Reports is the setting of some generic database options for your report, which are set using the ReportOptions class that relates to the report you are working with. Some of these options correspond to the options available in the report designer when you select Designer ^ Default Settings, but a few (like EnableSaveDataWithReport) are not available in the Report Designer, only through the object model.

Property

Description

EnableSaveDataWithReport

Returns or sets the Boolean option to automatically save database data with a report

EnableSavePreviewPicture

Returns or sets the Boolean option to save a thumbnail picture of a report

EnableSaveSummariesWithReport

Returns or sets the Boolean option to save the data summaries you create with the report

EnableUseDummyData

Returns or sets the Boolean option to use dummy data when viewing the report at design time (Dummy data is used when there is no data saved with the report)

A common use of these types of properties is for saving a report with data to send to other users. These properties can be used in conjunction with the Refresh and SaveAs methods to save a report with data that can be distributed to other users.

To test this, just alter the code in Print_Button code in Forml as follows: myReport.Export()

myReport.ReportOptions.EnableSaveDataWithReport = True myReport.Refresh()

myReport.SaveAs("c:\CrystalReports\Chapter09\saved.rpt",

CrystalDecisions.[Shared].ReportFileFormat.VSNetFileFormat)

MsgBox("Your report has been exported in PDF format and saved to

C:\CrystalReports\Chapter09\test.pdf and your original report has been saved to C:\CrystalReports\Chapter09\saved.rpt")

End Sub

Even if the user doesn't have Crystal Reports or Crystal Reports .NET, a simple viewer application created with Visual Studio .NET is all you need to view the report in its native format (or, if you export to PDF, the Acrobat viewer). The code for a sample viewer is included in the code samples for this chapter.

Setting Report Record Selection

When working with Crystal Reports, you will probably want to use record selection to filter the records that are returned. This record selection formula translates to the WHERE clause in the SQL statement that is generated by Crystal Reports.

You can see the record selection formula for a report by right-clicking your report and selecting Report ^ Edit Selection Formula ^ Records. This will open the formula editor, as shown in Figure 9-6, and allow you to edit your record selection formula.

The record selection formula within Crystal Reports is written using Crystal Syntax so you may want to review the section on Crystal Syntax in the previous chapter.

You can retrieve the report's record selection and set it using the same property, as we saw in the examples in Chapter 4, "Report Integration for Windows-Based Applications":

myReport.RecordSelectionFormula = "{Employee_Addresses.Country} = 'USA'"

Whenever the report is run, this record selection formula will be applied, and the report will be filtered using the formula specified.

You may look through the object model trying to find where to set the SQL statement that Crystal generates. At this point, your only two options for working with the SQL are to set the record selection (using the method just discussed), which will set the WHERE clause, or creating your own dataset using your own SQL statement and then "pushing" the data into the report.

Was this article helpful?

0 0

Post a comment