Create an Excel based BPC Admin Consol Using VBA, ABAP and BPC10.x

[gajendra_moond]

Purpose:

As BPC Admin, we need to switch a lot between Excel EPM Addin and Web Client for various administration tasks. To enhance the productivity and save some moments, I have worked out a BPC Admin Consol using some ABAP, VBA and BPC and developed an Excel based BPC Admin Consol.

Features:

You will get the BPC configuration and BPC tasks in the same workbook. No need to switch to Web client for Model details, secured dimension, dimension structure etc.

System Requirements:

BPC10.x on SAP NW

EPM Addin Version 10.0 SP15 or later

Excel 2007 or later

Configuration:

The workbook contains two sheets:

• BPC_ADMIN_CONSOL - For common BPC Tasks
• CONFIGURATION_DETAILS - For details regarding models and dimensions

There are three parts to the development.

1. ABAP - Function Modules to extract BPC configuration
2. VBA - Connecting and Extracting data from ABAP FMs and Formatting it
3. Excel - For Name ranges to use in drop-downs

The BPC ADMIN CONSOL looks like the following:Let us check out the CONFIGURATION_DETAILS sheet first. This is how it looks like:As you see, there will be four FMs for each of the table based on Environment ID:

• FM for Model List
• FM for Package List
• FM for Model Structure
• FM for Dimension Structure

You can definitely utilize your ABAP and VBA expertise to get more information but this is just the starting point to explain. All these FMs will be Remote-Enabled Modules. I would advise to create a separate package for these ABAP developments just to keep the work area separate.

Ensure that all FMs you create are Remote-Enabled as shown below:

Refer to FM Documentation.txt for the source code and import parameters along with output table structure.

Once these FMs are created, we will connect to EPM Excel Addin. Open up a new excel file and ensure that following references are maintained:

Some references might be extra but I added them to be on the safer side.

Create two sheets in the excel file and name them as "BPC ADMIN CONSOL" and "CONFIGURATION_DETAILS".

Press Alt+F11 and go to VBA editor. Insert the module and copy the code in the file "Module Code.txt" and copy the code in "Sheet Code.txt" in worksheet "BPC ADMIN CONSOL".

In your module code, replace "Your ID" with your BPC ID and "Your Password" with your BPC Password.

You have to do the following:

- Maintain system details in sheet "CONFIGURATION_DETAILS"

- Maintain BPC URL in sheet "BPC ADMIN CONSOL"

- Remove "Convert to formula" in cell B5 of the sheet "BPC ADMIN CONSOL"

- Use the cell references as show in the screenshots so that attached code works perfectly for you.

In CONFIGURATION_DETAILS sheets, add five button as show in the screenshot above and assign macros to them as follows:

Button	Macro Name
Connect to BW	LogOn
Get Model List	GetModelList
Get Package List	GetPackageList
Get Model Structure	GetModelStructure
Get Dimension Structure	GetDimensionStructure

Enter Environment ID and then click "Connect to BW" button. This will connect you to the backend BW system. Now click on each button and get the various lists.

Once these lists are populated, we will create name ranges to be used in the BPC ADMIN CONSOL sheet for dynamic dropdowns.

Here are the name ranges you should be creating:

Name Range ID	Formula
Model_List	=OFFSET(CONFIGURATION_DETAILS!$B$11,0,0,COUNTA(CONFIGURATION_DETAILS!$B$11:$B$20),1)
Model_Start	=CONFIGURATION_DETAILS!$E$10
NEW_PACKAGE_LIST	=CONFIGURATION_DETAILS!$F$11:$F$2000
PACKAGE_DETAILS	=OFFSET(CONFIGURATION_DETAILS!$E$11,0,0,COUNTA(CONFIGURATION_DETAILS!$E$11:$E$2000),1)
PACKAGE_GROUP_LIST	=OFFSET(CONFIGURATION_DETAILS!$F$11,0,0,COUNTA(CONFIGURATION_DETAILS!$F$11:$G$2000),1)
PACKAGE_LIST	=OFFSET(CONFIGURATION_DETAILS!$F$11,0,0,COUNTA(CONFIGURATION_DETAILS!$F$11:$F$2000),1)

Now we are ready for sheet "BPC ADMIN CONSOL"

You need to put "Data Validation" for following cells as follows:

Allow: List and Under Source copy the following for the cells defined:

Cell	Data Validation (Source)
K5	=Model_List
F16	=OFFSET(Model_Start,MATCH(K5,PACKAGE_DETAILS,0),1,COUNTIF(PACKAGE_DETAILS,K5),1)
F23	Input Schedules,Reports

You can now take drop down of the models and packages extracted in the sheet CONFIGURATION_DETAILS and it will dynamically select the team and allow you to run the package, open reports and input schedules corresponding to a given model.

Remember to save your file as .xlsm.

This can further be enhanced depending on your ABAP and VBA expertise. I understand it will be some work for you guys before you get it working but I hope it will be a useful tool for you in your BPC projects.

Enjoy!