Creating formulas in Web Intelligence (Webi) is one of those essential skills for a Webi report developer. SAP BusinessObjects 4.x provides functionality in Webi to create formulas using three basic techniques: Type it into the Formula Bar; Drag-and-Drop fields into the Formula Bar; and Using the Formula Editor. For this post, we’ll build a formula to calculate Extended Sold Amount. We will use the eFashion universe and the fields listed below in the Webi report. I’m starting at the point where the report is already built and displaying in Design mode.
So, in preparation, create a Webi report based on the eFashion universe and bring in the following objects:
- SKU number
- SKU desc
- Quantity sold
- Unit Price MSRP
- Sales Revenue
Our goal is to create a formula to find the Extended Sold Amount, which I’m defining as:
Quantity Sold multiplied by Unit Price MSRP.
Process – Here we go . . .
1. Start out by inserting a blank column in the table in the report you just made.
a. Right-click on the Unit Price MSRP column
b. Hover over or click Insert from the short-cut menu
c. Then select Insert column on right
2. A blank column will be inserted to the right of the column you just right-clicked.
3. Highlight that new blank column by clicking where the data will be, then click into the Formula Bar.
4. In the Formula Bar, add the following formula: =[Quantity sold]*[Unit Price MSRP]
This can be accomplished using more than one method:
a. Typing it into the Formula Bar
b. Dragging-and-dropping fields into the Formula Bar
c. Using the Formula Editor
Typing the Formula
a. When a formula is typed into the formula bar it must begin with an equal sign (=). Otherwise, your input will display as text. Remember, the formula is case sensitive and the field names must be exact and surrounded with square brackets.
b. Click the green check mark next to the Formula Bar to validate the formula.
a. Highlight the Quantity sold field, then drag-and-drop it onto the Formula Bar. Webi inserts the equal sign for you.
b. Next, type in an asterisk (to represent multiplication) after the square bracket of the field.
c. Highlight the Unit Price MSRP field, then drag-and-drop it after the asterisk in the Formula Bar.
d. Click the green check mark on the Formula Bar to validate the formula.
Using the Formula Editor
a. To get to the Formula Editor, first make sure you have highlighted where you want the formula to be (remember that’s the blank column you inserted), then click into the Formula Bar. (The below example already has a formula just to help guide you through the steps.)
b. Click the Formula Editor icon on the Formula Bar.
c. The Formula Editor screen displays. This screen contains areas for: the formula, the fields (Available Objects), Functions, Operators, and Description.
• When you highlight one of the elements in Available Objects, Functions or Operators, its description is displayed in the Description section.
• Elements can be added to the Formula area: typed in, dragged-and-dropped, or double-clicked.
d. When your formula is complete, click the green check mark to the right of the Formula to validate it.
e. Click ‘OK’ to close the Formula Editor.
So, that wasn’t hard was it? Which method do you use most often? SAP supplies a user guide titled Using Functions, Formulas and Calculations which you might find helpful if you’re new to Webi. This guide provides detailed information on the advanced calculation capabilities available in Webi and a syntax reference for the functions and operators.