Setting Report Record Selection

In addition to setting the database credentials for our report, we can also set the record selection formula that is used to filter our report records. The record selection formula within your report is created whenever you use the Select Expert within the Crystal Reports Designer by right-clicking your report and selecting Report ^ Select Expert, and then clicking the table you wish to see.

This opens up a second , containing the information on that table. You can choose to hide or show the formula on this box, as shown in Figure 4-12.

Figure 4-12

Any selections you make in the Select Expert are translated into a record selection formula written in Crystal Syntax, and displayed in the textbox, as you can see in the preceding case.

If you click OK here, then the next time you run the report it will only return customers in the USA.

For more information on creating formulas and Crystal Syntax, turn over to Chapter 8, "Formulas and Logic."

You can also edit the record selection formula that is generated by right-clicking your report in the Report Designer and selecting Report ^ Edit Selection Formula ^ Records.

When your report is run, this record selection formula is translated into the WHERE clause for the SQL statement that is submitted to the database and the results are returned to Crystal Reports. So though you can't change the SQL statement your report is based on, you can control the records that are returned to the report.

At run time, the SelectionFormula property gives us the ability to return or set this value. To return a record selection formula, we would simply request the property as a string, as shown here:

CrystalReportViewer1.SelectionFormula.ToString()

Using the report and viewer we have been working with, we could set the record selection property when the form loads, before the call to the database, ensuring that only records for customers in the USA are shown:

Dim myConnectionlnfo = New CrystalDecisions.Shared.ConnectionInfo() CrystalReportViewerl.SelectionFormula = "{Customer.Country} = 'USA'" With myConnectionlnfo

Alternatively, another way we could use this property is to draw a drop-down list on our form with all of the different countries in the report and allow the user to select which country he or she wants to filter by. When working with record selection, you can come up with some pretty neat ways to deliver one report that could be used for many different types of users.

In Chapter 3, "Designing Reports," we looked at how a Crystal Report can use parameter fields to prompt the user for values that can be displayed on the report, used in record selection criteria, and so on. When viewing a report that has a parameter field present using the CrystalReportViewer, the standard parameter will appear when the report is run or refreshed, as shown in Figure 4-13.

Was this article helpful?

0 0

Post a comment