Many SAP NetWeaver BW administrators lock or severely restrict the capabilities of changing queries by end users. This is to prevent the end user from altering the query in some way that would adversely affect the result set or overall performance. Often, end users would like the ability to alter or add their own key figures to the existing query. Without the ability to modify the query, this is not possible.

To address this need, SAP added a new feature called ad hoc formulas to the Web Analyzer. I will discuss how to enable this feature to create ad hoc formulas and embed them into a Web Analyzer query. To use ad hoc formulas, the system must be upgraded to SAP enhancement package 1 in SAP NetWeaver 7.0 (also known as SAP NetWeaver 7.01). This SAP enhancement package makes the new ad hoc formula function available in the Web template for the end user to use. Before upgrading to SAP enhancement package 1, you need to take care of some prerequisites, which are described in SAP Note 1248905.

Enable the Ad Hoc Formula Functionality

To start using ad hoc formulas, this feature must first be turned on in the active Web template — by default, this feature is turned off in the standard Web template. Thus, even if you have upgraded to SAP NetWeaver 7.01, users may not see the option to create ad hoc formulas when they right-click a query in the Web Analyzer.

To turn on the feature, you must first access the active Web template. The active (or default) Web template is the Web template that is used whenever queries are run in the Web Analyzer. To edit and turn on this feature, enter Web Application Designer (Web AD) and click the Edit button. Choose the Web template that is used in your Web analysis queries. Bring up the Web template into edit mode.

While in the edit mode for your Web template, choose the CONTEXT_MENU_1 Web item. This Web item represents the options and the menu that appear when you right- click in a query when running Web Analyzer (Figure 1). In this menu, the feature is available to the end user. The CONTEXT_MENU_1 Web item includes an option to turn on the ad hoc formula functionality. You can find this in the lower-left corner in the Properties area. The option is labeled Local Formulas. You can turn on this option by changing it to the on status. Once this option is turned on from the context menu, when the user right-clicks on the report in Web Analyzer, the end user sees a new option called Formulas. End users can then use the option to add their own ad hoc formulas to the query they are running.

Figure 1

Options and menu in Web Analyzer

Using Ad Hoc Formulas

Now that the option is turned on in the active Web template, query runtime users are able to use the ad hoc formula builder to create their own formulas. These formulas are shown and work with the other key figures that are displayed in the report. In fact, once an ad hoc formula key figure is created, it acts like all the other key figures in the report. You can sort, hide, and move them just like the other key figures.

To create an ad hoc formula while running a query in Web Analyzer, right-click the query key figures and click the Calculations and Translations option. Click Formulas and choose New Formula. The system brings up a screen that end users can use to create the ad hoc formula (Figure 2). All key figure values must be chosen from the menu options shown on the screen. If you try to enter a formula or paste in a formula from another area or query, the system gives an error message and does not allow you to save the formula.

Figure 2

Screen to create an ad hoc formula

In this example, I am creating a new formula called Percentage Filled. This is the percentage of the order quantity associated with the original order quantity. I set up the formula by choosing the key figure value order quantity and using the %_A (percentage share) and the key figure original order quantity. I added a description in the Description field. This is what the end user sees as the description of the key figure when running the query.

The ad hoc Formula Builder allows you to use any key figure that exists in the query as an operand. This includes restricted and calculated key figure values. Ad hoc formulas can also have a scaling factor or a set number of decimal places. To set these, click the Display tab at the top of the Formula Builder screen (Figure 3).

Figure 3

Click the Display tab

Here is a sample of the most popular functions that you can use in an ad hoc formula:

• Percentage functions: Percentage deviation, percentage share
• Data functions: No dimension, date, time, no error, no division by zero
• Trigonometric functions: Sine, cosine
• Mathematical functions: Power, logarithm, root, remainder, absolute value, maximum, minimum
• Boolean functions: AND, OR, XOR, NOT, LEAF

The system allows some Boolean operators in the ad hoc formulas. The functions AND, OR, XOR, and NOT are available for use. However, to perform useful decision making using the Boolean logic, the operands such as =, not =, <, and > need to be included in the functionality. These functions are not available in the ad hoc formulas. This severely limits the use of the ad hoc formulas and makes the Boolean operations very limited.

Because of the limitation of the Boolean functionality, you should only consider ad hoc formulas for performing mathematical functions on existing key figure values. They do have the advantage of acting like other key figures and can be saved as a favorite so the saved ad hoc formula can be used over and over again (Figure 4). The Percentage Filled key figure is shown in the query. This acts and looks like the other key figures in the query and can be sorted, totaled, scaled, or hidden just like any other key figure.

Figure 4

Save favorites and use them again

You can change or delete an ad hoc formula once you create it. To do so, simply go back into the Calculations and Translations option. Choose Formulas and your key figure. The option comes up to Edit or Remove (Figure 5).

Figure 5

Editor and Remove options

You can also use one ad hoc formula in another ad hoc formula. For example, if you want to add two ad hoc formulas, the system allows this functionality. This can be useful if more complex calculations are needed. The calculations can be performed in steps and then combined together into one key figure value.

Gary Nolan

Gary Nolan is an SAP NetWeaver BW-certified consultant and author of Efficient SAP NetWeaver BW Implementation and Upgrade Guide and Efficient SAP NetWeaver BI Implementation and Project Management. Gary specializes in gathering and evaluating requirements, configuring SAP NetWeaver BW, and providing project management, performance management, and data architecture and data modeling expertise. A former platinum consultant with SAP America, Inc., Gary has more than 20 years of SAP experience, working with SAP NetWeaver BW since version 1.2B.

You may contact the author at melvan.consulting@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.