Using the CASE Builder

The Builder provides a tool to aid in constructing CASE statements. CASE statements may be used as calculated fields or as selection criteria.

Example - Build a CASE Statement:

Look for null (or zero) AP invoice dates (RPDIVJ) and replace them with a predefined date to prevent errors, and then convert the remaining dates, to a true date calculated field.

1. On the Builder panel iSeries tab, click the CASE Builder button. The Case Builder panel appears.

4. Click OK on the Builder panel. The expression (&lib.F0411.RPDIVJ in this example) is pushed to WHEN field on the CASE Builder panel.

5. On the CASE Builder panel, use the drop down list to select the appropriate expression operator (equal (=) in this example). A new field appears to the right of the Expression Operator field.

6. In the new field, enter the desired value -or- click the Builder button adjacent to the new field to select a field from a table (zero (0) has been keyed in this example).

7. In the THEN field, enter the desired value -or- click the Builder button adjacent to the THEN field to select a field from a table (DATE('01/01/1900') has been keyed in this example).

8. Click the ADD Expression button. This starts the CASE statement in the CASE Builder work area.

9. If additional WHEN expressions are required, then edit the WHEN and THEN fields as necessary. When the revised expression is complete, click the ADD Expression button again. The revised expression is added to the current CASE statement in the work area. Repeat this process for as many fields as necessary for the CASE statement.

10. In the ELSE field, enter the desired value -or- click the Builder button adjacent to the ELSE field to select a field from a table (the Builder button is being used in this example).

11. In this example a Julian date is being selected and converted to a true date field (see Creating Date Fields).

On the Builder panel:

· Click the plus sign (+) to the left of Tables.

12. Click OK on the Builder panel. The expression is pushed to the ELSE field on the CASE Builder panel.

13. Click the ADD Else button. The else expression is added to the current CASE statement in the work area.

14. Click the Done button. The completed CASE statement appears the original Builder work area.

15. Click OK. The Field List panel appears, displaying the calculated field, with an alias of "Expr1", at the bottom of the list.

16. To modify the alias field name for the calculated field, on the Field List panel right click on the field and select Alias (AS nnnnnn) from the Field List popup menu. On the Modify Alias panel, enter the desired alias for the field and click OK. Refer to Assigning Aliases to Fields for more information.

5.5.3.4 EXD Row

The Builder functional button, %EXDROW%, is used to return the Excel row number for each row of data when the EXD is generated in Excel. This function may be used to create a calculated field, and may remove the need of using fill down columns in Excel.

Example - Using EXD Row Function to Create a Calculated Field:

Use the EXD Row function to create a calculated field which will calculate/report the amount paid by subtracting the open invoice amount column from the gross invoice amount column in Excel.

Initial Set Up:

1. Create a new query, retrieving gross invoice amount and open invoice amount (see below).

To Add a Calculated Field using the EXD Row Function:

1. On the Field List panel, right click on a field and Select Add a Calculated Field on the Field List popup menu. The Builder panel appears.

2. In the work area on the Builder panel, enter the appropriate to build the expression. In this example the following has been entered:

· Type ' (a single quote).

· From the operation buttons, click = (Equal).

· Type D (capital letter D).

· From the Misc tab, click %EXDROW%.

· From the operation buttons, click - (Minus).

· Type E (capital letter E)

· From the Misc tab, click %EXDROW%.

· Type ' (a single quote).

3. Click OK. The Field List panel appears, displaying the calculated field, with an alias of "Expr1", at the bottom of the list.

4. To modify the alias field name for the calculated field, on the Field List panel right click on the field and select Alias (AS nnnnnn) from the Field List popup menu. On the Modify Alias panel, enter the desired alias for the field (Amount Paid) and click OK. Refer to Assigning Aliases to Fields for more

information.

5. From the Ribbon, select Home>Save Query.

6. From the Ribbon, select Home>Single Record to execute the query. The ResultsContainer panel appears displaying one record.

7. In Excel, create an EXD formula with the following values. Refer to Creating and Using EXD formulas for more information.

· Select the query name identified in step 5.

· Specify the target worksheet.

· Type the Starting Cell/Range/Table.

· Select to Include Headings.

· Specify the cell reference for the Smart Parm.

8. From the ExecDash menu, select Generate All Query Detail Lists. The %EXDROW% created a calculation, subtracting column E from column D, for all rows included in the generated data output. This functions similar to the EXD fill down columns feature.

Nav Tip:This function may also be processed by selecting the EDash toolbar or ribbon equivalent (see EDash Toolbar or EDash Ribbon).