Using Planning Objects in Analysis For Microsoft Excel

[Former Member]

Create the Query and the Planning Sequence

Please use the BEx Query designer to create a suitable query. Keep in mind that you probably use other characteristics and thus will have to adapt the solution everywhere where we use our characteristics

Please also create a suitable planning sequence. In our example we use a revaluation function for copying the budgeted values

     Create the Workbook

Open SAP BusinessObjects Analysis for Microsoft Office. Please make sure you are using version 1.1 or higher. Also please keep in mind that the planning capabilities are not switched on by default. Please check the settings:

Go to the Analysis tab, and choose the entry ‘settings’. Check that the check box for the planning features is tagged.

We are ready to insert our revaluation function.. Please display the Analysis panel and navigate to the components tab. On the name of the workbook use the context menu to use the planning Sequence.

Enter the name of your planning Sequence or Planning Function.

Now we have to choose a fitting filter for the planning Sequence.

For executing Planning Sequence filter values have to be provided. Filter values can be used in two ways

1) "Members"  Option: Here we will directly input the values of filter for which we want to execute the Planning sequence

2) "Cell" Option: Here values can be mapped dynamically using cells in the excel sheet where these values will be populated from selection screen of BEx Query  

Insert a Button with the VBA Coding

We finally create the button for executing the revaluation function. In the ‘Developer’ tab in the Excel ribbon choose the button ‘Insert’.

If you cannot find this tab you have to enable it in Excel under ‘Options->Customize Ribbon’.

In our example we use certain names and aliases for our planning function, characteristics, and name of the crosstab. We have used a variable for each of them at the beginning of our VBA coding so you can easily adapt it to your names:

Following Code has to be used behind the button for execting the embedded planning function or Planning sequence.

Planning Function:

Dim lResult As Long

lResult = Application.Run("SAPExecutePlanningFunction", "PF_1")

Planning Sequence:

Dim lResult As Long

lResult = Application.Run("SAPExecutePlanningSequence", "PS_1")

Where PF_1 and PS_1 are Formula Alias name given for respective Planning Objects.