Executive Summary: Creative and appropriate use of the EPM Client can overcome complex formatting issues that some have difficulty with in converting existing schedules to forms. Here is a generic example to get you thinking about how the tool can be used to solve what seem to be complex issues. EPM 10 client software is much more robust than first glance shows to those that are coming from a 7.5 environment. We can solve complex client formatting issues.
EPM 10 does convert some reports and input schedules to forms in the new version from the older versions. However, many have noted that complex forms may not convert. This is certainly true. However, some are noting that forms and formats simply cannot be duplicated in cases where complex formatting in a customer report or input form is required.
For complex formatting in cell look and feel and locking needs it is difficult to achieve the objective with just one EPM Format Sheet. Did you know that one purpose of the tool is to allow for greater flexibility in reporting? Did you know that it is possible to have two reports or forms operating in the same sheet with a common axis?
In this simple demonstration I will give you the food for thought that you might need to get over a very simple hump that you will find as more and more clients convert to EPM 10 from older versions and need complex formatting control. Below you see two reports combined with one shared axis. The Account Row Axis is shared. The reports are the ones by the columns, and report one is the blue column and report two is the light orange set of columns.
The Report Goal Different Format and Different Locks
One thing some have noted is that they want a custom lock message. This is achieved by using the LOCK feature on the QtrFormat sheet for the Blue Column data.
Note All and Lock. You Must Select LOCK in the Use Drop Down
The other report has its own formatting of different colors or other features but does not lock the values so data input can proceed.
By the above sheets we accomplish more complex formatting using the native features of the tool including multiple combined reports using a common axis and using more than one format sheet. This is similar to EVDRE and formatting of the past, but many do not see this solution. In this way we can avoid “hard coded” reports and formats using the excel process.
In order to achieve proper refresh and still lock the sheet, simple macro language that is familiar is required. So we can continue to use the native tool GUI rather than inserting refresh buttons or send buttons, we use these macros to control protection of the sheet.
These are native “user exits” if you will for EPM 10, and have been used by many for years. The new tool is a bit less forgiving during refresh on protected sheets.
The above form of protecting the sheet is not absolutely required. If one wants to use an alternative approach the below process alone is sufficient and may allow users to select more areas of the sheet particularly if expand and collapse are used by the reporting community.
Either way one might want or need a custom lock message for users if they click into a cell that has the locked property set to true.
The following VB inserted into the worksheet functions is appropriate for this purpose.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
MsgBox “This Cell is Locked For Data Input”
Application.EnableEvents = True
This outline of a basic method is intended to be food for thought. Remember the tool does work and there is a way for most things we have all learned to do in prior versions. You need to peel the onion and come up with the ideas. This is one way that keeps you in the native toolset and has few limitations. More than two reports with a common axis as one presentation is very possible. We have used this method for up to 5 common axis reports needing different formatting.
We have found that this process gives us the equivalent features we have always enjoyed without having to give up on the dynamic nature of reporting or inputting data.