Adapting SAP Sample Planning Views

Objective

After completing this lesson, you will be able to adapt a predelivered Microsoft Excel template

Adapting SAP Sample Planning Views

In the previous unit, we created planning views from scratch. A lot of users of SAP Integrated Business Planning start their work in the Microsoft Excel UI with the predelivered template. VBA templates that are available for download are very flexible. There are two options:

  1. Using the embedded template, where all VBA code is inside the Microsoft Excel workbook. We use such a template in our next exercises. For your implementations, you should use the archive and instructions that are available with SAP Note 1790530. ChartVBA_Embedded. xlsm that is available there would be updated with IBP release.
  2. Using VBA Add-in templates, where the central code resides in an additional Microsoft Excel add-in. It means that users must install an additional add-in. The same note 1790530, provides the SAP_IBP_Chart. xlam file and directs to the exact path for placing the file and enabling it.

Note that another good resource to use to access multiple artifacts, is SAP Best Practices Explorer (www.rapid.sap.co/bp/). There you can search for SAP IBP and then for Technical Assets for SAP IBP (there will be options for time-series, order-based and demand-driven replenishment). You can download sample planning views that are adapted for different planning processes (demand, supply planning with heuristics, supply planning with the optimizer, inventory optimization, and so on).

Creating a Template from Predelivered ChartVBA*.xlsm

Defining multisheet or single sheet views

  • If you need a single sheet view:

    Delete the second worksheet that came with the predelivered file.

  • If you need a multisheet view:

    Copy the sheet as many times as you want. Deleting extra sheets later is easy.

When you are logged on to SAP IBP, you can start defining your new view starting from the cell next to Start Here. When the planning view is rendered, you can work with the chart. You can select key figures to plot and filters to use on the chart. If you want to focus on table format predominately you can hide the chart.

You need to define a planning view for every sheet on the multisheet template. You have an option to copy settings from sheet to sheet (time, attributes, key figures, filters, and so on).

Choose the button to copy sheet settings.

Once you have defined your planning views, you can now upload your template using the Template Admin functionality (Template → Add). Other users will be able to use it at that point.

When a user creates a planning view based on a template, the system defaults settings from the template, such as time, key figure, and planning levels. The new planning view is based on the Microsoft Excel workbook of the template. Therefore, any items stored in the workbook, such as formatting, charts, or SAP IBP Formulas, are also part of the new planning view.

In one of the later units, we are reviewing the relative impact of various settings on the performance of SAP IBP Planning views. Regarding templates and passing the information to the user that will be using the template after the administrator created it, there is an easy way to work with filter options.

If an attribute description has been changed in the configuration of a web client, but the changed attribute description does not appear in the templates or your favorites in the Microsoft Excel add-in, try clearing the metadata cache.

Note

Clear metadata cache.

You can save planning views in the SAP IBP, add-in for Microsoft Excel as favorites to easily access them again in the future. When you create planning views from scratch or change planning views that you created from a template, you can save the planning views as favorites so that your settings aren't lost. You can share the favorite with other users.

Conditional Formatting

Conditional formatting works in the same way as the standard Conditional Formatting functionality of Microsoft Excel. Note that you must know the cell references used in the rule, when using the rule type Use a formula to determine which cells to format.

For the data to be conditionally formatted in a certain SAP IBP planning view and for that setting to persist, a conditional formatting rule must be created via the SAP IBP Formatting Sheet.

To do so, planners must navigate to the SAP IBP Formatting Sheet, select the key figure which needs to have conditional formatting in the Dimension Member/Property Formatting space, and then create a rule in the Microsoft Excel Home tab, using the Conditional Formatting menu.

In the most common use cases, a conditional formatting rule is created to highlight key figure values that are in a certain range or to highlight key figure values in time buckets where other key figure values (or possibly, local member values) are in a certain range. In the latter case, the formula is created in the SAP IBP Formatting Sheet, but the values are taken off the planning views. It is recommended to check the conditional formatting formula in the planning view, to ensure that correct cells are being referenced, and to determine how the formula must be anchored.

Note that conditional formatting has an impact on performance in the planning views.

Sorting in Planning Views

Sorting data in planning views is possible based on attribute values, key figure values, and members. The Microsoft Excel sorting function can also be used with some limitations.

Attribute-Based Sorting

In the planning view settings, you can assign a sorting rule to each attribute selected for the planning view.

