Adding formulas to the Dataforms - Hyperion Planning

We all know, how to create a data form using rows, columns, page and POV. In this I want to explain how to add Formula rows/columns in Hyperion Web form. Here in this blog I have used application called VIZAGPLN. All the screenshots show this application only.
Step-1: Go to Planning Application > Navigate > Applications > Planning > VIGAZPLN as shown below.

Step-2: Go to Administration > Manage > Forms and Ad Hoc Grids to open the forms shown as below.

Step-3: Select the form and click on edit, shown as below.

Step-4: Go to Layout and click on edit select member selector icon to insert require Member/members in rows and columns as shown below.

I will explore further how to add formula row/column with below three requirements for better understanding.

           Req1) Displaying Half year sales using Sum function:

                      Sum function returns the sum of rows, columns or cell.

                      Example: Sum (Column[A], Column[B])

Step-5: Select the members of IDescendants(“Total Expenses”) from Account dimension in the row selection shown as below.

Step-6: In the same way add Q1 and Q2 from Period dimension in the column selection shown as below.

Step-7: Now right click on cell value of row or column to get options shown as below and select option “Add Formula Column”.

Step-8: Another column for Formula will be added and name that Formula cell and click on the cell value of formula to get open the “Segment Properties” automatically, if not select manually which will be located at the right side of the layout page shown as below. Then edit the formula tab which was circled in black as below.

Step-9: Write the formula and validate it shown as below and save the layout.

Step-10: Now open the “EXP” form to see the output as per the given formula as below.

Here we got the required output in “Half year sales” which is the addition of Q1 and Q2.by using Formula

Req 2) Listing top 3 expenses by using Rank function:

            Syntax of Rank: Rank([Reference], Order)

Step-11: Add the Formula column or edit the existing Formula cell shown as below.

Step-12: Now edit the formula and write the formula for Rank shown as below and validate the formula.

Step-13: Save the Layout and open “EXP” form to see the output for the given output shown as below.

We have got the ranks based on the Expenses of Q1. Now we want the top 3 expenses to be displayed on the top.

Step-14: To get the expenses based on the ranking, right click on the Formula cell i.e. “Top 3 expenses” to get the option shown as below.

Right click > Sort > Sort Ascending/Sort Descending.

Step-15: Now Expenses got listed out based on the Rank shown as below.

     

           Req 3) Calculating Relocation Claim using IF condition and IsMissing function:

                      Syntax: IF and IsMissing: IfThen(Condition, TrueParameter, FalseParameter).

                      If the condition given is true then TrueParameter as to be displayed

                      If the condition is false then FalseParameter as to be displayed.

Step-17: Apply the formulas as “IfThen(IsMissing(Column[B]),[C],[B])” shown as below and validate it.

Step-18: Open the “EXP” form to see the output for formula given, shown as below.

Jan is in column B and Feb is in column C.

Row 1: Condition satisfied with Column B (Jan) so the resultant formula column shown as Column B(Jan) Value as "50000".

Row 2: Condition not satisfied with Column B (Jan) so the resultant formula column shown as Column C(Feb) Value "9000".

For any requirement we can have additional formula Rows/Column in any of the forms in Hyperion. This is only for specific requirements.