Introduction

This article outlines the steps necessary to extract a copy of the SAP IMG nodes into excel and clean them up into a simple grid.

Why?

So the first question you might ask is why would you want to extract the thousands of nodes of the IMG into an excel file.   From my experience I have been asked to perform this task on many projects, primarily to help with scope definition and assign and manage responsibilities. This may seem redundant these days, with the integrated tools that allow for project management in solution manager that allow you to represent this hierarchy and report against it in many ways. However in the fog of a time constrained implementation I can assure you that the learning curve and access issues to all the necessary tools, often causes things to spiral down to the lowest common denominator of representing information in a system that most people have on their computers and are familiar with. In most cases this ‘lowest common denominator’ is excel.

What can I do with this?

I have used an excel extract of the IMG on multiple projects for the following reasons, but I’m sure you could think of many more

· Create a list of in and out of scope nodes to assist with scoping

· Assign formulas to ‘estimate’ work and skills mix for a given scope

· Assign ownership of nodes within projects with multiple disparate teams

· To form a document management system to track configuration documentation

· To track configuration completion for project management

Extracting the IMG to Excel

Preparing the IMG format

The first step in extracting the IMG is to ensure that it is represented in the right format to allow extraction. The default display settings do not allow extraction.

Go to transaction SPRO

Select Settings > User Specific

Make sure that the ‘Disp. Struct. with tree control’ is not checked

From SPRO go into the SAP Reference IMG

Displaying additional Node information

Although many of the more recent IMG nodes do not have a transaction code associated with them the older ones do and every node has a unique identifier that helps identify duplicate nodes that are repeated throughout the IMG. I find it useful to display this information as part of the extract. To do this, from within the SAP reference IMG select Additional information > Display Key > IMG activity

Now when you open an IMG node down to the most detailed level you will see an additional key

These keys are unique for a specific configuration node so you can use this to identify replication. Additionally the last four or five digits are often the tcode of the configuration node if one exists. E.g. in the highlighted example above the tcode OMJI takes you directly to the block for goods movements. Before extracting to Excel it is necessary to open all nodes (or as many nodes as you want to extract) as the extract only takes the levels open on screen. To do this use the expand all button a the top menu

Extracting to Excel

With the open IMG now just go to the system menu and select System >List > Save > Local File

Select spreadsheet as the doc type form the pop up

Then choose a location and filename to same your file and press generate

Cleaning up the Excel File

Now the fun begins as the Excel file is in a hierarchical format that you may want to clean up. Open the file in Excel

The file will look something like below

As you can see the IMG folders are nested, and depending on how deep the configuration node is it could be in different columns. However as we added the IMG activity column this is always in the same column and can help us identify which nodes are configuration nodes versus folders. I personally like to build the IMG structure into every line, and then delete the non configuration nodes afterwards. I.e. in the example above I only want to keep rows with an entry in column I, but without the structural information it is not clear what each node is i.e. Define prioritizations doesn’t mean anything on its own without knowing it is Materials Management > General Settings for Materials Management > Requirement Prioritization > Define Prioritizations

So this will take some manipulation to get the data in this format. There are many ways to do this but the simplest way I have found is to do as follows

Initial worksheet clean up

Firstly remove the blank rows and columns at the top and left

So that the words ‘SAP Customizing Implementation Guide’ are in cell A1

Determine the lowest Node

In this step it is important to determine the lowest node where an entry exists for each record this will enable the correct copying of data in the next steps

Firstly insert a row above row 1 and number the columns with data from the extract 1 through 10

Next in the first empty column after the data (K) enter the following formula

This looks complicated but basically it just copies the number in row 1 where there is writing

=IF(A2<>”",$A$1,IF(B2<>”",$B$1,IF(C2<>”",$C$1,IF(D2<>”",$D$1,IF(E2<>”",$E$1,IF(F2<>”",$F$1,IF(G2<>”",$G$1,IF(H2<>”",$H$1,0))))))))

This will then indicate the lowest level of detail for each record; you can copy this formula down all of column K

Next you must copy and paste just the values in place to avoid any formulas in column K so for this select the column copy then paste special as values We only want to use this values for configuration nodes for all other rows we want to set the value 1. So for this we create the following formula in row L

Copy this formula down column L for the whole dataset, then copy column L and paste the values over column K

This has the result of showing the lowest level of a configuration node and showing level 1 for all non configuration nodes

Create formula to copy from cell above

In Cell A2 use the formula below to read the value form the cell above

=IF ($K3 = 1,A2,IF(A$1>$K3,”",A2))

This formula will copy the line above (unless there is a configuration node at a higher level)

Press control C to copy to the clipboard, now highlight columns A through H only (you do not want to have the IMG activity column selected)

Select all blank cells

Now this is the magic. To select all the blank cells in the columns you have highlighted use the select and find menu and select > goto special function

Select the blanks option

Paste formula to blanks using control V

You’ll now get a file that looks like below

In the initial rows there is some copying of the level numbers this can quickly be cleaned up though manually after the values have been copied below

Copy whole region as values

Firstly deselect the current selection by clicking in cell A1.

Then use the select and find menu and select > goto special function again and this time use the current region option

This should select all of your data. Now use the control C to copy

Then use paste special and values to overwrite the forumulas with text

Now you have just cells of text. You can manually clean out the numbers from the first few rows. You can also delete columns K and L at this point (configuration level)

This will give you a much cleaner spreadsheet

Delete non configuration nodes

Now reduce the size of the spreadsheet by keeping only the configuration nodes. This can be done by filtering on the IMG activity column for blank rows and removing them.

To do this insert change the cell values in row one from 1,2,3 etc to . I use L1, L2, L3 etc and configuration node as below

Now select the whole area again using the find and select function this time using current region function

Now insert a table with headers

Now select the configuration column drop down in the table and deselect all values and select only blanks

This now shows only the non configuration nodes

Select all rows and delete them, now you can remove the filter to leave only the IMG configuration nodes

Now you have an excel spreadsheet with only the configuration nodes in the IMG each with their full menu path structure contained in that record

Unfortunately SAP parses longer names to an extra column so in some cases the ends of words are in column J you could manually copy them back into the correct column or just delete it.

What Now?

Now you are free to add your own columns to show scope, ownership, completion, and documentation. So it’s really up to you how you use this spreadsheet.

Should this be done on all projects? Probably not. There are better tools provided in solution manager etc for managing scope / etc. however if your team is diverse and for infrastructure and training purposed you do not want to roll out formal tools to a large number of users , you can generally rely on most people being able to open and update an Excel spreadsheet. And with Microsoft’s tight integration with other office products, a detailed project plant is only a few clicks away

Want a Free copy of the entire ECC6.0 IMG in Excel for Free ?

if this all sounds like too much work and you’d like a copy of the ECC 6.0 IMG already formatted in Excel along with a PDF of this article, then simply follow me on twitter (using the button to the right) then send the following tweet @mungapensaplog…send ref P=98