A global company’s product portfolio produces complex reports that often require simplification to emphasize certain pieces of information. Managers usually want to see concise reports of important products that explain key elements of their product lines.

One way to produce such a report is to show how the top N products of a given portfolio perform relative to the remaining products in terms of sales or profit. The standard report only shows the top N products’ information. With access to the subtotal key figures of the top N products and the subtotal of the remaining products in one report, managers can allocate brand investments, research, and development to the right products. Moreover, some products could be very important in one region but not in the other.

Users can select the region and period for which they would like to perform the top N products analysis. You then have a report that includes the top N products, their subtotal, and the other products’ subtotal.

You can use a product variable with a user exit type to create such a report. We’ll develop a report that shows the top 20 products in a given company as compared to its other products, which is information a standard report doesn’t provide. We’ll also show you a previously undocumented way to code the ABAP user exit so that it calls another query to populate the values at runtime. First, you need to create a report that lists the top 20 products and their key figures, which we’ll explain next. This process applies to SAP BW 3.x and later.

Top 20 Products with Key Figures Report

You have to create a BEx Query to show the top 20 products based on sales. Follow menu path Programs>SAP Front End>BEx Query Designer to define the query ZSR_MCS1_QW_ESS0011_GC_QTD (Figure 1). The first column shows the current quarter sales that you’ll use later in the query condition to define top N products. The query produces the top section of the final report, which we’ll show at the end of the article. In our example, &ZT_VR1& &ZT_FYP1& / (GC/QTD) is a predefined restricted key figure that shows the current quarter sales.

Figure 1

Create the query with product in the rows

Drag the characteristic Product into the Rows section, and then drag the key figures under the Key Figure section from the left into the Columns section. Next, drag Fiscal year/period dimension to the Free Characteristics area, as shown in Figure 1.

Management Entity can represent a region, subsidiary, division, or whatever the user defines. Likewise, Fiscal Period is a year (as used in my example), a quarter, or a period of any length. By double-clicking on Management Entity and Fiscal Period, you can restrict them with user input variables so the user can select the management entity and time period when running the query. This allows the end user to run the report that gets the top 20 products for any combination of management entity and time period. Click on the define condition icon in Query Designer (circled in Figure 1) to bring up the screen in Figure 2.

Figure 2

Create a condition on product to list top N products based on sales

In this screen, you can define a condition. Check the Product check box so that the system only evaluates the condition for products. Select the first key figure (&ZT_VR1& &ZT_FYP1& / (GC/QTD), in this example) from the drop-down list, and then select Top N as the operator. Enter 20 in the Values field. I’m using 20 in my example; this number simply represents N in a given scenario. You can also create a variable to let the user pick the value of N. By checking the Variables Entry check box in Figure 2, you can define a variable for top N instead of top 20. Now that you’ve created the top 20 products key figures report, we’ll show you how to create a report that shows subtotals.

Subtotal of Top 20 and the Remaining Products Report

The process to create this report involves four steps. First, you generate a query to show only the top N products. Then you create a user exit variable that calculates the top 20 products. From there, you populate the product variable in the user exit and use it to derive the subtotals for the top 20 and the remaining products.

Step 1. Create a query with the condition that you show only top N products. In the screen shown in Figure 3, create query ZSR_MCS1_QW_ESS0034. This query is similar to ZSR_MCS1_QW_ESS0011_GC_QTD (the query from the previous section) and it uses the same Management Entity and Fiscal Period variables. You can follow the steps in the previous section to define the condition.

Figure 3

ZSR_MCS1_QW_ESS0034 calculates top 20 products

Step 2. Create a user exit variable under Product to calculate the top 20 products. Open Query Designer and expand the characteristic Product. Right-click on Characteristics to bring up the context menu and select New Variable. In the screen that appears, define a user exit variable (Figure 4). In this screen, you specify the fields as your needs warrant. We filled the screen with the appropriate values and included the variable’s name (ZC_PRD28) and a description.

