11.1.2 Planning Data form changes continued…… 10

I thought of implementing some validation logic to my Data form and also test the new functionality.

1st rule was to tell the user that Negative values are not permitted for FY11 Sales Budget.

So I defined a rule for Column B (Sales -. Budget)

You can define rules for the entire Grid, Columns, Rows, Cells

Add a if condition saying if Column B (in the pic it is Column C….I was doing something interesting so from now read Column C as B :)) < 0…..and now add a THEN condition.

THEN condition illustration below.

You can add a Validation message to the users and can have a Back ground color also….interesting isn’t it?

When I found that I can add a background color…..I thought of adding more colors…..colors can convey a lot more than numbers….

I defined a traffic lighting in Planning DataForm…..So that the users can budget accordingly looking at the Actual Sales.

So if the Sales was <= 10000 then cell color will show RED, which means Actual Sales was less for that region

If Sales was between 10000 and 50000 then YELLOW – Sales is doing fine.

If above 50000 then GREEN – Sale is great.

For Traffic Lighting don’t add anything as Validation Message and don’t check the option of displaying the validation message.

I tired to accommodate it in a single rule….but then the coloring convention was not working…..the last color was applied to all the conditions!!!!!

Adding Formula Rows in Planning Data Form

I feel like the DataForm design has changed so much that I feel that I’m creating a report not a data form.

So I played around a little with the Formula rows….I thought why not Rank the Actual Sales.

You can see which STATE did the best selling.

So I added a Column B (and applied a formula there) Rank([A],descending) (the column representation resembles Financial Reporting formulas)

So we are all set to test the new DataForm…..and here we go.

In order to test the validation I entered -1 for Budget.

I got a RED cell and the validation message saying not to enter Negative Values.

However it allowed me to enter -1 (-1 was saved to the Database) which I didn’t like much…..ValidateData.js has options where it’ll not allow the erroneous data to enter into the system.

But hope that this will get changed in the new releases. (Coloring of the cells and Formula rows are very good)

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

10 thoughts on “11.1.2 Planning Data form changes continued……

  • Anonymous

    Hi good tips.. i am new to hyperion planning. I was trying to make my formula column work but am getting error. I am trying to use the percentage of total

    Is it possible to have the forluma column without the validation rules.

    Can you please advise

  • NewGen Hyperion Consultant

    Hi Celvin,
    Nice post. Infact I read all your posts. They r all really helpful. I need your help on Form validation. I am working on I have a webform which contains say 5columns. Column1 is used for Target purpose. So its value is fixed and freezed. Column2 to column5 have to be distributed the values(of column1) in a way that they should always add up to a total equal to column1 or the webform should not save the data. So, if Column1 has value of 100, then Column2(20), column3(30), column4(50), column5(0). So total of column2(20)+col3(30)+col4(50)+col5(0) =col1(100).
    Other wise if the user enters value in a way that col2 to col5 do not add upto col1, form does not save the values and prompt user any message to correctly enter the values.

    Can you please help me prepare any such validation? Also, could the message be popped up saying that total values do not match the Target no. Also, could we provide the row no in message for which the total failed?
    I have some priority delivery in coming week. Can you please help me on this?


  • Nigeldkw

    Any chance that you can create a data validation rule that checks against a smart list attached to an account in a column. I have an Account called PMApprov with SmartList SL_PMApprov attached with two values "Accept" and "Reject". If you could get this to work and please give me either the data validation rule code or screenshots would be great, as it is vexing me.

  • Anonymous

    Rather filling the cell with some color I want to set the color only for the number. Could you please get me some input


    I have an issue with validation rule on one of my web form. In web from we have a row allocation which takes the total of above above members. The issue is if I give series of 10.00% on the web form, the validation rule throw us an error “total allocation rate not matched to 100%” but any other combination excluding 10.00% which gets total as 100 is working fine. Below is the validation rules which we have used.

    if current cell value != 0
    if current cell value!= 1
    then process cell = red

    Can you please guide us what could be the issue and how to overcome for the same.