Skip to main content

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.

Comments

  1. $28 August 2020 at 20:45

    Thanks for the valuable piece of information!

    ReplyDelete
  2. $28 August 2020 at 20:57

    This comment has been removed by the author.

    ReplyDelete

Post a Comment

Popular posts from this blog

Password encryption in MAXL script

Password encryption in MAXL script An objective of this blog is to provide information about how to encrypt the Username and Password in Maxl script. To login into Essbase Server, in traditional method we manually enter the User credentials and do the process. We also have another process to login into Essbase server and do the process i.e. through Maxl scripts in which we can automate Maxl scripts through batch by scheduling and also through command prompt we can run the process by calling the Maxl. Through manually login User credentials are remembered by the user whereas through Maxl script we give the User credentials in text file. So, there might be a chance of stealing the User credentials. To provide the more security we do User credential encryption. There will be two basic keys are used Encrypt: Public key Decrypt: Private key The Public and Private Key pair comprises of two uniquely related Cryptographic keys (It is a string of data used to lock and unlock cry...
Post a Comment
Read more

Block creation for data storage members

We all have the confusion that, how block creation happens in Essbase. Even I used to have that confusion. So, I wanted to clear that confusion by clearly explaining how block creation happens using data storage members. In Block storage, I am going to tell about, 1.      Block size. 2.      The potential number of blocks. 3.      The number of existing blocks. 1.    Block size: The size of each cell is 8kb. So the size of a block will be 8* no. of. Cells (Dense * Dense) bytes. Fig: 1.1 In the above picture, if you see we have two dense members i.e. Product and Year. For block size, we need to take members which are stored only. So no of cells= 9*1bytes. So Block size= 8*(9*1) = 72bytes. Below Fig shows the Block size, Fig: 1.2 2.    Potential Number of blocks: Maximum number of blocks that are derived from the product of one sparse member to other sparse members. Cons...
3 comments
Read more