Consultor Eletrônico

Status: Unverified

GOAL:

SQL: Can OpenEdge Subscripted Array References be used in Crystal Reports?

GOAL:

Where can the OpenEdge Array Subscript notation be used in Crystal Reports?

GOAL:

How to use OpenEdge Array Subscript syntax with Crystal Reports SQL Command objects, SQL Expression Fields and Formula Fields?

GOAL:

How to create Crystal Reports SQL Command objects, SQL Expression Fields and Formula Fields that use OpenEdge Subscripted Array References?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.1x
OpenEdge 10.2x
Crystal Reports 11 (XI)
Crystal Reports 2008

FIX:

Yes, the OpenEdge Subscripted Array References may be used in Crystal Reports with the SQL Command objects, SQL Expression Fields and the Formula Fields. This solution describes how to create each of these three Crystal Reports objects and how to use the OpenEdge Subscripted Array syntax with them:
A. Creating Crystal Reports SQL Command object using the OpenEdge Array Subscript notation:
The Crystal Reports Command Objects allows developers to create their reports based on their own SQL queries. A Command object creates a virtual table whose fields are the SELECT phrase column list. Any of these virtual table fields may be used in the design of Crystal report. The following procedure demonstrates the use of the OpenEdge Array Subscript notation in a Crystal Reports Command object definition:
1. In the Database Expert, expand the Create New Connection folder and browse the subfolders to locate your data source.
2. Log on to your data source if you are not already connected.
3. Under your data source, double-click the Add Command node.
4. In the Add Command to Report dialog box, enter the following SQL query that uses the OpenEdge Array Subscript notation:
SELECT
"Salesrep1"."MonthQuota"[1] AS "January",
"Salesrep1"."MonthQuota"[2] AS "February",
"Salesrep1"."MonthQuota"[3] AS "March",
"Salesrep1"."MonthQuota"[4] AS "April"
FROM
PUB."Salesrep" AS "Salesrep1"
5. Click OK to return to the Report Designer.
6. In the Field Explorer, a Command table appears under Database Fields with the four fields specified in the query.
7. These fields may now be inserted in the Crystal Report like any other database TABLE or VIEW field.
Notice that the individual array elements in this example are returned in their NUMERIC native data type.
B. Creating Crystal Reports SQL Expression Fields using the OpenEdge Array Subscript notation:
The Crystal Reports SQL Expression Fields are calculated fields created using SQL to be used in a Crystal Report. The SQL Expression Fields may be used to sort, group and select the report data. The following procedure demonstrates the use of the OpenEdge Array Subscript notation in a Crystal Reports SQL Expression Field definition:

1. Click: View > Field Explorer from the main Crystal Reports menu.
2. Right-Click: The SQL Expression Fields and Select: New to access the SQL Expression Name dialog box.
3. Type 'December' for the SQL Expression Name box and click OK to access the Formula Workshop.
4. Type the expression: "Salesrep1"."MonthQuota"[12] in the SQL Expression Editor.
5. Select the 'Save and close' button or CTRL+S to dismiss the SQL Expression Editor and return to the Report Designer.
6. In the Field Explorer the new 'December' field appears under SQL Expression Fields.
7. This field may now be inserted in the Crystal Report like any other database TABLE or VIEW field.
Notice that the i.ndividual array element in this example is returned in its NUMERIC native data type.

C. Creating Crystal Reports Formula Fields using the OpenEdge Array Subscript notation:
The Crystal Reports Formula Fields are calculated fields defined in the Crystal Reports Formula Language to be used in a Crystal Report. The following procedure demonstrates the use of the OpenEdge Array Subscript notation in a Crystal Reports Formula Field definition:
1. Click: View > Field Explorer from the main Crystal Reports menu.
2. Right-Click: The Formula Fields and Select: New to access the Formula Name dialog box.
3. Type 'January' in the Formula Name dialog box and click OK to access the Formula Editor dialog box.
4. Type the expression: ToNumber({Salesrep1.MonthQuota}[1 to InStr({Salesrep1.MonthQuota}, ";") - 1 ])
5. Select the 'Save and close' button or CTRL+S to dismiss the Formula Editor and return to the Report Designer.
6. In the Field Explorer the new 'January' field appears under Formula Fields.
7. This field may now be inserted in the Crystal Report like any other database TABLE or VIEW field.
Notice that the individual array element in the above example is extracted as a STRING and is then converted to its NUMERIC native data type using the Crystal Reports Formula Language.
Obviously it is less efficient to use the OpenEdge Array Subscript notation with the Crystal Reports Formula Fields than with either Command Objects or SQL Expression Fields..