In the drop-down menu next to the attribute, you can choose from the following sorting rules:

  • A-Z, for sorting in ascending order.
  • Z-A for sorting in descending order.
  • Custom sorting rules, either previously saved or created ad hoc.

Value-Based Sorting

You can sort the planning view by the values of a specific key figure in ascending or descending order. You might want to focus on the planning combinations that require your attention, so you want to see combinations with the highest or lowest key figure value at the top of your planning view. By sorting the data in your planning view based on the values of a key figure, you can focus on the top-selling products or products with low days of coverage.

It is possible to combine attribute-based sorting and value-based sorting.

Microsoft Excel Sorting

You can use the native Microsoft Excel sorting function in your planning view worksheets. To enable this function, in the SAP IBP ribbon, in the About group, choose Settings → Options. On the Formatting tab, select the Repeat Row headers checkbox.

Once you have enabled the native Microsoft Excel sorting, you can sort the values in a column of a planning view by using the Excel filter drop-down. Changed key figure values in the sorted planning view are recognized during saving.

Note the following points:
  • Using Microsoft Excel Sorting in conjunction with the local members in the row axis is not supported.
  • The use of the Microsoft Excel option Sort top to bottom is supported, but Sort left to right is not.
  • Do not deactivate the Microsoft Excel Filter function in the Data ribbon after having sorted the planning view.
  • Using Microsoft Excel native sorting in large planning views may have an impact on performance.

Adapt a Predelivered VBA Template for Own Template

Business Example

You are a member of the project team managing the implementation of SAP Integrated Business Planning for Supply Chain. You want to create a template to use with your planning area.

ExerciseStart Exercise

Note

When the data values for the exercise include ##, replace ## with the group number that had been provided to you.

