
- 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.
For Example:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.