Business Objects Training

October 2008 e-newsletter

tip: Using the If and Match functions to group data values
With the If function in Web Intelligence you can create a variable that will group data values. In our example, we have colors of products and their sales. We are going to group the individual colors into three groups — black, blue and brown. Additionally, we are going to use the Match() function so that we do not have to list the colors individually.

Our current report shows the following data:

We want to group the colors into three categories. If the color begins with black then it will be in the black category; if the color begins with blue, it will be in the blue category; and if the color begins with brown, it will be in the brown category.

We will create a new variable. Click the Variable Editor button on the Reporting Toolbar:

The Variable Editor is displayed:

Give the variable a name. In this example, the variable will be named:
v_Color Grouping

We will use the If() function and the Match() function to group our colors.

The syntax for the If() function is:

If ([condition to test]; what to do if the condition is true; what to do if the condition is false)

NOTE: This is sometimes called an If/Then/Else statement.

We are going to use a nested If. This means that for the "false" part of the function we will use another If() statement. And, within the nested If() function, we are going to use the Match() function so that we do not have to specify all of the colors.

The syntax for the Match() function is:

Match([variable name to compare];"string to compare it to")

With the Match() function you have two wildcards available: * and ?

The asterisk (*) replaces one or more characters. The question mark (?) replaces only one character. We will use the asterisk in our variable.

For our variable we will use the following syntax:

=If(Match([Color];"Black*");"Black"; If( Match([Color];"Blue*");"Blue";If (Match([Color];"Brown*");"Brown" )))

Click the checkmark to validate your syntax. Once validated, click OK.

The variable is now listed on the Data tab of the Data Manager and can be used in the report:

Founded in 1994, Dataspace is a consulting and solutions firm focused on the flawless execution of Business Intelligence and Data Warehousing initiatives. Visit www.dataspace.com.