September 9–11, 2013 Anaheim, California
Learning Points
Overview of basic features of EPM Add-in for MS Office
Advanced features:
Multi Source reports
Advanced Cell Based Formulas
Ribbon Extension
Some tips and Tricks
Driver based input formulas
Data validation
Rolling 12 months
Learning Points
Overview of basic features of EPM Add-in for MS Office
Advanced features:
Multi Source reports
Advanced Cell Based Formulas
Ribbon Extension
Some tips and Tricks
Driver based formulas
Data validation
Rolling 12 months
Formatting using named ranges
More resources and Documentation
Creating EPM Connections:
Local vs. Planning & Consolidation type
Local connection type is for ODBO connections (e.g. basic BW cubes). Only used for data retrieval. Data Manager features
Custom members can be created with MDX
Planning & Consolidation type is for Web Service connections Used for BPC 10 MS and BPC 10 NW.
Allows data input, and the use of the Data Manager ribbon, but does not support the creation of custom members
Manual vs. Automatic connections
Automatically created connections, accessed from the BPC web portal, are not stored in the EPM – Connection Manager window
Manual connections are stored in an XML file
Once logged into the EPM data source, click the New Report
button or drag & drop dimensions from the dimension list in the EPM Pane to the row axis and columns axis.
Once your report is generated, you can access its options (tabs) by
clicking the Edit Report button. The Report Editor opens with a list of options and tabs.
By selecting Use as Input Form option, the Save Data button will become available, when coming back to your report
.
Building reports and input forms 1 of 2
Click on the Account dimension in row axis on the EPM Pane in order to define the desired expansion. The Member Selector opens and you can now define your Account expansion
Select account member(s), and in the Selection Relationship option from drop-down (e.g. Member and Descendants) , and move it to the Selected Members window, by clicking the right arrow button
You report now displays
NetIncome and all its descendants for the Account dimension.
For each report, you can decide to apply a formatting by clicking the View Formats
button on the EPM tab. A new sheet appears in your workbook (called EPMFormattingSheet). Apply some formatting to the desired sections. Be aware of the
priority (read from the top to the bottom).
Return to Sheet1 worksheet and click the Edit Report button. Under the Options tab click the Apply
Dynamic Formatting checkbox and choose EPMFormattingSheet in the drop-down box. Back to your report, the formatting is applied according to the
EPMFormattingSheet definition.
Basic formatting 2 of 2
Learning Points
Overview of basic features of EPM Add-in for MS Office
Advanced features:
Multi Source reports
Advanced Cell Based Formulas
Ribbon Extension
Some tips and Tricks
Driver based formulas
Data validation
Rolling 12 months
Formatting using named ranges
More resources and Documentation
Using the EPM Add-in, you can display two reports belonging to different data sources on the same worksheet
Report 1 is linked to a Planning and Consolidation connection.
Report 2 is linked to a native BW cube through ODBO driver connection.
Note that the Active Connection drop-down in the EPM Pane references all data sources which you are
simultaneously connected to. And you can add new connections directly from here choosing the Select Another
connection link:
Multi Source Reports 1 of 2
1
When displaying
several reports on the same spreadsheet, you can turn on the Axis Sharing Feature.
Report 1 is linked to a Planning and
Consolidation
connection, and shares its row axis with Report 2.
Report 2 is linked to the same Planning and Consolidation
connection, and displays another
Multi Source Reports 2 of 2
EPMCopyRange
This function enables you to add and repeat the content of a range of cells until the bottom (columns) or the right side (rows) of a report is reached. The range is added to the data grid and to the shift zones (if there are ones), starting after the row axis and below the column axis.
Typically, this function is very useful when you use hidden columns between your row headers and your data range (what we call shifting columns).
This function is not the official successor for Before Range/After Range. The new method for building Before and After Range would be to either use the Sorting & Grouping feature in the axis definition, or use the new EPMInsertCellsBeforeAfterBlock directly in the report.
Here, the formula is used to copy a formatting range down the rows.
It is applied for the shift
column (column B), as well as for the data grid.
The source definition of the formats to apply is placed in cells A2:D2, above the report.
In cells A2:D2, you can also add formulas to retrieve
comments instead of data for example.
Advanced cell-based formulas 2 of 3
=EPMDimensionOverride("000;001",“TIME",B20)
EPMDimensionOverride formula offers the ability to reference former MemberSet cell , so that your expansion in EPM Add-in will retrieve the same result. Former MemberSet parameters can be used (MEMBERS, BAS,SELF,LDEP(n), etc…)
In this example, the formula will override the member selection for the TIME
dimension in reports 000 and 001. The dimension member selection performed in the Member Selector is replaced by value in cell B20.
Advanced cell-based formulas 3 of 3
Ribbon extension…
Is a way to customize your EPM standard ribbon (when connected to any EPM Data sources)
Provides an already-customized client interface that better matches customer front-end needs.
How it works?
You will create and place a newly generated dll in the folder which contains all EPM add-in installation files (usually C:\Program Files (x86)\SAP
BusinessObjects\EPM Add-In).
The extensions (new dll) inside will be automatically detected by the EPM Add-in each time you log-on to your EPM datasource via the Log On button.
Ribbon Extension 2 of 3
How to create it?
Create an new Class Library project with Visual Studio 2008 or 2010. Implement all methods which are defined in the interface
IEpmRibbonExtensibility.
Build your project, and name your generated dll with the name ending with RibbonExtension.dll (for example: MegaEliteRibbonExtension.dll)
Create as many extension as you would like and define the priority order for each and Copy this generated dll and paste it into the EPM Add-in installation files.
Ribbon Extension 3 of 3
Result
Before enabling ribbon extension (standard ribbon)
Learning Points
Overview of basic features of EPM Add-in for MS Office
Advanced features:
Multi Source reports
Advanced Cell Based Formulas
Ribbon Extension
Some tips and Tricks
Driver based formulas
Data validation
Rolling 12 months
Driver based formulas allow you to define the drivers for your data input Driver based formulas allow you to save the results based on user specified calculations based on the specified driver
The results are
automatically refreshed when the drivers for the results change
Local members need to be defined to enable driver based formulas
Data Validation functionality of EPM Add-in allows to validate data input based on defined rules/formulas Data Validation in EPM Add-in is different than BPC Validations. BPC Validation.
BPC Validations can use validation Badi and can validate data from data loading whereas Data Validation of EPM Add-in is applicable to data input through EPM Add-in and can use
formulas in EPM Add-in
Data Validation in EPM Add-in can be used in addition to Excel Validation
Data Validation 1 of 2
Features available in Data Validation functionality:
Creation of custom formulas
Input message to guide the users to enter valid data
Error message to inform users about the reasons for invalid data entry
Input schedule with rolling 12 months allow updating display of actual and plan periods dynamically
For doing this, start by
creating a property in time dimension for the closed periods and filling it for closed dim members
While creating the report, select context and
member only instead of member and descendants Create a formula showing actual for the closed
Formatting with named ranges allow you to format
specific ranges in the report with custom formatting We can create named ranges for any dimension with any user specified dimension members The ranges show up in the property
when we apply formatting by property
More Resources and documentation
How to guide - SSO implementation via client certificates for BPC 10 NW.
http://wiki.sdn.sap.com/wiki/display/BPX/Enterprise+Perfor
mance+Management+%28EPM%29+How-to+Guides
EPM Add-in user guide and installation guide
