SAP Analysis for office is the de-facto tool for spreadsheet-based reporting on Business Objects in Excel. It can also be used for your planning applications . If you have cubes available in your backend system to planning, then Analysis for Office also allows for implementing a wide range of planning scenarios on a report.
Below , we have showcased a planning application built with Analysis for Office report using Input Ready Query in BW using BI Integrated Planning. Here the user will be able to select one or several source lines in a data (query) and can execute a predefined planning function on these records in the report. This also allows writing values back to the cube for persisting the data. A step by step process is shown below to showcase how this can be done.
Step 1: Creating an Input Ready Query
We will need a input ready query which is then used to build the planning application. Here we have used a simple query on a Planning cube . A planning cube is a must because only a planning cube will allow write back of data that is required for planning applications.
Step 2: Enabling Key Figures for planning
Key figures which are to be changed/edited in Analysis for Office must be checked for Input-Ready. Below is the screenshot highlighting the option.
Step 3 : Enable change mode in the query
Also, the query should be selected for the object to change it during navigation. There is an option to select this under “Query Properties”. Save the query.
Now the Query is all set , we can now go to Analysis for Office to see how to enable the same.
Planning in Analysis for Office
Step 1: Enable Planning Features
Go to the Analysis tab and choose Settings -> Advanced Settings. Make sure that the box for planning features is checked.
Step 2: Create report for Planning
Open the same query in AO and create a report. Enable the cube for load mode to enable planning
On clicking the message,
This results in not being able to perform planning function as shown below
Note: Whenever the Planning Sequence/Planning Function is executed, the Info Provider property should be set to the below shown option
Choose the second option for Planning to be made possible
Step 3: Enable Inputs in the Analysis for Office report
On doing this, data target is switched to Interactive Planning. On the Analysis Components tab of the report, make sure the data source and Input Enabled option is checked.
Going back to the Analysis for Office report, now that planning is possible, edit the key figure values and hit save in Planning Ribbon.
Record is now transferred to the cube. To check that, go back to the cube and choose first option in real-time load behavior.
Verify the output, the record is transferred successfully.
Other features of Planning Functions
If the base values get changed , then all the changed values get checked for consistency , if the changed data entries are correct, the recalculated data is available. The totals are updated and the new values are visible in all other components of the workbook. The recalculation is executed for all data sources in the workbook that are available in the selected planning system.
The ‘PlanDataTransfer’ command from the API method SAPExecuteCommand can be used to recalculate planning data.
Once the data is changed and if the changed data entries are correct, it is saved in the data sources and a message is displayed that the data was saved. The saving is executed for all data sources in the workbook that are available in the selected planning system.
The ‘PlanDataSave’ command from API method SAPExecuteCommand can be used to save planning data.
- Lock Cells
Input-ready crosstab and total cells in Analysis can be locked. A locked cell cannot be changed manually when the crosstab is recalculated. The locked cells are unlocked automatically on executing a planning function or sequence.
To undo your changes, you have the following options:
Choose Back To Previous Saved State
This option undo’s all changes since the last save of the data. The undo is executed for all data sources in the workbook that are available in the selected planning system.
‘PlanDataReset’ command from API method SAPExecuteCommand can be used to achieve the same.
Choose Back To Previous Consistent Client State
With this option, you undo all changes since the last successful recalculation of your data. The undo is executed for all data sources in the workbook that are available in the selected planning system.
The ‘PlanDataClientReset’ command from API method SAPExecuteCommand can be used to revert to the last recalculated state of your planning data.
With SAP moving to an embedded Planning model , you can look at implementing these planning functions in your Analysis for Office workbooks and move to an embedded BI model which helps in maintaining close alignment with the SAP roadmap. Also because the transaction data and planning data are available in the same system , it becomes easy to implement these scenarios and make your system landscape leaner.
Want to know more? Click here to get in touch.