Hyperion Planning Input Forms
Hyperion Planning Input Forms
Hyperion Planning Input Forms
Jake Turrell
Background
16+ Years Hyperion Implementation Experience Certified in both Planning and Essbase Prior Practice Lead at Hyperion Partner Firm Co-Editor of the book Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals
Over 50 years of expert experience between the covers. Advanced content written so that developers at all levels will gain assistance and insight. Chock full of experience proven best practices. Collaboratively written by a team of respected and sought after Essbase developers.
US-Analytics is an industry leading professional services firm focused on helping clients successfully establish and maintain long term Business Intelligence (BI) and Enterprise Performance Management (EPM) applications and programs.
BI and EPM Strategy and Processes Custom and packaged BI and EPM Applications Oracle Infrastructure Managed Services and Hosting For over a decade, market leading companies have trusted US-Analytics to solve complex business problems, drive managerial excellence, and deliver operational agility. Learn more at Booth #107 or www.us-analytics.com
Planning 11.1.2.2 (ADF Interface) Major League Baseball Statistics (Pitching & Hitting) 50+ Stat Accounts FY10 FY13
Disclaimer
The Planning and Essbase objects used in this demo are available for free. They are unsupported.
They are available for learning purposes only and may include incomplete and/or inaccurate data.
Agenda
Organizing Data Limiting Data Creating Interconnected Forms Creating Data Validations Creating Row & Column Formulas Creating Embedded Charts Predictive Planning
ORGANIZING DATA
Organizing Data
Good Layout = Easy Consumption Forms vs. Bulk Data Interfaces White Space Borders Composite Forms
Dont present users with a massive wall of cells. Hide irrelevant rows and columns.
Use the Show Separator feature to give users context regarding different subject areas.
Use blank rows to introduce spacing into forms. Use Composite Forms to present different (but related) subject areas on the same form. Use Data Validation Rules that always evaluate to True for additional formatting options.
To introduce a blank line into a form add a Formula Row with no formula or label. Certain older versions of Planning require a label, otherwise the dimension name will be displayed. In these instances, use . as a label.
Using Borders
Show Separator does not work with certain unpatched versions of ADF.
Composite Forms
Pros
Under the right circumstances, composite forms bring together multiple subject areas into a single view.
Cons
Formatting and space allocation can be unpredictable, especially when users have different display resolutions. In Smart View, composite forms render on separate Excel tabs.
Data Validations are good for more than just validating data!
Developers can create validations that ALWAYS evaluate to True, simply to format cells in a form.
LIMITING DATA
Set in the Grid Properties, Row Properties or Column Properties. Prevents blank rows or columns from being displayed.
Set in the Grid Properties. Does nothing, unless used with Suppress Missing Data. Typically good for forms where most rows will have no data. Typically bad for forms where most rows have data. Can cause issues displaying certain dynamic members and attributes.
Demo / Examples
Limiting Data
Pros
Can keep irrelevant data off the form. Can make forms faster (when used correctly). Can make forms easier to consume.
Cons
Planning does not support Conditional suppression in forms. What happens when users want to plan for a member combination that currently has no data? (Hint . . . Take the Form Ad-Hoc)
INTERCONNECTING FORMS
One aspect of building a good form is providing multiple navigation routes to and from that form.
Create right-click menus that take users from one form to another, while passing context.
This is especially useful when moving from a summary form to a detail form.
Menus can be used to do much more than navigate to another form. They can:
Creating Menus
Add menu items that open forms, launch business rules, manage approvals, open URLs, etc.
Assign the menu to a Required Parameter. This simply tells Planning where the user must right-click in order to open the menu. Add the menu to a form in the forms Other Options.
Demo / Examples
Menus
User Variables
Users can set User Variables that limit the data presented on a form.
Open the Planning application. Select the menu options Administration, Manage, User Variables. Click Add.
In the form layout, select the variable from the Variables tab.
Users open the Planning application and select the menu options File, Preferences. **
Users must select the Planning icon, then the User Variable Options tab.
The documentation is not clear. Use Context is a variable setting. Enable Dynamic Variables is a form setting. Both options allow users to change variable values on the fly, but in different ways. Both options can be used at the same time.
Use Context variables are set when the user right-clicks on one form to navigate to another form (and passes context). Use Context variables can not be set in the user preferences. Enable Dynamic variables can be modified directly in a form. Enable Dynamic variables can be set in the user preferences. A variable can leverage both settings.
Demo / Examples
User Variables
If a user does not have read or write access to a member, it will be suppressed on the form.
Usefulness of this feature is limited, because users often have read access to more members than those to which they can write. There is no easy way to limit members in a Page list box, row or column based only on write access. **
DATA VALIDATIONS
Developers can create soft validations that do nothing but present a message.
Developers can create hard validations that prevent users from promoting their data or alter the promotion path. Developers can create data validations that ALWAYS evaluate to true simply to introduce some formatting into their forms.
Evaluate conditions in an individual cell or range of cells. Evaluate conditions in a design-time cell, for example a cell defined in the form with a function that returns multiple individual cells. Evaluate conditions in a column or row.
Evaluate cells with a specific version type. Evaluate cells with a specific variance reporting type.
Evaluate cells that reference members of a dimension with a specific user defined attribute.
Evaluate cells that reference members with a specific attribute member association. . . . . format cells that meet these criteria.
Demo / Examples
Data Validations
Data Validations
Pros
Theyre very flexible and support many conditions. They integrate with Approvals. Unlike custom JavaScript, they work in Smart View. Unlike custom JavaScript, they dont require programming skills.
Unlike custom JavaScript, they dont break every time you upgrade.
Data Validations
Cons
Watch out for floating point decimal errors. Reference a range of numbers rather than an explicit number. Cannot easily exclude non-editable cells. They dont actually prevent data from being saved (yet). A data validation in one form cannot reference another form. Be careful using red cells with data validations if your users format negative values as red in their user preferences.
In the Form Layout, right-click a row or column and select Insert Formula Row (or Column).
Abs Average
AverageA
Count CountA Difference Eval
Example Parameters
Sum(row[2]) row[2].Sum
Sum(column[A], column[C])
IfThen((IsMissing([A]) AND [B] > 0), 0, Eval([A] / [B]))
Demo / Examples
Formula Rows & Columns
Pros
Can help with database retrieval performance. Can help resolve database calculation order issues. Can save having to run a business rule with the save of a form. Good for one-off calcs that dont require database development. The function Pi is available for anyone who wants to forecast the circumference of a circle or is to lazy to type some numbers into a formula . . .
Cons
Does not handle divide-by-zero issues gracefully. No explicit function to return #Missing. This is a challenge when suppressing missing rows and you have a column formula. Formula columns are suppressed entirely if a label does not exist in the first row heading. Limited control over in-sheet calc order when mixing Row AND Column formulas in the same form. The Pi function is irrational . . . what if I need 16 digits???
EMBEDDED CHARTS
Demo / Examples
Embedded Charts
Pros
Cons
Grid Diagnostics
To monitor the performance of forms, Planning includes Grid Diagnostics. (as of version 11.1.2.2) Select the menu options Tools, Diagnostics, Grids. Select the Run Diagnostics button. Select the forms to review. Press the Run Diagnostics button. Click the chart for individual form stats. Experiment with different chart options.
Demo / Examples
Grid Diagnostics
To change this behavior, select the menu options Administration, Application, Properties.
Add the setting DATA_GRID_CACHE_SIZE and give it a value. Save changes and restart the Planning server.
Place the property in the Application Properties to only affect a single application.
When using the ADF interface, Planning will return the form in chunks of 25 rows and 17 columns.
When the user scrolls beyond this initial chunk the application will fetch 25 more rows and 17 more columns. Administrators can change this threshold by adjusting an Application Property called GRID_PARTIAL_FETCH_SIZE.
Property requires two values (row & column setting) separated by a comma.
Affects all forms and all users in a given application. Only available starting with 11.1.2.2 patch 303. Patch readme file makes no mention of a related System Property.
PREDICTIVE PLANNING
Questions?