Can I use "parameter fields" for "SQL expression fields" in the crystal report "?

Source: Internet
Author: User
Can I use "parameter fields" for "SQL expression fields" in the crystal report "?

I write an SQL expression field in the SQL statement of the crystal report.


// Ideographic statement

Select sum (from segment 1) from table where from Segment 2 (character) = {? Parameter Self-segment}

You can select a record by passing parameters in the Parameter Self-segment.

The syntax error is prompted because the statement is not recognized.

Is this method feasible? If it is feasible, please help!

------------------------------------------------

Solution:

1. From the perspective of SQL expression field definition, you can only use the Structured Query Language (SQL) to write ......

SQL expression Field

SQL expressions are similar to formulas, but they are written in Structured Query Language (SQL) rather than in Crystal Reports formula language. SQL expressions can be used to query a specific data set from a database. You can sort, group, and select SQL expression fields. For an introduction to SQL expressions, see Crystal SQL designer.

2. merge "parameter fields" into "record Selection Formula.

Do not display all data in the report every time you open the report. You can create a parameter field to prompt you to specify the data you want to view. To reduce the amount of data transmitted from the database server, merge these parameter fields directly into the record selection formula.

In general, parameter fields provide users with interactivity. The user responds to the parameter prompts to specify the data to be viewed. However, directly combining the parameter fields into the record Selection Formula not only provides interactivity, but also reduces data transmission and improves performance.

You can add parameter fields to the selected record formula by selecting an expert or recording selected formula studio. When you use "record selected formula Studio", you can treat the parameter field as any other field.

In the following example, you will create a parameter field and then add it to the record Selection Formula with "select expert. For more details about designing and creating parameter fields, see parameter fields.

Add a parameter field to the selected record formula

Open the sample report group by intervals. Rpt (in the feature examples sample folder ).

Spend some time browsing the "Group" tree and paying attention to how data is organized. You can also see that 269 records are returned for this report in the lower right corner of the Crystal Reports window.

Note: This number is displayed only when the "status bar" option is selected in the "View" menu.

Click the design tab.

Click field resource manager on the View menu ".

In "field resource manager", right-click "parameter field" and select "new" from the shortcut menu ". The create parameter field dialog box is displayed.

In the parameter "name", type salesquota.

In the prompt text, type what was last year's sales quota? (What is the sales quota last year ?)

Click the value type list and select a number ".

Make sure that "discrete value" is selected, and then click "OK ".

Note: You have created a parameter field. The rest of these processes describes how to use "select experts" to add parameter fields to the record selection formula.

On the report menu, click Select expert ". The "select field" dialog box appears.

Select the customer. sales last year and click "OK ". Select an expert ".

On the customer. sales last year tab, click the drop-down list and select "greater ". A new drop-down list is displayed.

Click the second drop-down list and select {? Salesquota }.

Tip: click "show formulas" to view the selected formulas of the new record, for example, {customer. sales last year }> {? Salesquota }. You may not have used "select an expert", but created this formula in "record selected formula Editor. (To view the editor, open "formula Studio" and select "record selection" from the selection formulas folder ".)

In select experts, click OK ".

You have now added the parameter field to the selected record formula. When you switch to preview mode or click Refresh, you are prompted to enter a new parameter value. Then you can enter a value that represents the sales quota for the previous year. The final report only displays the customers whose "last year's sales volume" exceeds the specified value.

For example, if you set the parameter to 40000, the report will only show those customers whose sales last year have exceeded $40,000. Note that in the lower-right corner of the Crystal Reports window, only 58 records are returned for the report, instead of 270 records returned before you put the parameter field into the selected formula.

By improving the report, you can retrieve all required information and ensure that the number of records transmitted from the database server is minimal.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.