Skip to Content

This blog entry will show the configuration of Integrated Excel Planning that will help in uploading plan data against WBS’s from Excel sheet. I searched around on this topic for a while and never found a complete configuration guide on Excel Integration. The blog explains all steps right from creation of planning layout to uploading plan values. Though the example used in uploading the plan values against WBS elements, steps would be similar for other CO objects like., IO and cost Centres.

Introduction

For planning with integrated Excel, Microsoft Excel is used as a planning screen for your planning data in the SAP System. This allows you to combine the comfort of Excel’s formatting and data processing functions with the powerful planning functions of your SAP application. You are therefore able to use the Office input interface with online planning in the SAP System. There are two ways of using Excel to plan your data:

  • Planning with Excel integrated into the user interface of the SAP System

Excel replaces the standard SAP planning screen. This enables you to plan using the functions of both the SAP System and Excel.

  • Offline planning in Excel and uploading data into the SAP System

From one or more PCs that are not connected to the SAP System, you can enter your planning data into several Excel spreadsheets. This data can then be uploaded into the SAP System, where you can process it further using the SAP planning functions.

We are going to proceed with below steps:

  1. Creation of Planning Layout.
  2. Creation of Planner Profiles.
  3. Offline Planning in Excel and uploading the values.

All of these are can be done by a functional consultant, so let us start with the configuration.

Creation of Planning Layout

Here we create a planning layout copying an existing layout. The layout is created to upload plan values against WBS and Cost Element.

Enter T Code OPO1.

image 

Option to Create/Change/Display planning layouts appears.

image

Double click on Create PS Planning Cost Element/Activity Input Layouts to create one. Copy the Planning layout 1-701 and mention the planning layout ‘SDNWBS’ and click on Create.

image

All details of the referenced layout 1-701 are copied into SDNWBS layout.

image 

Here we amend the copied layout to our requirement. So delete the elements/columns  that are not required.

image

Similarly delete the other columns and now we are left with Cost Element (characteristic values) and Total Plan value (key figure).

image 

Now we add the WBS element as one of the lead column value. We do this by moving out the WBS from the general data selection and use it as lead value in layout. But before that click on New lead column, this inserts a column which will be edited to use WBS element as a characteristic value.

image 

Move the WBS element into available characteristics

image 

Perform the check and confirm. There should be no errors here.

image 

Change the lead column element.

image 

Move the WBS element from Available characteristics to left.

image 

Mention the variable and other details of the selected characteristics as shown and then perform the check and confirm. No error should be observed here.

image

For the lead column set the option to display the WBS element along with the description, this help pulls up the description of WBS into planning layout.

image

Planning Layout is complete now. Save the layout! Layouts are transportable.

image

Creation of Planner Profiles.

Here we create a planner profile that uses the layout created above. Settings for Excel Integration are done here.

T Code KP34.

image

Double click on Create User-Defined Planner Profiles

image

Create New Entries.

image

Mention the name and description of the profile.

image

Under the General Controlling screen enter the planning area as shown below.

image 

Under the layout for controlling screen create new entries to mention the layout created earlier and mention a file description. Ensure to check Integrated as this allows excel integration. System might issue a warning, ignore it.

image

Under the Default parameters screen mention the default parameters (to bypass the standard system checks for generating the excel file name) and click on overview icon to open the planning layout in Excel.

image

Open the Planning layout with ‘Enable Macros’

image

The planning layout in opened Excel with the default parameters mentioned.

image

Save the Excel layout and just be sure save the file and save the file description.

image

image

image

image

Under the Excel application choose ‘File’ and ‘Save as’ to save template onto the desktop. This allows offline planning and later uploading the values.

image

Exit the planner profile setup saving the preset parameters.

image 

Save the planner profile and with we are done with the configuration.

image

Offline Planning in Excel and uploading the values.

Now that we the configuration in place, we choose/set the configured planner profile using the T Code KP04.

image

Open the Excel template saved on the Desk top and complete the planning. The Excel sheet opened is usually write protected, so undo the protection, complete the planning and save the Excel. After we are done and satisfied with the Excel Sheet planning, the file needs to be save/converted into text file for uploading. Note we are trying to load values against 2 different WBS in different projects.

image

Use T code CJR2. From Menu choose Extras>Excel Planning>Upload.

image

Mention the Path of the file, File description (as mentioned in the planner profile) and executed.

image

That’s it. The log confirms the values have been successfully uploaded.

image

In CJR2, instead of Excel planning, you can still do normal way of planning but the layout is in Excel now.

Plan Values as viewed on any standard WBS report.

image 

Excel upload has few limitations on the number of records that can be uploaded in a single file. 

Related OSS Notes 698010,499152,489867,416387,391316 & 397799

This is my first blog!  Expect your feedback be it negative or positive.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

    1. Savitha S
      Hi Srinivas,
      I’m a basis consultant and I’ve the below question.Could you please tell me what needs to be done to overcome this issue?

      “When more than one person tries to edit a range of WBS3 plans using CJR2, the second person who goes into CJR2 to edit the WBS3 plans receives an error: “The records are already being processed by userxxx”.  The second person is then prevented from saving any changes made to the WBS3 plans.  The ranges of WBS3 plans that the two people are editing do not overlap.”
      Thanks,Savitha

      (0) 
  1. Anjaneya Prasad K
    Thank you srinivas for posting such a detailed document. This functionality is least explored but now iam very confident using this tool.
    We need such valuable guidance from you senior guys, so that we can work confidently..
    Thank you
    Amruta
    (0) 
  2. Rakesh Pradhan
    Hi Srini,

    Thanks a lot for your Excel tool integration with SAP-PS. I had seen Excel integration with SAP in xRPM / cProjects. You have done good job. Hope you will come up with new details also.

    Regards,
    Rakesh Pradhan

    (0) 
  3. Rama Wunnava
    Hello Sreenivas,
    This is regardiing your blog of 2008 on the subject topic. The information provided is very useful but I have a question.
    How do we change the default ‘Decimal Notation’ from EU format (xxx.xxx,xx) to US format (xxx,xxx.xx)?
    Could it be done by any config changes or do we need to copy the whole program and modify the default selection? Any help in this regard is highly appreciated.
    Thanks.
    (0) 
  4. Gail Mudgeway
    This has been a great help to me in uploading planning data from a spreadsheet for internal orders. I have also gone on to use a later blog you wrote about uploading the planning data by period.

    I have tried to create a layout which will provide the ability to upload a combination of period and total plan data using a DK.

    Unfortunately I cannot get this to work but will investigate further

    (0) 

Leave a Reply