Skip to Content

This document demonstrates how to create a custom ribbon with menu buttons in BPC Input Schedules. It includes use of Custom UI Editor for Microsoft Office along with VBA coding. 

Overview

SAP BPC N/W 7.5 version does not have any standard functionality to add a custom ribbon. There is a How to document by SAP (Creating Custom Menu within BPC NW) which describes more about adding custom menu buttons in the Input Schedules layout. Whereas, this paper explains how to add a custom Ribbon with menu buttons in BPC Input Schedule using Custom UI Editor for Microsoft Office. In some business cases, standard BPC Functions such as expand, refresh, send data, update dimension members etc. can be triggered by adding menu buttons on the custom ribbon using VBA code.

Add Custom Ribbon on Input Schedule Layout

  1. In order to add a custom ribbon, we need to download Custom UI Editor for Microsoft Office. It can be downloaded from the link below.  http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx
  2. Save your BPC Input Schedule as a macro-enabled file with .xlsm extension, example Test_Ribbon.xlsm and close the file.
  3. Start the Custom UI Editor and go to File menu and click on Open button and select your file.
  4. Right-click on the file name in the left pane, and then select Office 2010 Custom UI Part as shown below. This adds a customUI14.xml part to the                   document.

      /wp-content/uploads/2012/04/1_90942.png

5. A sample code is shown below. Customize the code as per your requirements in the right pane.

     <customUI xmlns=http://schemas.microsoft.com/office/2009/07/customui>

            <ribbon>

                   <tabs>

                          <tab id=customTablabel=Custom RibboninsertAfterMso=Viewkeytip=S>

                                <group id=GrpLogOnlabel=BPC Log On>                 

                                       <button id=BtnLogOnlabel=Log OnimageMso=LockonAction=BPC_LogOnsize=large  />

                                </group>

                               <group id=GrpSelectlabel=Plan Selections>

                                      <button id=DDSelectlabel=SelectimageMso=SelectionPropertiessize=largekeytip=P

                                        onAction=User_Selections />

                                </group>

                                <group id=GrpPromolabel=Manage Promotions>

                                     <menu id=DDPromolabel=PromotionsimageMso=AdministrationHomesize=large

                                        keytip=F>       

                                            <button id=MbtnCreatePlabel=Create PromotiononAction=CreatePromo />

                                            <button id=MbtnChangePlabel=Change PromotiononAction=Change_Promo />

                                            <button id=MbtnDeletePlabel=Delete Promotion

                                              onAction=Delete_Promo />

                                     </menu>

   <button id=MbtnUpdatelabel=Update to CRMimageMso=AddContentType

     onAction=UpdateToCRMsize=largekeytip=H />

                                </group>

                                <group id=GrpWorkbooklabel=Workbook Options >

                                     <button id=MbtnSavelabel=SaveimageMso=SaveItemonAction=Save_Datasize=large

                                       keytip=WS/>

                                     <button id=MReflabel=RefreshimageMso=RefreshonAction=Refresh_Datasize=large

                                        keytip=WR/>

                                </group>

                        </tab>

                   </tabs>

            </ribbon>

     </customUI>

     6. This code performs the following actions:

        a. First, it adds a custom tab to the Excel sheet. Code to add it is mentioned below 

               <customUI xmlns=http://schemas.microsoft.com/office/2009/07/customui>

                    <ribbon>

                         <tabs>

                              <tab id=customTablabel=Custom RibboninsertAfterMso=Viewkeytip=S>

                              </tab>

                         </tabs>

                    </ribbon>

               </customUI>

          b.  Then, it adds menu buttons to the custom tab. These buttons can be drop down as well.

               <group id=GrpSelectlabel=Plan Selections>

                    <button id=BtnSelectlabel=SelectimageMso=SelectionPropertiessize=largekeytip=P

                      onAction=User_Selections />

               </group>

               <group id=GrpPromolabel=Manage Promotions>

                    <menu id=DropDownlabel=PromotionsimageMso=AdministrationHomesize=large

                      keytip=F>              

                         <button id=MbtnCreatePromolabel=Create PromotiononAction=CreatePromo />

                         <button id=MbtnChangePromolabel=Change PromotiononAction=Change_Promo />

                         <button id=MbtnDeletePromolabel=Delete PromotiononAction=Delete_Promo                                                             

                    </menu

               </group>

           c. There are two types of image attributes that can be used to add images on the buttons on the Ribbon:

      • The imageMso attribute is used to add images that are built into Microsoft Office. Mention the name of the image to be displayed and its size.

                       e.g.:  imageMso=SelectionPropertiessize=large

                       The list of built-in images can be downloaded from here. 

                       (http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21103)

      • The image attribute is used to add custom images to the Ribbon. In the Custom UI Editor, go to Insert menu and click on Icons button to insert the image. You can see it now below the customUI file.

                       e.g.: image=Custom_Imagesize=large

                              /wp-content/uploads/2012/04/2_90943.png    

           d. The onAction attribute specifies the macro that is executed when you click the button.

            e.g.:  <button id=BtnLogOnonAction=BPC_LogOn

    7.  Once the code is ready, click the Validate button on the toolbar to verify that your .xml is free from errors. When

        the .xml is error free you will receive a message stating that the code is well formed.

   8. Save and then close the editor.

Add VBA Code to the Test_Ribbon.xlsm file

  1. Open the Test_Ribbon.xlsm file. The “Custom Ribbon” tab that you created should now be displayed.

     /wp-content/uploads/2012/04/3_90969.png

   2. Click the Developer tab and then click on Visual Basic. This opens the Visual Basic Editor.

   3. In left pane, right-click on VBAProject(Test_Ribbon.xlsm) node. Click on Insert and select Module.

    /wp-content/uploads/2012/04/4_90970.png

   4. Add the following procedures to the inserted module. The name of the procedure should be same as mentioned in the onAction part of the .xml code.

       For example, for BPC Logon Button, the corresponding macro is called as mentioned below.

       Custom UI code:

         <button id=BtnLogOnonAction=BPC_LogOn

VBA Code appendix


    

    ‘For the Log On to BPC Button

     Sub BPC_LogOn(control As IRibbonControl)

         Dim myval As Boolean

         Dim AI As Excel.AddIn

         On Error Resume Next

         myval = Application.AddIns(“Ev4Excel.xla”).Installed

         On Error GoTo 0

         If myval Then

             Set g_clsExcel = GetEVExcel2007()

             g_clsExcel.Logon 1

         Else

             Set AI = Application.AddIns.Add(Filename:=”C:\Program Files\SAP BusinessObjects\PC_NW\ Ev4Excel.xla”)

             AI.Installed = True

         End If

     End Sub

     ‘For the Select Button

       Sub User_Selections(control As IRibbonControl)

           Selection.Show

       End Sub

     ‘For the Save Button

       Sub Save_Data (control As IRibbonControl)

           Application.Run “MNU_eSUBMIT_REFSCHEDULE_BOOK_NOACTION”

           Application.Run “MNU_eTOOLS_EXPAND”

           Application.Run “MNU_eTOOLS_REFRESH”

       End Sub

    ‘For the Refresh Button

       Sub Save_Data (control As IRibbonControl)

           Application.Run “MNU_eTOOLS_EXPAND “

       End Sub









To report this post you need to login first.

1 Comment

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

  1. Somar B

    Thanks for sharing.

    Has anyone in BPC 7.5 tried to created ‘action pane’ as a ribbon, just like in BPC 10?

    (0) 

Leave a Reply