Steps

  1. Use the connection that you previously created (connection SAP110).

    1. Open Microsoft Excel and choose the SAP IBP tab.

    2. Choose Log On Connection.

      Result

      The Logon dialog box appears.

    3. Select the SAP 110 connection you previously created.

    4. Choose Log On and log on to your planning area using the user name and password provided for you.

  2. Using the ChartVBA_Embedded template, create a planning view containing Consensus Demand Qty and other demand key figures for the next 12 months for your products for the Series2 product sub-family.

    1. In the Planning View section of the SAP IBP ribbon, select New View → From Template and, from the drop-down, select the ChartVBA_Embedded template.

    2. Choose Monthly Time Periods starting from the current period to 12 months in the future, for example, Feb ‘23 to Jan ‘24.

    3. Select Attributes and choose Product Sub-Family.

    4. Select Key Figures and choose the following key figures:

      • Consensus Demand Qty

      • Sales Fcst Qty

      • Marketing Fcst Qty

      • Choose Currency EUR
      • Choose Unit of Measure EA

    5. Select Filter and first choose the My Group Products## filter that you created in the previous exercise, then choose Add Attribute and choose Series 2 for the attribute Product Sub-Family.

    6. Choose OK to view the report.

    7. To adjust the graph, select Sales Forecast Qty and select the blue plus sign to the left of Series in the Chart header.

  3. Render the view on the second sheet, by populating data for Consensus Demand Qty and other demand key figures for the next 12 months for your products for Series2 product sub-family on a product level.

    1. On the second sheet position your cursor to the right of the "Start Here" indicator and choose New View → Without Template on Current Sheet.

      Result

      The Create Planning View dialog box appears.

      Note

      The action of creating a New View had established this worksheet as an SAP IBP worksheet and now you have an option to use SAP IBP add-in functionality to populate the sheet

    2. Select Attributes and choose Product ID.

    3. Choose Copy Settings on the top right of the dialog box and copy the following settings from Planning View 1 to Planning View 2:

      • Time

      • Key Figures

      • Conversions
      • Filter

    4. Choose OK to view the report.

    5. To hide the graph, click the switch in cell A3.

  4. Copy the second sheet you just worked on and introduce value-based sorting for a Consensus Demand key figure.

    1. On the second sheet select New View → Copy Current Sheet.

      Result

      New worksheet appears.

      Note

      You can change the name to Planning View 3

    2. Choose Edit View → Edit Planning View and, on the Filter tab, remove the filter on the attribute Product Sub-Family.

    3. Choose OK and populate the data into your view. You should see the key figure values associated with three products that appear in the A-Z default sequence (T-F2## - T-F4##).

    4. Choose Edit View → Edit Planning View and, on the Key Figures tab, on the bottom right of the form, select the dialog for configuration of the value-based sorting.

      You will see the new entry form Manage Sorting Rules - Value-based Sorting appear.

    5. Select Consensus Demand key figure, periodicity of a Month, Rolling setting, and current and next month.

    6. Select Add new sorting (plus sign), and name your sorting rule Consensus Demand Sorting ##.

    7. Choose Add.

    8. Choose OK, and OK again, on the main Edit Planning View screen.

    9. Observe the order of the products in the planing view.

  5. Introduce sorting for a Product ID attribute.

    1. Choose Edit View → Edit Planning View and, on the Key Figures tab, to remove the value-based filter Consensus Demand Sorting ##, select none.

    2. Choose OK and populate the data into your view, you should see key figure values associated with three products that appear in the A-Z default sequence (T-F2## - T-F4##).

    3. Choose Edit View → Edit Current View and, on the Attribute tab, to reverse the sorting, choose Z-A in the sorting space.

      Choosing a sorting option.

    4. Choose OK and populate the data into your view and observe the changed order for your products.

    5. Choose again Edit View → Edit Current View and, on the Attribute tab, select Open dialog for customizing sorting (three dots).

    6. Select products: T-F3##, T-F2##, and T-F4## in that order and then choose Add. Then, in the Name field, enter My Add-hoc sorting and choose Add.

      Note

      An element Others is present in the selected items. That entry incorporates the values that you did not specifically place in the sorting sequence. You can also choose to sort the attribute values that are part of the Others category, but only in an ascending (A-Z) or descending (Z-A) order. To change that setting, use the toggle boxes on top next to the search bar.

    7. Choose OK.

    8. Choose OK and populate the data into your view and observe the changed order for your products.

    9. In the planning view, position your cursor on the first Product ID (that should be attribute value T-F3##) and access the context menu by right-clicking.

    10. Using the Sorting entry of the context menu you can change the sorting back to the Z-A one, or the standard A-Z one.

  6. Using your new planning view, create a template with Consensus Demand 2 levels ## as the template name.

    1. Optional suggestion: On real-life customer projects, where performance of the templates is very important, use the sequence Edit View → Edit Planning View → Edit Template Settings > Copy as Suggestion .

    2. On the SAP IBP tab, choose Templates → Add to create a new template.

      Result

      The Add Template dialog box appears.

    3. In the Name field, enter Consensus Demand 2 levels ## and choose Add.

    4. Choose Refresh.

  7. Save your new planning view as a favorite called Consensus Demand 2 levels ##.

    1. Choose Favorites → Add to add the newly created template as a favorite.

      Result

      The Add Favorite dialog box appears.

    2. In the Name field, enter Consensus Demand 2 levels ## and choose Add.

      Note

      If you see a warning coming about worksheets with changed or not current data, choose Yes.

Create a Conditional Formatting Rule

ExerciseStart Exercise

Steps

  1. Using the favorite from the previous exercise, create a conditional formatting rule highlighting Consensus Demand Qty values that are higher than a threshold value of 20,000.

    1. In the favorite Consensus Demand 2 levels ##, make sure that you are working with the first sheet, where we are displaying three key figures in monthly buckets.

    2. On the first sheet, choose Edit View → Edit Planning View.

      Result

      The Create Planning View dialog box appears.

    3. Choose WeeklyTime Periods starting from the current period to 52 weeks in the future.

    4. Leave all other settings the same.

    5. Choose OK to view the report.

    6. Choose Edit View → View Formats and transition to the IBPFormattingSheet.

    7. Under the Dimension Member/Property Formatting section in the Row area, choose Add Member/Property.

    8. Choose Add Condition.

    9. Choose Key Figures under Dimension Selection. Then choose Consensus Demand under Member Selection, and then choose OK.

    10. Choose OK again. An additional line should now be added to the SAP IBP Formatting Sheet.

    11. In that new line, in the Data column, or F column, transition to the Microsoft Excel Home tab, and choose Conditional Formatting → New Rule.

    12. Select the rule type Format only cells that contain and in the rule select Greater than or Equal To.

    13. Enter the value 20,000 and set a format selecting the fill in your choice of color.

    14. Choose OK.

    15. In the Use column of H Column, double-click and deselect All, and select ConditionalFormatting.

    16. Return to your planning view sheet and refresh the data.

    17. Observe the highlighted cells and validate that the rule is working as intended.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn