Creating Formulas with Character Functions

In the eFashion universe, the year object returns the year as a four-digit value, such as 2025. If you want to display the year in a two-digit format, such as 25, use the Right function to show only the last two characters.

The Right function displays a fixed number of characters, starting from the right of the character string, and removes the others.

The Right function returns the rightmost characters of a string.

string Right(string;num_chars)

To show only the last two characters of the year, use the following syntax:

=Right([Year];2)

The example syntax can be broken down into the following parts:

• [Year] Name of the object that contains the data.
• 2 Number of characters to display from the right of the character string.

To include the year name and display the last two digits in the section header, use the following syntax:

=NameOf([Year])+" "+Right([Year];2)

The example syntax can be broken down into the following parts:

• NameOf([Year]) To display the name of the Year object.
• +" " To concatenate a space after the object name.
• + Right([Year]);2) To concatenate and display two characters starting from the right end of the character string.

The Store name object in the eFashion universe returns the name of each store in the Fashion company. Each store name includes the prefix "e-Fashion".

If you want to remove the "e-Fashion" prefix from each store name, you need to adjust the string. Removing the "e-Fashion" prefix can be difficult because store names have different lengths. The Right and Left functions do not work in this case.

You can remove the "e-Fashion" prefix by using the SubStr function.

The SubStr function extracts a specified character string from within a string.

string Substr(string;start;length)

To display the store name without the "e-Fashion" prefix, use the following syntax:

=SubStr([Store name];11;Length([Store name]))

The example syntax can be broken down into the following parts:

• [Store name] The string from which the substring is to be extracted.
• 11 The position of the first character in the input string to be extracted.
• Length({Store name]) The number of characters to be extracted.

The report displays the store names without the e-Fashion prefix.

In this example, you want to show the word "Quarter" instead of the letter "Q."

The Replace function replaces part of a string with new text.

string Replace(replace_in;replaced_string;replace_with)

Use the following syntax:

=Replace([Quarter];"Q";"Quarter ")

The example syntax can be broken down into the following parts:

• [Quarter] Describes the name of the object that retrieves the data.
• "Q" Describes the value stored in the database followed by 1 through 4 to indicate the first through fourth quarters of the year.
• "Quarter " Describes the character string that replaces "Q" in every instance.

The Quarterly Revenue figure shows the report that appears when you replace the Quarter column with the new variable.

You are working on a report that includes the Store and Address columns.

In the Address column, you want to show only the street names for each eFashion store. The Address object returns both the building number and the street name. The number of spaces before the street name starts is not consistent. To solve this, you need to create a substring that returns only the street name.

Each address has a space after the building number. Create a formula that finds the position of this space for each entry. Use this position as the starting point for the SubStr function.

The Space variable finds the first space in each address string. If you add a Space column to the report, it shows the number of characters before the street name for each address.

The Pos function returns the starting position of a text pattern in a string.

int Pos(test_string;pattern[;start][;end]])

Use the following syntax:

=Pos([Address] ; " ")

The example syntax can be broken down into the following parts:

• [Address] The string to be tested for the text pattern.
• " " The text pattern.

The number that the Space variable returns is the count of characters before the street name starts. You can now create a variable called Address that removes the building number from the address.

The SubStr function extracts a specified character string from within a string.

string Substr(string;start;length)

Use the following syntax:

=SubStr([Address];[Space]+1;Length([Address]))

The example syntax can be broken down into the following parts:

• [Address] The string from which the substring is to be extracted.
• [Space] The position of the first character in the input string to be extracted.
• Length([Address]) The number of characters to be extracted.

To finish the report, delete the Space column. Replace the Address column with the Address variable you created.

For more information about this topic, please read SAP BusinessObjects Web Intelligence User's Guide

• Use character string functions like Right, Left, and SubStr to extract specific parts of text in reports.
• Apply the Replace function to substitute parts of a string with new text for clearer report formatting.
• Use the Pos function to find character positions and create dynamic substrings based on variable text lengths.
• Understand when to use variables versus formulas for flexible and reusable calculations in your documents.