لطفا منتظر باشید ...
Working with Data Sources
When working with data within the Report Designer, most of the options and functionality relating to databases and tables can be found in the Database menu found under the main menu by right-clicking in your report.
In the following sections, we are going to look at some of the most common tasks when working with data in our reports.
Setting Database Options
Crystal Reports.NET has a number of options that are specific to working with data sources and can be set once for the design environment. To see these settings, open any report, right-click, and select Designer | Default Settings | Database, which will open the dialog shown overleaf:
You can select the database objects you wish to show when creating a Crystal Report, including:
Tables
Views
System Tables
Synonyms
Stored Procedures
Important
This is where most developers get tripped up when working with stored procedures. They won''t appear as an available data source when you connect to your server UNTIL you turn the Show... Stored Procedure setting on.
You can also set a filter for database objects, using the Table name LIKE and Owner LIKE textboxes. Use the (%) symbol for multiple characters and the underscore (_) to indicate a single character. If you were looking for all objects owned by user DMCAMIS, the Owner LIKE text would be DMCAMIS%.
With the options in the middle of the dialog, select whether to list the tables and fields by their Name, Description, or Both, and use the checkboxes beside these options to sort table and field names alphabetically.
Finally, in the bottom of the dialog, under Advanced Options, select from the following:
Use Indexes or Server for Speed - Use existing database indexes or the database server itself for processing where a performance improvement could be gained.
Perform Grouping on Server - If you have created a summary report with none of the details showing and no drill-down capabilities, you can push the grouping of that report back to the server. This improves performance, because Crystal Reports.NET doesn''t have to get all the records and do the grouping itself. A GROUP BY clause will be inserted into the SQL that Crystal Reports generated.
Database Server is Case Insensitive - By default, Crystal Reports.NET is case sensitive, meaning {Customer.Country}=" ca" and {Customer.Country}="CA" would return different data sets. This setting eliminates that case sensitivity for SQL databases.
Select Distinct Data for Browsing - When browsing for data from pull-down or browse dialogs, this ensures that only a distinct recordset (no duplicates) is returned.
Since some of these changes relate directly to the database you are working with, you may need to log off and log back on for them to take effect.
Adding a Database or Table to a Report
When designing a report, you will need to add additional databases or tables from time to time as the need arises. In the sample reports included with this chapter, there is an Employee Profile (Employee_Profile_Basic) that lists employee names, birth dates, and hire dates. It does not list the employee''s city - that information is held within another table that we are going to add to the report.
To start with, open Employee_Profile_Basic in Visual Studio .NET by double-clicking on the solution (.sln) file within that folder. Double-click on employee_profile.rpt to open it in the Report Designer. Look at the four fields that are displayed in the report, and then look in the Field Explorer (View | Other Windows | Document Outline) where you will see the Employee table under Database Fields:
We want to add the City to the report, but it isn''t available from this table, so we are going to have to add the table that it is in. Right-click anywhere within the report, and select Database | Add/Remove Database, to open the Database Expert dialog, which you can then use to add additional data structures to the report.
Locate the Xtreme Sample Database and expand the node to find the table you wish to add, in this case, Employee_Addresses. Select the table name and click on the arrow icon to add it to the right window, which indicates that it has been added to your report. You can add further databases or tables here. When you have finished, select the Links tab, which allows you to specify the relationships between these tables.
You may need to draw the link(s) to indicate the relationship between the new tables you have added to the tables currently in your report. On this occasion, the link between Employee and Employee_Addresses was automatically generated. By clicking on the link, and then on Link Options, we can set the options for the join types that these links represent (detailed in the next section). When you have finished, click OK to exit the Database Expert and return to your report design.
The first thing you should look for is your new table in the Field Explorer:
If you expand the Employee_Address table you can now add some extra items to the report that you couldn''t see before, for example, Country, Postal Code, and of course, City.
Drag City from the Field Explorer to the Details section of your report, and position a label directly above it in the Page Header. You have now successfully added another table to your report, and to prove this you should be able to see a field from this new table in your report. Click on the Start button in Visual Studio .NET to open your new report, which should look something like the following:
This report should now be similar to Employee_Report_Table that is saved in the Chapter06 folder of the code download.
You may want to adjust the linking in the Visual Linking Expert, or check the database schema for more information on how the tables should be joined together, which is described in the next section.
Using the Visual Linking Expert
Relational databases are usually split into a number of different tables - these tables can be joined back together to create complex queries. In Crystal Reports.NET, these joins are created by using the Visual Linking Expert to visually draw a line between two key fields and setting options on these links to indicate join types.
In addition to specifying database linking when you first add a data source to your report you can also invoke the Visual Linking Expert at any time by right-clicking on your report and selecting Database | Visual Linking Expert from the right-click menu that appears (this is the same dialog that you used a moment ago to set up the links).
Using the dialog, you can draw links between the databases and tables in your report to indicate the relationship between each. To draw a line between two fields, imitate dragging the first field and dropping it on top of the second. You will know you have the field positioned correctly when your cursor turns into the shortcut icon.
If you make a mistake, you can remove a link by clicking on the line to highlight it and pressing the delete key, or to clear all links, use the button of the same name on the right-hand side of the expert.
Important
Our earlier example was very simple - in the Visual Linking Expert you can create multiple links between tables if your database schema requires them.
By default, Crystal Reports will join two SQL tables with an Equal join. To change the default join type, right-click directly on the line drawn between the two tables and select Link Options from the menu.
Using the Link Options dialog, select a join type for this link from the list below:
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
You can also select an operator to work with the join type you have selected, including:
Operator
Description
=Equal To
>Greater Than
>=Greater Than or Equal To
<Less Than
<=Less Than or Equal To
!=Not Equal To
At any point you can click the Auto-Arrange button to arrange the Visual Linking Expert layout for readability, but sometimes you will get better results if you position the tables yourself.
If it''s still open from the last example, take a look at employee_profile.rpt in Crystal Reports.NET. The report has been created from the Employee and Employee_Addresses tables. To see the SQL that Crystal Reports.NET has generated, right-click on the report, and select Database | Show SQL Query:
SELECT ''Employee''.''Last Name'', ''Employee''.''First Name'', ''Employee''.''Birth Date'',
''Employee''.''Hire Date'', ''Employee_Addresses''.''City''
FROM ''Employee'' ''Employee'' INNER JOIN ''Employee Addresses'' ''Employee_Addresses''
ON ''Employee''.''Employee ID''=''Employee_Addresses''.''Employee ID''
The fields you select in your report control the contents of the SELECT statement, but it is the links that control the FROM clause. When working with multiple tables or a large database, your database administrator should be able to give you some guidance on how the tables should be arranged and joined together.
If you find working with links in Crystal Reports.NET difficult, you can always use a SQL command as the data source for your report, and perform any joins in the SQL statement you write.
Verifying Database Structures Used in Your Report
As your database structures evolve and change, Crystal Reports you have created from these structures may no longer work due to differing field names and types. To ensure that the changes made in the database are reflected and accounted for in your existing reports, you will need to verify the database that they were created on by selecting Database | Verify Database from the main right-click menu.
If you have databases or tables in your report that are not used, you may receive the message Verify files in report that are not used? Click Yes to proceed.
At this point, Crystal Reports.NET will run through the data structures in your report and verify that nothing has changed. If all of the data structures are unchanged, The database is up to date will be displayed on your screen.
If anything has changed in the data structures, you will receive a message informing you of this, and that Crystal Reports.NET is proceeding to fix the report:
If Crystal Reports.NET finds simple changes, like a database field has been extended, or a decimal place changed, it will simply update its version of the data structures and display the message The database is up to date.
If Crystal Reports.NET finds a major change (like a field name missing, or a changed field type) it will open a Map Fields dialog. A list of unmapped fields will appear in the upper left-hand corner of this dialog. These are fields that are currently in your report that Crystal Reports.NET could not find when it attempted to verify the underlying data structure. To resolve any mismatched fields, select a field out of the dialog with Report Fields at the top, locate its counterpart in the list on the right-hand side, and click Map.
If the type of the field has changed as well as the name, uncheck the Match Type option to show all fields.
When you have finished mapping all of the fields that were not found in the verification of the data structures, you can return to your report design and Crystal Reports.NET will use these mapped values in place of the missing fields.
Changing a Database Location
Another handy feature is the ability to change the location of the database that your report uses. For example, you can design a report on your test database, and then later point it to a production version. To change the location of the database in your report, right-click and select Database | Set Location and from the dialog shown, select the database or table you want to point to.
Using the Data Explorer tree on the right, locate the data source and database or table you wish to change the location to, highlight, and click the Replace button.
If you are using multiple databases or tables in your report and subreports, there is a checkbox in the bottom left-hand corner with the option of Apply this change to subreports. Turning this option on will change the database location for all other subreports as well.
If the data structures are different between the old database or table and the database in the new location you have selected, the Map Fields dialog will appear, and you must map any unfound fields in your report to fields in the new database structure.
Setting a Database Alias
Aliases are used when you need to reference a table in a report more than once. A common example would be where you had an employee table with a supervisor ID that was also the employee ID of the supervisor. To get Crystal Reports.NET to reference the same table, you would need to add it to the report a second time, and give it an alias like EmployeeSupervisor.
To set a database alias, open the Database Expert by selecting Database | Add/Remove Database. In the window on the left, find and then double-click on the database or table you want to apply the alias to, and the following dialog will open.
Select Yes, and enter the new alias for the database in the Alias Name dialog.
You will see your new alias in the right-hand window of the Database Expert, with the alias you specified in the previous dialog. You can edit this name by selecting the table in the Selected Tables window, and pressing F2.
This will only change the alias of the data source within Crystal Reports.NET and will not touch the underlying SQL statement. Selecting OK will accept this and present you with the Visual Link Designer, which you can use to change the links between the different tables as we described earlier in this chapter.
