How to add a Comments column to Hyperion Planning Webforms and Reports
I know 2 options to let users enter comments in a Planning Webform:
- Use the Comment Function available with Planning
- Create a Comment member in the Period dimension
The first option is out of the box but is very limited and not easy to report on. The second option is actually relatively easy to implement and also very flexible, this blog post is explaining how to implement it in 4 steps.
Create a “Comments” member in the Period dimension
The first thing to do is to create a member to capture comments. You can use any dimension but I found out that the Period dimension is the best choice for the following reasons:
- When you have periods in columns in a form or report, you can have comments in the last column: it allows users to enter comments about the data they entered for a specific row, as they would do with Microsoft Excel.
- It allows comparison of the comments posted on the different Budget versions, or Forecasts.
Simply add a new member with the Data Type Text in the Period dimension in Planning:
Your Period dimension should look like something similar to the image below (I am using the Vision Application):
Verify the Evaluation Order
One key element to make the operation successful is to have the Evaluation Order setup correctly to make your Comment member work as expected:
The order of the dimensions in the evaluation order settings will determine which dimension Data Typetakes precedence in a data form or a report. For example, if you have the Account dimension before the Entity dimension, then the Account member Data Type in a form is used and not the Entity one, move it the other way and the Entity Data Type is used.
In our case, we want Period on top of the others.
Refresh the Database
You created a new member and (potentially) changed the dimension evaluation order setting, so you need to refresh your database for your changes to be taken into account.
Add the “Comments” member to your form
That is pretty simple now, just add the “Comment” member to your POV:
Try it for yourself!
You have a form with a nice Comments box where you can enter your details in plain English.
Make sure you open a Planning connection with SmartView when you query your data:
Where it gets interesting
If you use an Essbase connection to retrieve data, you will get the following:
No text comments but numeric values, why? Because Essbase stores a reference a column in the Planning database where the text comment actually sits.
That means that if you Submit 12 to Comments in the second row with an Essbase connection, you will have the same comment as in the first row. If you Submit 12 with a Planning connection, it will create a new reference and the actual text comment is 12.
That also means that if you have a calculation script that performs DATACOPY “BUDGET” TO “FORECAST” will copy comments with data and it can make you administrator life very easy once the system is live.
Reporting Planning Text data with Financial Reports
With this method, it is very easy to report on text data with Financial Reports. The key is to make sure you are using a Planning connection (and not an Essbase connection). By the way, starting with 220.127.116.11.500, Planning connections often are faster than Essbase connections with Financial Reporting; give it a try if you have a slow report.
Then create your Report:
It will look very good and it is really easy to setup:
I have been implementing this feature for the past 2 years and users simply love it, it is a great addition to any Hyperion Planning application.