This is a continuation of blog series SAP CPM, Which planning content is right one for me? by our product owner Ravindranath Nayak. In this blog, I will explain how to create a planning application using SAP CPM planning content.
We have identified planning content for periodic planning from previous blog. So the input ready query name is /CPD/PFP_M_A2_IRQ0001. I will insert this query into SAP Analysis Office workbook to create an application.
Before you start, I recommend that you create some dummy planning data using example workbooks that are delivered with SAP CPM. This will help you in rearranging the columns or sizing them appropriately.
I am aiming for a layout something like this.
Follow these steps:
- Open Analysis for Microsoft Excel from Windows start menu. Place the cursor in the required where you need to insert the query in the empty workbook.
- Click on Analysis –> Display –> Components.
- Select and right click on “Book1” and choose “Use Data Source”.
- Click on Skip button when the “Logon to SAP BusinessObjects BI Platform” pop up occurs. Now choose the SAP system where definition of input ready query resides. Enter the login credentials.
- Search for the required data source and click on Ok button. Let me take an example of inserting a standard Periodic Planning Input ready query.
- It is recommended to fill the Prompts by passing the example data you have created before you started this exercise and click on Ok button.
- Right click the query added below the Book1 and click on Insert Crosstab.
- Specify the range of the cell in which you want the query to be displayed and click on Ok button.
- Congratulations! You have successfully inserted a new input ready query into workbook.
Let’s now insert couple of planning functions:
- Goto Components Tab in the Design Panel and right click on the “Book1”.
- Choose Planning Function or Planning Sequence.
- Search for the required Planning Function or Sequence and click on Ok button. Let me take an example of inserting a standard Calculate (Fiscal Period) Planning Sequence (/CPD/PFP_M_A2_PS02).
- Congratulations! Planning Object is inserted. We need now a button on the UI to associate this planning function.
Let’s insert a button and associate it with planning function:
- Goto Developer tab in the Analysis office ribbon.
- Click on the Insert dropdown and choose the button which you need based on your requirement.
- On inserting the button Assign Macro pop up occurs.
- Change the Macro name if required and click on New button. Let’s name the button as “Calculate”.
- On click of the New button then the module will be inserted with a subroutine.
To Map the inserted Planning Object with the created Button:
In this section I am trying to execute the Planning Object on click of the newly created “Calculate” button.
Below steps guides you to create a simple logic to execute the inserted planning object:
- Initial step would be to refresh the inserted Planning Sequence.
- Next step is to retrieve the list of variables of the Planning Sequence.
- Next step is to get the values for the dimensions of the inserted data source or query.
- Then the obtained values of the dimensions of the query would be set to the Planning Sequence.
- Last step would be executing the planning sequence by passing all the obtained values.Below are list of SAP Analysis Office API used in simple code snippet given below:
Sl.No. Analysis Office API 1. SAPExecuteCommand 2. SAPListOfVariables 3. SAPGetDimensionEffectiveFilter 4. SAPSetPlanParameter 5. SAPExecutePlanningSequence
Note: If you are trying to execute a planning function then relevant AO API’s should be changed.
To Insert a Dynamic Analysis Chart:
- Select a cell of the crosstab you want to visualize in a chart. By inserting a chart with Analysis, the data of the entire crosstab is visualized in the chart. If you want to visualize only a subset of the crosstab data, you can use Microsoft Excel functionality. Note: If you change the data displayed in the crosstab, e.g. with a filter, the chart is adapted automatically.
- Choose Chart à Insert Chartfrom the Analysis Tab in the AO ribbon.The chart is added to the analysis. You can position it in the worksheet using drag and drop.
To Modify a chart:
To modify the chart, you can use Microsoft Excel options for charts. For example, you can change the chart type or define a data range for the chart. You can move the chart to another worksheet in the workbook.
On the Component tab in the design panel, select and right click on the chart you want to move and click on “Move to”. The Move To dialog opens up in that select the sheet that should contain the chart and press OK.
Let’s add some meta data of the project:
- Select the cell and add the following formulas.
- Use “SAPGetDimensionInfo” API to get the name of the dimension of the inserted query.
- Use “SAPGetDimensionEffectiveFilter” API to get the defective filters of the dimension of the inserted query.
Let’s save the new workbook application and see how it looks:
Now let’s save the workbook on NW server.
- Goto File –> Analysis –> Save Workbook –> Save Workbook to the SAP NetWeaver Platform.
- In the Save documents pop up choose My documents and enter the name and description of the workbook.
- Click on Save button. The workbook gets saved to the SAP NetWeaver server.
We are just a step away. Adjust config to add the newly created workbook:
- Follow the given path: SAP Reference IMG –> SAP Customizing Implementation Guide –> SAP Commercial Project Management –> Project Cost and Revenue Planning –> Define Plan Types.2
- Based on your requirement choose the Plan type and click on Link Workbook. In my example let me select “0PTF” as my plan type and choose “Link Workbook for Planning”.
- Click on New Entries and enter the created workbook name and save the customizing. Now the newly created workbook can be launched from the Financial Plan screen of Commercial Project Management.
Your output should be looking something like this.
Stay tuned. Next blog in this series will demonstrate, how one can extend planning model such as showing a calculated column such as % discount or changing layout.