Meet the challenge of creating SAP NetWeaver BW monthly inventory data targets, including reports. Learn how to create a customized report that quickly displays opening balance, receipts, issues, end balance, and valuated stock for the current month and previous month. Sample code is included.
Key Concept
Receipts and issues are important points to be familiar with when monitoring inventory levels. Receipts refer to all material coming into stock, and issues refer to all materials going out of stock.
Every company needs to know about its inventory and stock. Your organization might require a monthly inventory report that shows the starting balance for the given month, end balance, receipts, issues, and valuated amount. This report may need to correctly sum and filter by material group and material.
One place to have this kind of information is in SAP NetWeaver BW. SAP has delivered objects for material stocks and movements, but they are cumbersome to follow because you have to maintain several tables, run three different InfoPackages, and complete many other necessary steps. The SAP-delivered objects also require users to be locked out of SAP ERP Central Component (SAP ECC) to initialize setup tables. I have seen several articles about how to design monthly inventory snapshots, all of which are cumbersome.
Also, with SAP-delivered objects, when users run reports, processing and displaying the results take a long time because an InfoCube contains millions of records and the query has many calculations. However, I developed a method in which it takes about 15 seconds to display data for two months.
I explain step-by-step instructions of how to create a custom database view and a DataSource in SAP ECC. With my method, no user needs to be locked out of SAP ECC, and there is no marker update involved. A marker update is like check point: It gives a snapshot of the stock on a particular date when it is updated.
Note
This article assumes you have basic knowledge of SAP NetWeaver BW. You also need to know how to create a Data Transfer Process (DTP), transformations, InfoPackages, DataStore objects (DSO), InfoCubes, MultiProviders, and SAP ECC database views.
My goal is to achieve the data flow shown in Figure 1.
Figure 1
A complete data flow
Figure 1 shows one DataSource, one InfoPackage, two DSOs, three InfoCubes, and one MultiProvider.
There are two parts in this design:
- Getting end stock and valuation
- Getting receipts and issues (RI)
I cover a 15-step process in this article:
- Step 1. Create a table view in SAP ECC
- Step 2. Create a generic DataSource
- Step 3. Replicate the DataSource in BI
- Step 4. Create DSO ZMMI_D01
- Step 5. Create a ZMMI_D01 transformation
- Step 6. Create a ZMMI_D01 DTP
- Step 7. Create an InfoPackage
- Step 8. Create a Monthly Inventory End Stock InfoCube
- Step 9. Create the ZMMI_C03 transformation
- Step 10. Create a ZMMI_C03 DTP
- Step 11. Create Inventory End Stock History – ZMMI_C04
- Step 12. Create RI DSO - ZMMI_D03
- Step 13. Create an RI InfoCube
- Step 14. Create a ZMMI_C04 transformation
- Step 15. Create a monthly inventory MultiProvider
Let’s get started.
Step 1. Create a Table View in SAP ECC
A standard SAP DataSource (2LIS_03_*) requires you to initiate setup tables. To do this, you must lock users out of the SAP system so that no material movements happen during initialization and setup. When creating a customized database view, you use the base tables and, therefore, there is no setup table to initialize.
First, you create a database view in SAP ECC. In this view, you have all the necessary fields for your report. This view gets data from three tables:
- MKPF: Material document header table
- MSEG: Material document segment table
- MBEW: Material valuation
Go to transaction code SE11 and create a database view (e.g., ZV_INVENTORY). Under the Table/Join Conditions tab, use all the join conditions to get the correct unduplicated records (Figure 2).
Figure 2
ZV_INVENTORY database view and its join conditions
Click the View Flds tab and then click the Table fields button, which brings up a Base Tables pop-up. Use the Choose button to select one table at a time. Once finished, close out the pop-up.
Now, mark the boxes to the left to select the fields you need to populate and show in your report. When finished, click the copy button (Figure 3). Continue in a similar manner with the other two tables.
Figure 3
Select the fields
When finished with the three tables, click the activate icon 
near the top of the screen.
Go to the View Fld tab, which brings you to the screen in Figure 4 showing the selected fields.
Figure 4
The selected ZV_INVENTORY table fields
At the top of the screen, click the check icon 
, and then click the activate icon to activate the view (Figure 5).
Figure 5
Check and activate the view
Step 2. Create a Generic DataSource
To make this view available to SAP NetWeaver BW, you need to create a generic DataSource in SAP ECC. To do so, use transaction SBIW and enter a name for the DataSource (e.g., ZTR_INVENTORY) for transactional data. Fill in the required fields highlighted in Figure 6, and choose the ZV_INVENTORY database view you created earlier for the View/Table field by clicking the icon to the right of the field. Now double-click ZV_INVENTORY. Save this DataSource to Application Components MM (Material Management).
Figure 6
Transactional data DataSource ZTR_INVENTORY
In the next screen, click Generic Delta and enter BUDAT in the Field Nm field (Figure 7). This step enables you to delta extract. You don’t want to fully extract every time.
Figure 7
The generic delta setup for ZTR_INVENTORY
Click the save icon and then save the DataSource. In the subsequent Header Data screen, under the Selection column (Figure 8), check off the fields you want to use for filters in your InfoPackage. This is useful for testing purposes so that you don’t have to extract all materials movements for testing. Keep in mind that you do not need to use any filters in production when extracting data for live reporting.
Figure 8
Header data for DataSource ZTR_INVENTORY
Tip!
Make sure under the Hide field column that there are no boxes checked; otherwise, any fields that have checks will not be visible in SAP NetWeaver BW.
You now are done with all the set-up steps you need to take in SAP ECC. You do the rest of implementation in SAP NetWeaver BW.
Step 3. Replicate the DataSource in SAP NetWeaver BW
In order for the DataSource you created to be visible to SAP NetWeaver BW, you need to replicate it there.
Note
As a general rule, any time you create or change a DataSource in SAP ECC, you must to replicate it in SAP NetWeaver BW; otherwise, the changes will not be visible to SAP NetWeaver BW.
You saved the DataSource in SAP ECC in the MM component in the previous step, so in SAP NetWeaver BW, you replicate the MM component. Go to transaction RSA1 and click DataSources in the left panel. Find the MM component in the right panel, right-click it, and click Replicate Metadata (Figure 9).
Figure 9
Replicate the DataSource
As soon as the replication is done, you see your ZTR_INVENTORY DataSource.
You need to activate this DataSource, so right-click it and select Change (Figure 10).
Figure 10
Change the DataSource
Next, click the activate icon (Figure 11).
Figure 11
Activate the DataSource
For my custom implementation, you need to create the following InfoAreas (Figure 12):
- Acquisition Layer InfoArea, which holds the DSOs
- Consolidation Layer InfoArea, which holds the InfoCubes
- Reporting Layer InfoArea, which holds the MultiProviders
Tip!
Create InfoAreas for your developments and group them together. This way, you segregate SAP-delivered objects and customized objects.
Figure 12
Inventory InfoAreas
Step 4. Create DSO ZMMI_D01
You now need to create a standard DSO under the Acquisition Layer InfoArea. The DSO contains all materials and movements. You also need to capture all records per material document, material, calendar month, plant, and debit/credit, so these InfoObjects are put under the Key fields section of the DSO (Figure 13). The rest of the fields you need are put under Data Fields section (Figure 14).
Remember, DSOs have key fields and data fields. If two records with the same key fields arrive to the DSO, the second record overwrites the first record. If the two records are different, a new record is appended.
Figure 13
Add InfoObjects under the Key fields section
Figure 14
Add InfoObjects under the Data Fields section
I provide a table of these fields so that you can copy and paste into the Key fields and Data fields sections (Table 1). Click here to download this table.
Table 1
Fields for Inventory DSO – ZMMI_D01
Save, check, and activate the DSO.
Step 5. Create a ZMMI_D01 Transformation
The next step is to create a transformation for the DSO you created, with the source being the ZTR_INVENTORY DataSource. I do not describe step-by-step instructions about how to create the transformation, but Figure 15 offers a guideline on the mapping. Note that there is a routine for 0QUANTITY.
Figure 15
The transformation for Inventory DSO ZMMI_D01
Note
SAP ECC does not save negative numbers for credit or debit; all values are positive. A best practice is not to have too many formulas in a report. You want the transformation to do all calculations before data is loaded so that your queries don’t have too many formulas. Too many formulas and calculations in a query slow down the execution and increase the runtime.
If a record is a debit, you want the value to be negative. To accomplish this, create a routine for 0QUANTITY. You need to have two fields linked to 0QUANTITY (Figure 16).
Figure 16
A routine for 0QUANTITY
Figure 17 shows the code for the 0QUANTITY routine. Click here to download a copy of this routine code. Insert this code in the QUANTITY InfoObject in the transformation you created earlier.
Figure 17
The code for the 0QUANTITY routine
Step 6. Create a ZMMI_D01 DTP
The next step is to create a DTP with the source as ZTR_INVENTORY DataSource and the destination as DSO ZMMI_D01 (Figure 18). There is no filter required unless you want to put one for testing purposes.
Figure 18
A DTP for Inventory DSO – ZMMI_D01
Click the Semantic Groups button to specify how you want to build data packages that are read from the source (DataSource or InfoProvider). Define key fields by selecting the indicators beside them (Figure 19). Data records that have the same key are combined in a single data package. For more on this topic, click here to read this SAP Help document.
Figure 19
Define the semantic groups
Step 7. Create an InfoPackage
Now you need to create an InfoPackage. The InfoPackage should not restrict any data. The first time you run the InfoPackage, choose Initialize Delta Process as the update mode and, thereafter, choose the Delta Update mode (Figure 20). Because you are using a database view, you don’t need to lock any users out of SAP ECC during this process.
Figure
InfoPackage for Inventory DSO – ZMMID_D01
Load the data to the DSO and activate it by right-clicking the DSO and then clicking Activate Data (Figure 21).
Figure 21
Activate the data in the DSO
In the subsequent screen, click the Start button (Figure 22).
Figure 22
Start the data activation
Step 8. Create a Monthly Inventory End Stock InfoCube
Next, create an InfoCube on top of the DSO. This InfoCube contains the monthly inventory end stock by material by month and year for each plant. The InfoCube holds the end stock for the current month only.
To achieve this, create a basic InfoCube under the Consolidation Layer InfoArea. The fields you need in this InfoCube are Material, Plant, Storage location, and Calendar year/month (Figure 23).
Figure 23
Monthly Inventory End Stock InfoCube ZMMI_C03
Step 9. Create the ZMMI_C03 Transformation
In this step, you create a transformation between Inventory DSO ZMMI_D01 and End Stock InfoCube ZMMI_C03 (Figure 24).
Figure 24
Inventory DSO to end stock transformation for ZMMI_C03
There are two routines and one formula in this transformation:
- Routine for ZSALK3 (Value of total Valuated Stock)
- Routine for 0TOT_US_VAL (Total Value consumed)
- Formula to generate the calendar month and year
Let’s look at these components in more detail.
Routine for ZSALK3
The routine for ZSALK3 has the following fields linked to it:
- Debit/credit indicator (0DCINDIC)
- Price unit (0PRICE_UNIT)
- Standard price (ZSTPRS)
Figure 25 shows that the target field is ZSALK3 (Value of Total Valuated Stock). The fields linked to ZSALK3 appear in the Source Fields of Rule section. By default, Currency Key is added as a field here because ZSALK3 is a currency field.
Figure 25
Value of Total Valuated Stock for ZSALK3
In Figure 26, I want to get the price for a unit of one. Some materials sell in units of 100 or 1,000. If the field for Debit/credit indicator is S, ZSALK3 extracts the value of one unit.
Click here to download a copy of the ZSALK3 routine code. You put this code in the ZSALK3 InfoObject in the transformation.
Figure 26
Code for the routine for ZSALK3
Routine for 0TOT_US_VAL
The routine for 0TOT_US_VAL has the following fields linked to it (Figure 27):
- Price Unit
- Quantity
- Standard Price
Figure 27
Total value consumed for 0TOT_US_VAL
Use the code in Figure 28 to get the total stock. Total stock = Standard price / Print unit * Quantity.
Click here to download a copy of the 0TOT_US_VAL routine code. You put this in the 0TOT_US_VAL InfoObject in the transformation.
Figure 28
Code for the routine 0TOT_US_VAL
Formula to Generate the Calendar Month and Year
The formula shown in Figure 29 generates the calendar month and year from the system’s current date. Double-click 0CALMONTH, and for Rule Type, choose Formula from the drop down menu.
Figure 29
Set the calendar month and year
In the next screen, take the following steps using the 0CALMONTH formula (Figure 30):
- Select Date Functions from the drop-down list on the right
- Double-click DATE_MONTH
- Double-click SYST_DATUM
- Verify that the formula showing is DATE_MONTH(Current Date)
Figure 30
The 0CALMONTH formula
Step 10. Create a ZMMI_C03 DTP
For this DTP, you need to have semantic groups and a filter. Click the Filter button. The filter eliminates any stock that is not part of a storage location (Figure 31). Therefore, you filter out any material so that the storage location is blank.
Figure 31
The filter for End Stock InfoCube DTP ZMMI_C03
To get the correct summation of all key figures in the InfoCube, you need to group the same materials and plants in the same package. Click the Semantic Groups button, and then check off the fields for Material and Plant (Figure 32).
Figure 32
Semantic groups for End Stock InfoCube DTP ZMMI_C03
Activate this DTP and load data to the InfoCube.
Note
The number of records loaded to this InfoCube is a fraction of the number of records in the DSO. This is important: As I mentioned earlier, the InfoCube holds one record per material for each plant per month, summing key figures.
Step 11. Create Inventory End Stock History – ZMMI_C04
Next, create an InfoCube (ZMMI_C04) on top of End Stock InfoCube ZMMI_C03. This new InfoCube holds end stock for previous months that has been loaded on a monthly basis from the end stock InfoCube. Therefore, ZMMI_C04 is structured exactly the same as ZMMI_C03. The transformation is a direct assignment between the two InfoCubes with no formula or routines.
You also need to create a DTP with no semantic groups or filters because in ZMMI_C03, the records are sorted (packaged) properly and filtered. The data flow for InfoCube ZMMI_C04 is shown in Figure 33.
Figure 33
Inventory end stock data flow
You now have all the information you need for the first part of my solution (e.g., all material movements, end stock quantity, and valuation).
Step 12. Create an RI Data Flow
Now let’s work on getting RI. Figure 34 shows the data flow for the process.
Figure 34
RI data flow for ZMMI_D03
DSO ZMMI_D03 extracts the data for RI from Inventory Movements DSO ZMMI_D01. For now, let’s use the same structure as ZMMI_D01.
Figure 35 shows the data modeling (structure) of DSO ZMMI_D03.
Figure 35
RI DSO ZMMI_D03
Create a transformation for Inventory RI ZMMI_D03 with the source as Inventory DSO ZMMI_D01 (Figure 36).
Figure 36
Transformation for Inventory RI ZMMI_D03
Create a DTP for this DSO with no filters or semantic groups.
Step 13. Create an RI InfoCube
Create an InfoCube for ZMMI_C04 (Figure 37), which is structured the same way as End Stock Cube ZMMI_C03. This InfoCube holds the total RI by calendar month.
Figure 37
Inventory RI InfoCube ZMMI_C04
Step 14. Create a ZMMI_C04 Transformation
Next, create a transformation for InfoCube ZMMI_C04 with the source as ZMMI_C03 (Figure 38). There are two routines in this transformation.
Figure 38
Inventory RI Transformation ZMMI_C04
Segregate the data according to debit or credit. For debit records, the quantity goes to Field 0RECVALSTCK. For credit records, the quantity goes to Field 0ISSVALSTCK.
Now create a routine on Field 0ISSVALTCK; credit/debit and quantity are linked to this InfoObject (Figure 39).
Figure 39
InfoObject 0ISSVALSTCK for quantity issued
The code for this routine is shown in Figure 40. Click here to download a copy of the 0ISSVALSTCK routine code. Put this code in the 0ISSVALSTCK InfoObject routine (Figure 41).
Figure 40
Routine code for 0ISSVALSTCK quantity issued
Figure 41
Insert the routine code as shown in the 0ISSVALSTCK InfoObject routine
Next, create a routine on Field 0RECVALSTCK; credit/debit and quantity are linked to this InfoObject (Figure 42).
Figure 42
InfoObject 0RECVALSTCK for quantity received
The code for this routine is shown in Figure 43. Click here to download a copy of the 0RECVALSTCK routine code. Put this code in the 0RECVALSTCK InfoObject routine (Figure 44).
Figure 43
Routine code for 0RECVALSTCK quantity issued
Figure 44
Insert the routine code as shown in the 0RECVALSTCK InfoObject routine
Now create a DTP with the semantic groups shown in Figure 45 and filter (Figure 46). You need to group all records into the same package by Material, Debit/credit indicator, Plant, and Calendar year/month.
Again, you want only those records that have a storage location, not those with a blank storage location.
Figure 45
Semantic groups for ZMMI_C04
Figure 46
Filters for ZMMI_C04
Step 15. Create a Monthly Inventory MultiProvider
So far, you have built an InfoCube for month-end stock and another InfoCube for RI. To link these InfoCubes and have them appear on one record in a query, you need to create a MultiProvider on top of them.
The data flow for MultiProvider ZMMI_M03 is shown in Figure 47.
Figure 47
Data flow for monthly inventory MultiProvider ZMMI_M03
Under your reporting layer, create MultiProvider ZMMI_M03 (Figure 48). This MultiProvider contains InfoCubes ZMMI_C03 and ZMMI_C04. Because the structures of these two InfoCubes are identical, your job is easy. From one InfoCube (it doesn’t matter which one), drag the dimensions into the MultiProvider, including the key figures. You end up with a MultiProvider similar to that shown in Figure 48.
Figure 48
Monthly inventory MultiProvider ZMMI_M03
The next step is to assign the MultiProvider fields to their corresponding InfoCube. Assign all characteristics to both InfoCubes.
Key figures need to be assigned as follows:
- 0CONF_QTY to Inventory End Stock History ZMMI_C04
- 0ISSVALSTCK to Inventory RI ZMMI_C03
- 0QUANTITY to Inventory End Stock History ZMMI_C04
- 0RECVALSTCK to Inventory RI ZMMI_C03
- 0TOT_US_VAL to Inventory End Stock History ZMMI_C04
- ZSALK3 to Inventory End Stock History ZMMI_C04
This concludes the creation of DSOs, InfoCubes, and the implementation and design of the MultiProvider.
Figure 49 shows the complete process chain for our inventory DSOs and InfoCubes.
Figure 49
The process chain for inventory DSOs and InfoCubes
Display a Sample Live Report
Let’s see the solution in action. The user enters a calendar month and year (Figure 50), and the report displays beginning balance (formula in the query), receipts, issues, end balance, and valuation (directly for the InfoCubes), as shown in Figure 51.
Figure 50
Selection criteria
Figure 51
Sample monthly inventory report
Let’s take a look at the query in the design view (Figure 52). Beg. Bal is a formula that calculates the beginning balance for each month. The formula is shown in Figure 53.
To have the previous month values, you need the formula to change by -1 as shown in Figure 53.
Figure 52
Monthly inventory report in design view
Figure 53
The formula to calculate the beginning balance
Summary
Rather than use an SAP-delivered InfoCube, you created an InfoCube that contains quantity RI and an InfoCube that contains end-stock balance. Over time, an SAP-delivered InfoCube accumulates millions of records and reports, and it takes the InfoCube a long time to execute.
In comparison, my method creates a MultiProvider and has written a report on this MultiProvider that shows beginning month stock, quantity receipts, quantity issues, valuated stock, and ending balance. This report presents prior month information, and executing it takes about 15 seconds.
Sam Gassem
Sam Gassem is senior business consultant and SAP BW lead at Rural Sourcing, Inc. (RSI). He has 13 years of experience on SAP NetWeaver BW. RSI is a leader in domestic sourcing, a cost-effective, on-shore alternative to the traditional model for IT outsourcing. RSI specializes in software development and support and maintenance for critical business applications. Its development centers are located in second- and third-tier cities across the United States. RSI was founded in 2004 by the former CIO of Baxter Healthcare with the intention of bringing jobs to areas of the United States in which historically the job market was dominated by agriculture and manufacturing.
You may contact the author at sgassem@gmail.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
Premium
