Skip to Content
Personal Insights
Author's profile photo Pallavi Dwivedi

Reorder Columns automatically in IBP Master Data (Excel UI)

Usually in a client implementation project, IBP consultants create many custom attributes to cater to multiple business requirements. The data in these attributes are reviewed by the consultants and end users in the “Master Data Workbook” option in IBP Excel UI. However, in a scenario where we have many attributes, it requires the users to scroll left or right to find the relevant attribute. In such cases, it would be helpful if we could re-sequence the attributes in the master data based on our custom sequencing. For example, it would be good to see Product ID and Product Description next to each other when we open Product Master data.

The utility developed will help in re-sequencing of all attributes in the master data with one click, thereby saving a lot of time in searching for relevant fields in the master data.

To define your custom sequence of master data attributes, follow the below steps-

Step 1

Download the SAP Hook for Master Data and place it in the Addins folder (C:\Users\xxx\AppData\Roaming\Microsoft\AddIns) of your laptop.

Please replace xxx with your laptop/desktop user ID-

For more information on SAP VBA hook, refer SAP Note – https://launchpad.support.sap.com/#/notes/2421657

Step2

Update the SAP Hook with additional Code as below-

  1. Code to be copied outside the Function Block>>
Sub Reorder_Column()

' Reorder_Column Macro

Dim ColumnOrder As Variant, ndx As Integer

Dim Found As Range, counter As Integer

Dim Sht As Worksheet

Dim add As Integer

If Cells(2, 1) = "#" Then

    add = 1

Else

    add = 0

End If

If add = 1 Then

    rows("1:1").EntireRow.Hidden = False

    Range("A1").Select

    ActiveCell.FormulaR1C1 = "#"

End If

If ActiveSheet.name = "Product" Then

    If add = 1 Then

    ColumnOrder = Array("#", "PRDID", "PRDDESCR",  _

    "BRAND", "UOMID", “UOMDESCR)

    Else

    ColumnOrder = Array("Product ID", "Product Desc", _

    "Brand ID", "Base UOM”, “Base UOM Desc.”)

    End If

    counter = 1

            Application.ScreenUpdating = False

            For ndx = LBound(ColumnOrder) To UBound(ColumnOrder)

                Set Found = rows("1:1").Find(ColumnOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

                If Not Found Is Nothing Then

                    If Found.Column <> counter Then

                        Found.EntireColumn.Cut

                        Columns(counter).Insert Shift:=xlToRight

                        Application.CutCopyMode = False

                    End If

                    counter = counter + 1

                End If

            Next ndx

            Application.ScreenUpdating = True

End If

If add = 1 Then

rows("1:1").EntireRow.Hidden = True

End If

End Sub
  1. In the function “IBPMDAfterRefresh”, call the above Sub using the below code-

 

Call Reorder_Column

 

Note-

  • For each master data attributes that you wish to re-sequence, update the order in Array (“#”, “AttributeID1”, “AttributeID2”, … and so on. Don’t delete “#” from the code and make sure that you add the correct Attribute ID as per configuration >> Refer 1 in image above
  • For each master data attributes that you wish to re-sequence, update the order in Array (“Attribute Description 1”, “Attribute Description 2”, “Attribute description 3”, … and so on. Make sure that you add the correct Attribute Description as per configuration. Refer 2 in image above.
  • The Sub Reorder_Column shows how to re-sequence attributes in Product master data. If you wish you re-order more than one master data, then copy the same code (Complete IF Block as in above image) and update the master data name to the desired Master data >> Refer 3 in image above
  • Save the code

 

Step 3

Load any master data from IBP Excel UI.

Before the SAP VBA Hook is updated with the code to reorder columns-

After the SAP VBA Hook is updated with the code to reorder columns-

 

I hope that the above code helps in re-sequencing the master data attributes in IBP Excel UI automatically and save time by avoiding repetitive copy-paste tasks.

If you find the above post helpful, do share your thoughts and feedback in the comment section. In case of questions, please post questions in the community by linking to the respective community tag for “SAP Integrated Business Planning for Supply Chain”: https://answers.sap.com/tags/67838200100800006742

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bartlomiej Jagodzinski
      Bartlomiej Jagodzinski

      Thank you for the article - there's one IBP limitation to it - such modified template cannot be saved centrally for others to use.

      It can only be saved locally or distributed through other means (email, sharepoint), hence deploying it to broad user community as default is for now a process outside of IBP.

      Do you know by any chance a solution/workaround to distribution of such templates to planners?

      Author's profile photo Pallavi Dwivedi
      Pallavi Dwivedi
      Blog Post Author

      Yes, you are right. I am not aware of a method to deploy this automatically for the planners. This needs to be circulated via email/sharepoint/onedrive at this point.

      Author's profile photo Ayan Bishnu
      Ayan Bishnu

      Hello Bartlomiej Jagodzinski

       

      I am currently working on this very similar requirement that you have mentioned. It is progress and once the development is cvomplete i will post the required codes a documents to Github under SAP IBP thread.

      In short I am leveraging VBA along with active x control to develop the template. As of now planning view templates are up and working fine. for MDT sheets its in progress.