Figure 4

Create a user exit variable ZC_PRD28

Step 3. Call ZSR_MCS1_QW_ESS0034 in the BEx Variable ABAP user exit to populate the product variable. Refer to the BEx variable by user exit documentation at https://help.sap.com/saphelp_bw320/helpdata/en/f1/0a56f5e09411d2acb90000 e829fbfe/content.htm for further details about using user exits to populate product variables.

You need to use the ABAP user exit code that you can download via this link. The code has three major sections. The first section retrieves all variable values for management entity, year, and other values selected by users.

The second section calls the query, ZSR_MCS1_QW_ESS0034, defined in step 2 to retrieve the result set internal table. When the query runs, the system first stores the result in the result set internal table. The top 20 product codes are also stored in the result set table. By looping through the result set table, all the product code values pass to the function call in the user exit module.

The third section retrieves the list of product codes based on the result set table. The system then populates variable ZC_PRD28 based on the result set table.

Step 4. Use the user exit product variable ZC_PRD28 in ZSR_MCS1_QW_ ESS0026_GC_QTD to derive the sub­totals for the Top 20 and the remaining products (Figure 5). The query contains the same column structures as ZSR_MCS1_QW_ESS0011_GC_QTD. It uses another structure in the rows to show the subtotal of the top 20, the subtotal of the remaining products, and the grand total.

Figure 5

Query for the subtotal of top 20 and the subtotal of the remaining products

Select Top 20 Products under the row Structure. This brings up the screen shown in Figure 6. Then, drag Product from the left side to the right. Double-click on Product to specify the variable ZC_PRD28, which you use to restrict the Top 20 Products.

Figure 6

Top 20 Products restricted by product variable ZC_PRD28

Next, double-click on All Products in Figure 5 to bring up the Edit Selection screen shown in Figure 7. You still need to drag Product from the left side to the right. However, notice the difference between Figures 6 and 7: the latter does not show a restriction under Product. In this situation, All Products shows the grand total for all the products under the Management Entity and the time period that the user selects.

Figure 7

All Products has no restriction on product

Then create a formula in the Rows section called All Others (Figure 8). All Others is the difference between the selection of All Products and the selection of Top 20 Products. It is the last step of the query creation and gives the user an idea of the total sales of the remaining products without showing the product details.

Figure 8

All Others equals All Products minus Top 20 Products

After creating this report, the final step is to publish ZSR_MCS1_QW_ESS0011_GC_QTD and ZSR_MCS1_QW_ESS0034 to the same Web template. Open BEx Web Application Designer and drag two table items from the left to the right layout screen. Then assign data provider ZSR_MCS1_QW_ESS0011_GC_QTD and ZSR_MCS1_QW_ESS0034 to the first and the second table, respectively. When you execute the report, the variable screen shown in Figure 9 appears. Enter the required data and click on the Execute button to view the report. In the sample report in Figure 10, PRODUCT 1, PRODUCT 2, and PRODUCT 20 represent the individual product lines you would see — a real report would have 20 lines above the three summary lines.

Figure 9

The selection screen of the top N analysis report

Figure 10

Sample report with totals for Top 20 Products, All Others, and All Products

Yu (James) Hong

Yu (James) Hong is an information management lead at a major manufacturing company. He currently is in a BI architect role in the critical corporate finance system. Prior to his current position, he worked as a senior consultant for BearingPoint and a program analyst for Colgate Palmolive, Inc. His expertise covers all aspects of the SAP NetWeaver BI implementation.

You may contact the author at jamesyhong@yahoo.com.

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

Balaji Bitra

Balaji Bitra is the BW lead at a major manufacturing company. Prior to that, Balaji was a senior consultant with BearingPoint, Inc. He is experienced in implementing large SAP BW projects.

You may contact the author at bitra_b@yahoo.com.

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