step by step guide to formatting the sap bpc epm sheet
DESCRIPTION
EPM Formatting Sheet can be used to format your Reports and Input Forms. There are various options for formatting. You can do simple formatting, lock the cells that are not input ready, use the format by property using multiple criteria, or use a new cell-based formula delivered with the EPM add-in to override the data range formatting. Dynamic formatting is defined in a dedicated sheet. Formats can be applied to levels of a hierarchy, types of members, odd/even rows, or columns and page axis dimensions. Since the EPM Formatting Sheet is an excel sheet, it can be copied and changed to be applied to other reports, worksheets. There are various sections on the EPM Formatting Sheet • Hierarchy Level Formatting • Dimension Member/Property Formatting • Row and Column Banding • Page Axis FormattingTRANSCRIPT
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 1 01/20/2014
SAP BPC NW 10.0 | KNOWLEDGEBASE
SAP BPC EPM Formatting Sheet
A Step By Step Guide for Formatting The EPM Sheet
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 2 01/20/2014
Table of Contents
1. Overview: ....................................................................... Error! Bookmark not defined.
1.1. BPC Environment :Formatting the Input form/Report ............................................... 3
2.0 Dynamic Formatting ................................................................................................. 8
2.1 Common Settings ...................................................................................................... 8
2.2 Heirarchy Formatting ................................................................................................ 8
3.0 Dimension Member and Property ............................................................................ 13
4.0 Row and Column Banding ....................................................................................... 21
5.0 Page Axis Formatting ............................................... Error! Bookmark not defined.23
6.0 Cell Protection ......................................................................................................... 24
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 3 01/20/2014
EPM Formatting Sheet
Overview:
EPM Formatting Sheet can be used to format your Reports and Input Forms. There are
various options for formatting.
You can do simple formatting, lock the cells that are not input ready, use the format by
property using multiple criteria, or use a new cell-based formula delivered with the EPM
add-in to override the data range formatting.
BPC Environment – Formatting the Input form/Report:
Steps to create EPM Formatting:
1.1. Login to the BPC System as shown below
1.2 Launch the EPM ADD-in EXCEL
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 4 01/20/2014
1.2 Choose the required model created by you.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 5 01/20/2014
1.3 Go to EPM and Click on OPEN and go to “Open Server Root Folder”
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 6 01/20/2014
1.4 Open the Input Form which is saved in the Server Folder.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 7 01/20/2014
1.5 Click on EPM Formatting sheet at the bottom of the EPM ADD-in Excel Sheet.
Apply format
You can define format
for Data or Header
Apply Formatting Switch Row or
Column Priority
Define Format
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 8 01/20/2014
2.0 Dynamic formatting is defined in a dedicated sheet. Formats can be applied to levels of a
hierarchy, types of members, odd/even rows, or columns and page axis dimensions. Since
the EPM Formatting Sheet is an excel sheet, it can be copied and changed to be applied to
other reports, worksheets.
There are various sections on the EPM Formatting Sheet
• Hierarchy Level Formatting
• Dimension Member/Property Formatting
• Row and Column Banding
• Page Axis Formatting
2.1 There are certain settings that are common to all sections:
• Checkboxes
• Priority to Column or Row Format
• Data and Use Columns/Cells
• Header and Use Columns/Cells
Actions relevant to several formatting sections
Checkboxes: Enable you to specify the formatting sections, or formatting items inside a
section that you want to apply to the reports.
Priority to Column or row format: Enable you to specify which one of the defined formats
for row or columns will be applied first in case of conflicts. For example, if you select Priority
to Column Format the Column section will appear below the Row Section, and the
precedence rules apply.
Formatting: Allows you to use the standard Microsoft Office Excel cell formatting features in
the Data and Header Columns to define the formatting. Also gives you the ability to directly
enter the format settings in the Use cell. By double clicking the Use cell, you will open the
EPM Formatting Sheet - Cell Format window which will allow you to override certain format
settings.
Cell Protection: However, you can unlock cells or lock cells as necessary. Right-click on one
or more cells, and from the context menu select EPM → Unlock Selected Cells. When you
are finished, if you'd like, you can re-lock by choosing EPM → Lock Selected Cells.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 9 01/20/2014
2.2 Hierarchy Level Formatting
The formats that can be applied in the hierarchy section are Default, Base Level and
Formatting on Specific Level.
• Base Level, Specific Level, and Default formats
• Structure Levels versus Relative Levels
• Levels can be added or deleted
• For Nested Dimensions, can choose Inner or Outer Dimension
Base Level Format: Specific format for base level members (not calculated)
Formatting on Specific Level: When applying formatting to a specific level, you have your
choice or Structure Levels or Relative Levels. Structure levels refer to the levels of the
hierarchy as defined in the source. Relative levels refer to the levels displayed in the report.
For Instance, Quarter is the 2nd level of the hierarchy as defined in the source connection,
but it is the first level being displayed in the report. If you select the Relative Levels option, a
checkbox appears Start formatting from the lower level displayed. This checkbox enables
you to invert the level order; the lowest level is displayed in first position.
If a row or column axis contains more than one dimension, you can specify which dimension
you want the defined format to be applied to. The Inner dimension is the last dimension,
and the outer dimension is the first dimension in the axis.
Default Format: Default format for hierarchy level.
2.2.1 How to Format for Hierarchies
From the EPM Ribbon, choose View Formats. The EPM Formatting Sheet will open in your
Workbook. For the Hierarchy Level Formatting section, choose the checkbox to apply the
formatting section. Choose either the radio button for Priority to Column Format or Priority
to Row Format. .
Note: The lowest set of formats in the section takes precedence. For the formats you want
to apply, choose the checkbox to the left of Apply.
Note: The precedence is Base Level, Formatting on Specific Level, then Default.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 10 01/20/2014
2.2.2 Default Format
Goto Trend by Entity Sheet and click on EPM and do “Refresh”
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 11 01/20/2014
2.2.3 Base Level Format
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 12 01/20/2014
2.2.4 Formatting on Specific Level
If you are using Formatting on Specific Level, choose either the Structure Levels radio button
or the Relative Levels. If you choose Relative Levels, you can also decide if you want the
formatting to start from the lowest level displayed by selecting the checkbox Start
formatting from the lowest level displayed. Additionally, if you are using Formatting on
Specific Level, you can choose either the Add Level button or the Remove Last Level button
as necessary.
For the formats you are applying, place your cursor on either the 10000 field, if you are
attempting to change the Data formats, and/or the Label field if you are attempting to
format Headers .The format palette will open as well as the Excel Context Menu. Set your
formats
either from the palette or by choosing Format Cells to select your formats there.
If you have more then one dimension in an axis, you can choose where the formats are
applied by selecting the radio button Inner Dimension or Outer Dimension to the left of the
Apply Format To: area.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 13 01/20/2014
Return to the sheet where your report is and be sure the Apply Dynamic Formatting
checkbox is selected. This can be found on either the EPM Ribbon→ Op8ons → Sheet
Options Formatting tab (applies to the worksheet) or from EPM - Report Editor (EPM Ribbon
→ Edit Report ) Op8ons tab. (applies to the report)
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 14 01/20/2014
Goto Trend by Entity Sheet on EPM and do “Refresh “
Lowest Level 2
Lowest Level
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 15 01/20/2014
3.0 Dimension Member and Property Formatting
Formatting can be applied to:
• Custom members
• Calculated Members
• Local Members
• Inputable members (BPC only)
• Changed members (BPC only)
• Specific members
• Property of a member
Lowest Level Lowest Level 1 Lowest Level 2
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 16 01/20/2014
3.1 Custom Member/Calculated Members
Go to EPM Formatting Sheet and select the calculated member radio button as shown
below. In the below case, all the entities in Asia Pacific region are grouped under JNJ APA
and the same is highlighted as it the total of the all the entities coming under it.
Goto Trend by Entity Sheet and click on EPM and Refresh the existing Worksheet.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 17 01/20/2014
3.2 Inputtable Member Default Format
Goto Dimension Member/Property Formatting and apply the Inputable Member Default
Format. The Format will be applied only to the Inputtable data and Header.
Calculated Member
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 18 01/20/2014
Goto Trend by Entity and Click Refresh in the EPM Section on the top of the EPM Add-in
Excel Sheet.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 19 01/20/2014
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 20 01/20/2014
3.3 Changed Member Default Format
Goto the Dimension Member/Property Formatting and go to Changed Member Default
Format.Only the inputtable forms which are changed after the initial load with be
highlighted.
GOTO Trend by Entity sheet and click EPM and Refresh the screen.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 21 01/20/2014
3.5 Formatting on Specific Member/Property: Dynamic Formats can be applied to the
current worksheet or a specific report.
• The current worksheet
• A specific Report
Note: If you modify a dynamic format, which has already been applied to a worksheet, you
will need to refresh the report to see the dynamic Formatting template by report.
• You can have multiple formatting sheets in your Workbook Each Report/Input Schedule
sheet can point to a different Formatting Sheet .We can apply dynamic format on the basis
of the specific Member
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 22 01/20/2014
Goto Trend by Entity and click on EPM and refresh the sheet.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 23 01/20/2014
4.0 Row and Column Banding:
Row and Column banding allows you to choose one or two alternating formats as per the
requirement. The format can be applied to Data or Headers and Rows or Columns. Go to
EPM formatting sheet and go to Row and column Banding and apply the format.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 24 01/20/2014
Go to Trend by Entity and click on EPM and refresh the Sheet.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 25 01/20/2014
5.0 Page Axis Formatting : Formatting for all dimensions of the Page Axis
Go to Trend by Entity and click on EPM and refresh the Sheet.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 26 01/20/2014
6.0 Cell Protection: One can unlock cells or lock cells as necessary. Right-click on one or
more cells, and from the context menu select EPM → Unlock Selected Cells. When you are
finished, if you'd like, you can re-lock by choosing EPM →Lock Selected Cells.
Goto to EPM Formatting Sheet and then go to Row and column Banding. Apply the Cell
Protection Option to Column Data as it is Actual and cannot be changed.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 27 01/20/2014
Goto Sheet Option on the EPM section and click on Protection. Select from the options given
below or go to the EPM formatting section and right click data validation. Enter the
password and protect the cell.
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 28 01/20/2014
______________________________________________________________
Author: Chief Architect:
Roshani Kshirsagar Jothi Periasamy
Data Science Solution Consultant SAP HANA/EPM/BI/EIM Community Leader
[email protected] SAP BPC/BOBJ/BI/MDM Domain Champion
[email protected], (916)-296-0228 - Cell
SAP EPM/HANA Community Service
CloneSkills, Inc., http://www.CloneSkills.com , [email protected] , 1.800.836.8959, Page - 29 01/20/2014
Thank You!
Jothi Periasamy
Chief Data Scientist
Experfy & Harvard Innovation Lab
Harvard University