How to check whether a form is read only/hidden and more (Decoding Webform options – FORM_OPT) 6


I wrote about how to Extract Column/Row/POV properties of Webforms 3 years back (feels like a long time ago) and noted that
it is quite a task to decode the grid options (FORM_OPT)

 

Now here is me eating my own words (or friend of mine made me).

 

I was visiting a friend of mine, Jomish (we both go way back) and we started this discussion about FORM_OPT. Yeah that is what some of us do on a weekend 😉

 

I swear that whatever I’m going to write about decoding FORM_OPT is not done by me, I’m just the messanger. The whole work is done by Jomish and Pat Egan.

 

Now let’s go back and look at what FORM_OPT is and which table it is coming from.

 

All the options that you can select in a webform, be it block suppression, using member alias, dimension options, segment options whatever it is – gets stored under FORM_OPT.
Let’s take a look at a simple form
form-layout

 

In order to explain/decide FORM_OPT I’ve to show you the brilliance of two minds.

 

What they (Jom and Pat) did was to create a copy of the form and change a variable (in their case they wanted to see which forms are read only)

 

This is the copy of the exact same form which we saw above with the difference that this form is now read only.

 

read-only

 

Let’s see the FORM_OPTs of both forms.

 

 

Now what they did (and you are going to do) is to find the difference between both numbers. Once they got the difference, they found out what to the power of 2 makes that number.

 

In this case it is (962077008901 – 962076877829) = 131072

 

They took the base as 2 and found out what the exponent is. You can use this calculator to do the math.

 

 

exponent-calculator

 

So we got that 2^17 = 131072. Now the math gets involved 😀

Since all the form_opts are a toggle (0/1), we now need to eliminate anything greater than 17 and anything after 17.

The math that needs to be performed is

(mod(FORM_OPT,power(2,n+1))-mod(f.form_opt,power(2,n)))/power(2,n)

Let’s look at the SQL for generating a list of read-only forms now.

select
o.object_name form_name, f.form_opt
,CASE (mod(f.form_opt,power(2,18))-mod(f.form_opt,power(2,17)))/power(2,17)
when 0 then 'write'
when 1 then 'read-only'
end "Form ReadWrite Property"
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
where o.object_name like 'Actual vs Plan%';

read-only-sql

You can run this for entire Planning application and find out which forms are read only

all-read-only-forms

 

Let’s look “Operating Expenses – Forecast” form,

read-only-test

 

Now let’s find out which forms are hidden in the system.

unhide

FolDifference is 8388608 and exponent is 23

select
o.object_name form_name,
CASE (mod(f.form_opt,power(2,24))-mod(f.form_opt,power(2,23)))/power(2,23)
when 0 then 'Visible'
when 1 then 'Hidden'
end "Form Hidden/Visible"
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
where o.object_name like 'Operating Expenses - Forecast%';

Let’s run the SQL and find out

visible

Now let’s run it for all the forms

all-visible

If you look at the SQL and the forms under Forecast folder (Vision application) you’ll see that the SQL results tie with the screen shot below.
hidden-all

Now that solves the decoding issue of FORM_OPT (now how that big of a number is generated is going to be a different blog post)


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

6 thoughts on “How to check whether a form is read only/hidden and more (Decoding Webform options – FORM_OPT)

  • Anonymous

    Thanks Celvin,

    I need this to test the form if the form is open or not, and then only process the file via FDMEE form name and file name are same.

    You saved at least a day of mine.

    Thanks,
    Shivendra

  • Philip Hulsebosch

    This is very nice information and excellent research work. The next step would be to find out what function has which number (the Exponent number). Then a user interface to check them on an off….
    Maybe something to split up in a larger group as community work.

    P.S. Attention Oracle, this is only possible on-premise !!!

  • Sandy Hira

    Hi Celvin,

    Hope you already know that many of your web pages are losing their formatting e.g. Groovy in PBCS and On-Premises Hyperion Planning – Part I, they are not displayed correctly.

    Many thanks for putting up great stuff though.

    • Celvin Kattookaran Post author

      Sandy, what’s wrong with that one? I tried to fix all formatting issues and I’m not aware of any. If you can please let me know what’s missing in that one, I’ll fix it right away. Thanks