Useful ways to format Bex results in Webi

Useful ways to format Bex results in Webi

Via Content in SCN

Running webi reports off of Bex queries can present interesting results due to the nature of using a relational tool to consume OLAP results. Here are some useful formulas/tips to help present Bex results in a cleaner manner.

Remove Values using SUBSTR():

A SUBSTR() formula in Webi is used to strip out unnecessary details from a particular field. A classic example is when dealing with compounded infoobjects. For example when there are reports created for financial and accou nting information a report user is interested in seeing the G/L Account number. However G/L Account Number is compounded with the company code that is configured in the backend. The account number is shows up in the following format CODE/number.

Lets say the G/L Account Number is COMP/13550563 but the user is only interested in seeing 13550563. We can use the substr formula.

=Substr([FIELD];6;8)

Substitute [FIELD] with your object. This formula says we will begin our field at the 6th character by ignoring t he first 5 which is COMP/ and the 8 in the formula says G/L account numbers are always 8 characters long and we need to display all 8. It is recommended you look at the actual Infoobject data length to determine the length of the object.

Remove/Replace Values using REPLACE():

We could have removed the company code from the above example using a REPLACE() formula as well if we are sure that our company code will always be the same. If the G/L account number shows up as COMP/13550563 we can write the replace formula as follows:

=Replace([FIELD];" COMP/";"")

This formula now strips out the unwanted COMP/ value from our G/L account number. This formula is also useful to remove additional details from timestamps and any details from an address field. This can also be used to replace existing data with data that might make more semantic sense to the user. For example changing a meaningless 'X' flag value to either YES or NO.

Create Custom Number Formats:

Different clients have different needs on how they view numbers and dollars on their reports. Custom number formats can easily be created in Webi. When yo u go into format for a number you can define your 'custom' format. Here is a sample for a standard accounting format where thousands are separated by a comma, null values are treated as zeroes and negative values are treated in RED and contained in parenthesis. All numbers follow a 2 decimal notation.

POSITIVE = 0,.00
NEGATIVE = (0,.00)[RED]
EQUAL TO ZERO = 0.00
UNDEFINED = 0.00

The comma allows us to seprate the thousands indicators. To apply to integers remove the decimal.

Use system variables to provide additional meta-data

There are a variety of formulas that are available that call the system specific fields. For example you may want to display the last date the report was executed. You can achieve it using  LastExecutionDate(). CurrentUser() gives you the username of the person accessing the report currently. These become more powerful when you use it along with CurrentDate() and CurrentTime()

Calculate Days between two dates:

It takes a significant amount of work to calculate the number of days between two date ranges in Bex but Webi makes it very easy for us to acco mplish the same. Suppose we want to find the number of days between our Transaction Date and our Posting date simply create a new variable of type Detail and use the DaysBetween() formula to calculate the number of days between the range.

=DaysBetween([Activity_Date];[Posting_Date])

Counting Members:

Webi also makes it very easy to create different counters. Suppose we want to compare number of business partners and the number of contract accounts it is very easy to accomplish by using the Count() formula. Create a variable of type measure and use the formulas as such:

=Count([Business Partner])

You can count different dimensions for a good comparative analysis.

Nest Concatenation() to present free text:

Webi contains a Concatenation() formula to join two strings however it is limited to only joining two strings at a time. This formula becomes really useful if you can nest them inside each other. For example lets say we want to display a free text from CRM on the Webi report but the free text is split into 4 fields due to BW's limit of 60 characters per infoobject of type CHAR. The free text is split into FIELD1, FIELD2, FIELD3, and FIELD4. In order to proper nest the concatenations remember this quick formula. If you wish to join 'N' number of fields then you need to use a total of 'N-1' Concatenation() formulas. In this case beause we want to join 4 fields we will need to use the Concatenation() 3 times.

=Concatenation(Concatenation(FIELD1;FIELD2);(Concatenation("FIELD3";"FIELD4")))