BW reports are great for situations when things are in flux, but what happens when conditions are static? You can deliver sales figures, for example, for products that are selling but it's more difficult to report on products that are not moving. Because you can use a MultiProvider that employs InfoObjects as InfoProviders, BW 3.x provides the technology. Here's what you need to do to set up the objects in the Administrator Workbench and create the queries in the BEx Query Designer to generate these types of reports.
Key Concept
When activated as an InfoProvider, InfoObjects have default key figure Number of Records, which has a one value for each record in the master data table. Designing a query that includes the Number of Records value allows you to more accurately report on dynamic business situations.
During my first BW implementation in 1999, one of the critical questions that cropped up was: “Gee that’s a great system, but can it tell us what we’re not selling?” The company was interested in sales figures, but it also wanted to identify what products were not moving as well as those customers that weren’t buying. It could then determine why and remedy the situation. For various reasons, both system and budgetary, the team I was on five years ago was unable to deliver this type of reporting with BW 1.2B. The question, however, stuck with me.
Recently, the business at my current employer made me revisit the issue. This time, with a little ingenuity and the feature set offered in BW 3.x, I discovered that it’s a simple process to track what’s selling and what’s not. By using an InfoObject as an InfoProvider, I found a key figure value that supports reporting on items that do not have values posted against them.
The approach is based on a feature in BW 3.x that allows the use of InfoObjects as InfoProviders, and is accomplished in four basic steps:
- Identify the InfoObjects to use for non-posted reporting (product, customer, etc.)
- Set the appropriate objects as InfoProviders
- Create a MultiProvider with these InfoObjects and the relevant InfoCube
- Create a query with conditions to display only those items with zero activity
Let me walk you through these steps and show you some things to watch out for. First, I’ll show what objects you’ll need to create and maintain in the Administrator Workbench. Then, I’ll explain how to create the query you’ll need in the BEx Query Designer. With very little effort you’ll be able to give your users reports on both what’s selling and what’s not.
How It Works
Before I get into the actual maintenance, let’s look at how the system can deliver this type of information. When activated as an InfoProvider, InfoObjects have a default key figure called Number of Records with a value of one for each record in the master data table. This default key figure can be used for reporting purposes.
Because InfoObjects in BW 3.x can be set up as InfoProviders, an unrestricted query can be designed to use the Number of Records values and display every record in the master data table. Similar queries can also be created to report on the master data with filters, variables, drill-downs, etc., so that they are performed on an attribute such as a list of all products within certain product groups.
Setting InfoObjects as InfoProviders by itself won’t allow users to report on items that do not have values posted against them, however. You also must create a MultiProvider that includes an InfoCube that uses the same InfoObject defined as the InfoProvider to record facts. With this arrangement, you can design a query that not only sees when something has posted in the fact table, but also when it has not.
The MultiProvider allows you define a query to display all the products or customers, not just those with values in the InfoCube. This is accomplished by including a column for the Number of Records values along with another column that has the sales values, or posted amount, or whatever key figure is required. You then set a condition for this column when designing your query that only displays zero values. Once this condition is activated, presto, the zero value records are displayed.
What to Do
InfoObjects for the non-posted values you want to display must first be activated as InfoProviders so they will include the Number of Records default key figure. Maintain the InfoObject either from the InfoObjects tab on the Administrator Workbench or go directly to the maintenance screen via transaction code RSD1. Note, for this example I have chosen to use object 0PROFIT_CTR.
Next, go to the Master data/texts tab for the InfoObject (Figure 1). The check box near the bottom of the screen with the description Character. is data target/InfoProvider is grayed out. While the flag cannot be selected, it can be updated depending on the entry in the InfoArea field adjacent to the text. Defining an InfoArea as an InfoProvider in the Administrator Workbench sets the flag when the refresh icon is clicked on (Figure 2).
Figure 1
The check box is grayed out but can be updated by the entry in the InfoArea field
Figure 2
The check box is grayed out but can be updated by the entry in the InfoArea field
After the InfoObject is maintained as an InfoProvider, create the MultiProvider with the relevant InfoCube and InfoObject. Right- click on the InfoArea where the MultiProvider is being grouped and select Create Multiprovider. Select the required InfoCube in the InfoCubes tab, then click on the InfoObjects tab and set the flags for the object (or objects) to be checked for non-posting. For this example, InfoObject 0PROFIT_CTR, and an InfoCube containing profit center transactions have been selected.
Clicking on the check mark at the bottom of the screen displays the InfoCube Creation screen. Proceed as usual creating your InfoCube by selecting the required objects. Keep in mind, however, because the purpose of this new InfoCube is to identify items that haven’t been posted, the number of objects should be kept to a minimum. For this example, it is restricted to only InfoObjects 0PROFIT_CTR and 0ACCOUNT, along with their compounding objects (Figure 3).
Figure 3
Keep the number of objects to a minimum when creating the MultiProvider
Click on the Dimensions... button to create one dimension for each object and its compound key. Assign the objects to the appropriate dimensions in the Assign tab, and click on the green check mark to return to the main InfoCube definition screen.
Use the Identification button to access the Identification of Characteristics Involved screen (Figure 4). Set flags in the “Equal to” column (Equ...) for the objects available in the MultiProvider. Most will be selected.
Figure 4
Set flags in column Equ... for the objects available in the MultiProvider
In this example, note that 0PART_PRCTR has not been selected. Because the data is being examined to determine which centers have not been posted to directly, partner profit centers have not been included. 0PART_PRCTR may be relevant for other analyses, but I excluded them from this example for purposes of simplicity.
After making the character selections, click on the check mark icon to get back to the main definition screen, then click on the same icon in the next screen, and activate the InfoCube. Refreshing the InfoProvider screen displays the new MultiProvider in the Administrator Workbench (Figure 5).
Figure 5
The new MultiProvider is displayed the in the Administrator Workbench after the InfoProvider is refreshed
Note
Because this development involves existing InfoCubes and data objects, and only a virtual InfoCube — the MultiProvider — has been created, there is no need to load any data before creating a query.
Define the Query
Once the InfoCube is activated, you create the query in the BEx Query Designer that returns data only for the non-posted profit centers. Include only those objects such as filters, free characteristics, and rows that exist in both InfoProviders. Note in Figure 6 that only Controlling Area, which includes the InfoCube and a compound key to 0PROFIT_CTR, and 0PROFIT_CTR have been included.
Figure 6
Include only the objects that exist in both InfoProviders in your query
Other objects that may be required such as account numbers can be included as restrictions within the key figures so that they only restrict the relevant InfoProvider. For example, 0ACCOUNT can be included within the first three columns to restrict the key figures because without it, the accounting postings could all balance to zero. To avoid this, the key figures have been split into credits, debits, and balances.
The Key Figure column for the master data provider is relatively simple. It includes just the key figure Number of Records (1ROWCOUNT) that provides a value of one for every master data record stored on the BW system. As a result, when a query is executed, a record will be returned for every master data item even if there are no transactions.
The Non-Posted column uses the formula shown in Figure 7. It records a value of one for the sum of absolute values that is not equal to zero. This column is used by a condition to display only records where the value of column Non Posted Center is not equal to one.
Figure 7
The formula yields a value of 1 for the sum of absolute values not equal to 0
Create the required query condition by clicking on the conditions icon 
and selecting New. Enter an appropriate description and set the single characteristics or combinations of characteristics button. Check the Profit Center box and click on the New button on the right.
Note
Absolute values (ABS) have been selected in the Mathematical Functions folder to accommodate instances where the sum of credit and debit values may equal zero for certain centers.
This activates the pull-down boxes grayed out in Figure 8 where you select the key figure on which the condition operates. In this example, the Non Posted Center formula is selected for the Structure column along with Not Equal to for the Operator column and 1 is entered for the Values column. Clicking on the Transfer button moves the selection up as shown below. Click on OK and save the query, which makes the condition effective when the query is executed.
Figure 8
Clicking on the Transfer button moves the column selections up
The end result is shown. If required in Figure 9, the columns can be hidden so that the report just returns a list of centers.
Figure 9
A report with items that do not have items post against them. If required the columns can be hidden so that the report just returns a list of centers.
I recommend that when you first deploy this functionality, you keep it simple. It can then be applied to InfoCubes and InfoObjects as required by the business to return more information on products or clients, for example, which are not generating sales.
Robert Oliver
Robert Oliver is an SAP BI developer working for an investment bank in London. He has been working with SAP R/3 since 1994 and SAP NetWeaver BW since 1999. Since 2005 he has been working with the planning and consolidation systems provided by SAP. Prior to his current role he was an end user and a consultant.
If you have comments about this article or BI Expert, or would like to submit an article idea, contact the BI Expert editor.
You may contact the author at Robert_Oliver@Hotmail.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
Premium
