Adding a Data Source to Your Report

Locate the type of datasource you would like to add to your report and expand the data source folder. Depending on what type of datasource you select, an additional dialog may open. For example, if you select ODBC (RDO), a second dialog will open and prompt you for an ODBC data source. Likewise, if you select Access/Excel (DAO) under More Data Sources, a second dialog will open and allow you to select a spreadsheet or database to report from.

In this instance, we are working with the ODBC data source labeled "Xtreme Sample Database 2003," so once you have selected this data source, your Database Expert should look something like Figure 3-3.

Figure 3-3

There are three additional nodes shown underneath your datasource: The Tables and Views nodes contain a list of all of the tables and views in the data source you have selected, and the Add Command option allows you to enter a SQL statement to serve as the basis for your report, which we will look at a little later.

You can also report off of stored procedures, system tables, and other objects. To turn this option on, right-click your report and select Designer ^ Default Settings. Under the Database tab, click the show options for the types of objects (tables, views, stored procedures, and so on) you want to report from.

In this case we will be reporting off of tables within our sample data source, so expand the Tables node, select the Customer table, and then use the right-arrow icon to add it to the list of selected tables. Likewise, select the Orders table using the same process.

Because we have selected two tables, an additional Links tab will appear on the Database Expert (shown in Figure 3-4). This tab will allow us to specify the relationship between these two tables.

Figure 3-4

Using the options shown in the Links tab, you can draw links or joins between the databases and tables in your report to indicate the relationship between each. To specify a link between two fields, drag the first field and drop it on top of the second.

If you make a mistake, you can remove a link by clicking the line to highlight it and then pressing the Delete key or, to clear all links, clicking the Delete button on the right side of the expert. This option is especially handy when Crystal Reports automatically attempts to use smart links in the tables you have selected.

By default, Crystal Reports will join two SQL tables with an Inner Join. To change the default join type, right-click directly on top of the line drawn between the two tables and select Link Options from the right-click menu to open the dialog shown in Figure 3-5.

Figure 3-5

Using the Link Options dialog, select a join type for this link from the list. Your choices are:

□ Right Outer Join

You can also use this dialog to select a link type using one of the comparison operators (=, >, >=, <, <=, and !=). When you are finished specifying the join type, click the OK button to return to the Links tab.

If you are working with a large number of tables, you may want to consider using the Auto-Arrange option found on the right-hand side of the Links tab. Click the Auto-Arrange button, and Crystal Reports will arrange the tables within this dialog to make viewing the layout and joins a bit easier.

By default Crystal Reports will auto-link the tables you have selected for your report, based on the field name and key fields found in the tables. To use this option yourself, select either the By Name or By Key options within the Links tab, and then click the Auto-Link button.

If you are unsure about how accurately this will link the tables in your report, don't forget that you can always remove individual links by highlighting the link to be deleted and then either pressing the Delete key on your keyboard or clicking the Delete Link button. You also have the option of removing all links by clicking the Clear Links button. If you have multiple links, you can use the Order Links button to specify in what order these links will be evaluated.

With your database linking in place, click OK to return to your report design. You can now start adding fields to your report. At the end of the last chapter, we had a look at the different types of fields you could add to a report. For the sample report we are working on, we want to add some database fields, as we will be using these fields later to demonstrate some advanced reporting features.

To do so, open the Field Explorer and drag the following fields onto your report's detail section:

Customer.Customer Name

Customer.City

Customer.Region

Customer.Country

Orders.Order Date

Orders.Order Amount

Your report should now look something like Figure 3-6.

Was this article helpful?

0 0

Responses

  • vera lorenzo
    How to add a datasource to a crystal report?
    8 months ago

Post a comment