Forum Stats

  • 3,854,167 Users
  • 2,264,331 Discussions
  • 7,905,586 Comments

Discussions

How to hide a column in HFM web form?

user11980961
user11980961 Member Posts: 4
edited Jun 26, 2017 12:27AM in Financial Consolidation

Hello All,

I have a requirement of calculating variance % in columns for number of accounts.

C1=A#account1.Y#@Cur.P#@Cur

C2=A#account2.Y#@Cur(-1).P#@Cur

C3=Scalc(((C1-C2)/C2)*100), CustomHeader:Variance %

How can i hide columns C1 and C2 in HFM web form?

My second question is:

Can i have Scalc formula like below?

C3=Scalc((((A#account1.Y#@Cur.P#@Cur)-(A#account2.Y#@Cur(-1).P#@Cur))/(A#account2.Y#@Cur(-1).P#@Cur))*100), CustomHeader:Variance %

But i am getting error with this formula.

I would be grateful if you can help me in this matter.

Hyperion version is 11.1.2.4

Tagged:
KostasN.
«1

Answers

  • Sai Kumar Kandunuri
    Sai Kumar Kandunuri Member Posts: 123 Blue Ribbon
    edited Jun 20, 2017 4:45AM

    Dear user11980961

        1.  I don't think you can hide columns in web forms. Instead you can go for reports to provide the user with Variance reports, wherein, you have greater flexibility.

         2. The error might be because instead of C1, you are using C3 as your column (as it is your first column). Else send the error message which you are facing, which will be more useful for other users to comment on.

    Regards

    SK

  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 5:22AM

    Hi,

    First of all, you can only hide rows on columns based on the values in individual cells, that is if the entire row/col is empty or zero or noaccess etc. You cannot deliberately hide rows or cols by design.

    Second, the SCalc() syntax allowed is based on form co-ordinates, -i.e. you can only use Col(1) or Row(1) or Cell(1,1) references to other cells in SCalc. You cannot refer explicitly to dimension members, which as you mentioned result in error.

    Regards,

    Kostas

  • Sai Kumar Kandunuri
    Sai Kumar Kandunuri Member Posts: 123 Blue Ribbon
    edited Jun 20, 2017 5:38AM

    Dear KostasN

         We can also perform the SCalc by referring to dimension members. I cross checked again, just in case.

    pastedImage_0.png

    Regards

    SK

    KostasN.
  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 5:57AM

    Sorry for this, although I consulted the admin manual, however this escaped my attention. Though, checking again, it does not mention about POV, but rather to single dimension member, I have not tested this, therefore I cannot tell if this difference is of significance.

  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 6:02AM

    Thinking of this again, rather than using a complete POV in SCalc formula, could you use account in the formula and set the rest of the POV in override?

  • Sai Kumar Kandunuri
    Sai Kumar Kandunuri Member Posts: 123 Blue Ribbon
    edited Jun 20, 2017 7:07AM

    Dear KostasN

         I'm trying to create a web form and try to use a POV in SCalc. I'll let you know once i'm done. I too have never tried out this.

    Regards

    SK

  • Sai Kumar Kandunuri
    Sai Kumar Kandunuri Member Posts: 123 Blue Ribbon
    edited Jun 20, 2017 8:28AM

    Dear KostasN

         We can use POV in the SCalc function. But we can't use the "Relative Time Periods", like 'Cur', 'Cur-1', etc. My knowledge is limited, so better you too try different permutations and combinations, then we can have a clear idea of it.

    Regards

    SK

    KostasN.
  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 9:49AM

    Hi SK,

    One quick thought about your findings, is that relative time periods (particularly true when Y#Prior is used, though P#Prior can also get you there when current month is January) may involve retrieving combined data from different subcubes, meaning that in order to perform a single column calculation, HFM has to retrieve data from more than one subcubes. However, it is pretty common to reference two or more subcubes at the same time, when you use different columns to reference to different years.

    Nice finding, I may also have hands on a system either tomorrow or the day after to also check. What I may also check is another case of multi-subcube reference, which is by combining different entities or scenarios (or even value members???) in the calculation.

    Regards,

    Kostas

  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 10:31AM

    Hi again,

    It's good to explore new things, though a possible solution to the initial problem, which seems to be the need to provide with a variance calculation on a single column of the form (and avoid displaying intermediate calculations on separate columns), is to do the calculation in a separate account using calculation rules and display that account on the form. This solution can be applied relatively easy, provided that you have admin rights to the application and can load metadata and rules. The variance account can then be also used οn reports, it will definitely be stored in the database, ready to retrieve at will.

    Regards,

    Kostas

  • user11980961
    user11980961 Member Posts: 4
    edited Jun 21, 2017 6:53AM

    Hi Kostas,

    Thank you so much for your help!!

    Requirement is to have accounts in columns and entities in rows. There will be variance % and comment (cell text) columns for each account. There will around 40 to 100 accounts in each web form.

    Users will utilize these web forms to analyze comments (cell text) entered by Entity owner.

    Either i have to create a lot of accounts for storing variance or I have to utilize some other dimension member for storing variance.

    FR report is taking a lot of time to retrieve data by entity for Year / Period comparison. Web form retrieve is quick but i am facing these customization challenges..

    Thank you!!!

This discussion has been closed.