<img src="https://certify.alexametrics.com/atrk.gif?account=3HHNq1DlQy20Y8" style="display:none" height="1" width="1" alt="">

SAP Analytics
Thought Leadership

Useful Tips of Advanced Functionality in EPM10 Dynamic Formatting

Posted by
Michael Sheard
Michael Sheard
on Wed, Sep 18, 2013 @ 07:09 AM


Column5 is the leading edge of Enterprise Performance Management and strives to provide technical tools which enable personal growth to benefit you and your organization. This blog will provide users who are familiar with dynamic formatting tips and how to use the full functionality to refine formatting.

Applying formats to “Specific Member/Property” …

The “Dimension Member/Property Formatting” section of the format sheet includes the ability to apply formats to specific members and properties.  They are further down the list than the row and column defaults and therefore take precedence over them.  The use of these to apply formats where the defaults are departed from is an important function.  They need to be applied in the correct order, with those taking precedence lower down.

Watch our EPM Add-in Tips & Tricks Webcast

 Restricting format “Use” …

To the right of each of the Data and Label columns is a “Use” column to specify the extent to which the format is applied.  The default is “All” which means that the entire format of the cell is applied.

A common problem is that the formatting applied to rows and columns clash with each other, for example a column may have vertical borders applied to it and a summary row for which a bold font (say) is required crosses over it.  If the latter (row) format is applied further down the format list using “All”, it will remove the vertical border as well as applying the bold font.

Issues such as the above can be avoided to restricting the format usage towards the bottom of the list.  In the above example, the use can be restricted to only apply the bold font, thus not removing the borders.  Other examples of this functionality are applying number formats, font colors or patterns.

Use of conditional formatting …

This does work in EPM10 but is not is widely required as it was with EVDRE that preceded it.  It can be applied to both data and labels.

The first tip is to avoid its use as far as possible in order to avoid the additional complexity that it introduces.  The functionality to apply formatting to specific members and properties (such as hierarchy levels and parents) can usually be used to avoid the need for a conditional format.

The easiest way to apply a conditional format dynamically is to get it working correctly in the report and then copy a cell into the relevant area of the dynamic formatting sheet.  The final step is to refresh the report and check that the format is applied and works correctly.

When formatting goes wrong …

The main causes are the incorrect specification being entered for a specific area or, more commonly, a correct specification being overridden by another format further down the list.  When this happens it is necessary to review the data area that is not formatting correctly and go down the EPM formatting sheet from top to bottom to identify formatting commands that apply to that area.  As required, add additional formatting specifications and/or change the formatting order so that the required format takes precedence over others.

Reporting eBook v3-01.png

Related Articles: 

Through EPM Academy, your organization will gain valuable insights on how to speed up acceptance and adoption, reduce roll out time and costs, and improve project visibility and organizational perception. Learn all about our upcoming BPC Logic Boot Camp & Reporting Workshops! 

Topics: Excel, Training, Performance


Recent Posts

Posts by Topic

see all

3  S T E P S

To Enhance Your EPM Performance:

 1.Get Your Ultimate Guide to Improving SAP EPM Performance

ebook 2

 Learn what could be contributing to your poor performance and how to diagnose common problems. Get tips that will empower you and your team to improve the  performance  of your system in order to get the most out of your SAP EPM Investment.

Get the e-Book


2.  Test Your BPC Performance 

Benchmark 2

Get a BPC performance report card and custom report to identify performance issues you may not even know you have.

Test my BPC


  3. Get Your EPM Assessment  


Our assessment delivers a complete, best practice roadmap for you to follow.  We’ll work together to assess what you need to pivot your financial forecasting, planning, reporting and analysis to handle the compressed requirements from the volatile COVID-19 environment. 

New Assessment