When I run a report, can I customize the menu item values that are displayed in our report? 

Environment:

Analytics
Oracle B2C Service

Resolution:

When creating reports that include a custom menu field, the specific menu item is restricted to be 40 characters. If you wish to customize the value displayed in your report, you can use the decode function or use embedded IF statements. In general, the decode statement looks like the following:

decode(table.c$menu_field_name), #, 'Custom Text 1', #2, 'Custom Text 2', #3, 'Custom Text 3', etc.)

You can also embed multiple IF statements, similar to the following format:

if(table.c$menu_field_name=#, ‘Menu Item 1’, (if(table.c$menu_field_name=#2, ‘Menu Item 2’, (if(table.c$menu_field_name=#3, 'Menu Item 3,')))))

Note: To determine the # values in the statements, open the custom field for editing and hover over the menu item.  The value that displays in the ID pop-up is the database value to use in the statement.

For example, if you have a custom menu field named Severity that you use with incidents, you might have four different menu options to set: Level 1 through Level 4. Within the database, though, these four options correspond to specific menu ID values such as:

6 - Level 1
7 - Level 2
8 - Level 3
9 - Level 4

As a result, if you include the Severity custom field (incidents.c$severity) in the output of your report, you can customize the values that display in your report that correspond to the menu ID values 6, 7, 8, and 9.

You can use a decode statement for the output column similar to the one below.

decode(incidents.c$severity, 6, '1: Site Down', 7, '2: Severe', 8, '3: Moderate', 9, '4: Minor')

The decode statement maps the numerical database values to the text strings you define within the single quotes for each item.

The default return value is given if there is no comparison value provided as shown on the syntax line below. This is useful if the field is nullable since decode doesn't accept NULL as a comparison value.

Syntax: decode(expression , compare_value, return_value [,compare, return_value] ... [,default_return_value] )

For additional information, refer to the 'Creating Basic Reports' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.