Hyperion Planning – validatedata.js

Happy New Year!

It has been a while since I have had to use the validatedata.js script to edit data on a Hyperion Planning web form. You would think with the new custom form validation in 11.1.2 you would not have a use for the validatedata.js file.

When using custom formulas in a web form that outputs a percentage value it is not possible to tell the formula to calculate the percentage differently for  year and quarter values.

One might ask why the percentages where not calculated in Essbase instead of on the client side web form. That is a great question it the simple answer is that the percentage formula was derived based on values within the web form. The web form was create to focus on key dimension intersections instead of building the web form to handle all or any dimension intersections.

Since the quarter and year value was summing the percentage it was necessary to overwrite the default calculation using the validatedata.js file. I used the loading form routine to check for the planning web application name and form name. Then I created an array to load as the form was being loaded with the numerator and denominator values. Then when the quarter and year percentage cells were calculated I replaced the value with the calculated percentage value. I also picked up on the row name to ensure that as the form contracted or expanded the percentages would be calculated correctly.

The only drawback to this solution is that it does not work with smartview. If you import the form into excel using smartview the percentages still show as a summation of the percentage values.

I will create sample validatedata.js and upload it so you might also be able to use it in your Hyperion Planning environment.