How to hide a column in HFM web form?

user11980961

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

Sai Kumar Kandunuri

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.

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

Dear KostasN

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

Regards

SK

KostasN.

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.

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

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

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.

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.

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

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!!!