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)
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
If you can send me the formula that you are using, i can try to correct.
Yes you can have formula column/row without any validation rules….
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 11.1.1.3. 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?
Regards,
Hi…..
You'll have to create a java script for doing this….Check for a file called SampleValidateData.js. That has some examples in it….
Hi Celvin,
I have already seen that. But I am not good with Java scripts. Is there anything you can help me with? May be a sample code from you would do great at this time.
Regards,
For to create a sample will take time….
in the 11.1.2.1 you can now add the rules (coloring rules) to a single rule.
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.
Hi,
Rather filling the cell with some color I want to set the color only for the number. Could you please get me some input
Regards,
Nowshad.
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
and
(
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.
Regards,
Rishi