SQL queries for finding folder location of Planning Webforms and more… 10

This entry was posted in Planning Show Usage SQL on by (updated 2304 days ago)
You might have spend many hours (during documentation phase of your project) to get the information about Webforms like Webform folder location, Business rules attached to the form, Menus associated with the forms,……

So here is an easier way to do it. (Thanks to my friend Jomish for helping me with the SQL queries)

To find out the folder location of Webforms

Run the below given query to extract the folder information of Webforms in an application

select

o4.object_name||”||o3.object_name||”||o2.object_name Path, o.object_name form_name, ofrm.object_name cube_name
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_object o2 on (o.parent_id=o2.object_id)
left outer join hsp_object o3 on (o2.parent_id=o3.object_id)
left outer join hsp_object o4 on (o3.parent_id=o4.object_id)
left outer join hsp_object ofrm on (f.cube_id=ofrm.object_id)

order by o4.object_name||”||o3.object_name||”||o2.object_name




The example works for up-to 2 sub folders under root, you can add more outer joins if you have more sub folders.

To find out the folder location of Task Lists

Run the below given query to extract the folder information of Task lists in an application

select


o5.object_name||”||o4.object_name||”||o3.object_name||”||o2.object_name
Path, o.object_name “Task List”
from hsp_task t
left outer join hsp_object o on (t.task_id=o.object_id)
left outer join hsp_object o2 on (o.parent_id=o2.object_id)
left outer join hsp_object o3 on (o2.parent_id=o3.object_id)
left outer join hsp_object o4 on (o3.parent_id=o4.object_id)
left outer join hsp_object o5 on (o4.parent_id=o5.object_id)

order by o5.object_name||”||o4.object_name||”||o3.object_name||”||o2.object_name;



The example works for up-to 3 sub folders under root, you can add more outer joins if you have more sub folders.


To find out the calculations associated with Webforms and their properties


Run the below given query to extract the Calculation script association with Webforms.

select

o.object_name form_name, frmcalc.calc_name,  frmcalc.run_on_load,  frmcalc.run_on_save,  
frmcalc.calc_type,  frmcalc.use_mru “Use Members on Form”,  frmcalc.hide_prompt
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_form_calcs frmcalc on
(f.form_id=frmcalc.form_id)

where frmcalc.calc_name is not null
order by o.object_name;

Different values for Calc_Type are

  • 0=graphical HBR; 
  • 1=Normal HBR; 
  • 2=Sequence HBR; 
  • 3=Native Essbase

To find out the menus associated with Webforms


Run the below given query to extract the Menu association with Webforms.

select


o.object_name form_name, menu.object_name
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_form_menus frmmenu on (f.form_id=frmmenu.form_id)
left outer join hsp_object menu on (frmmenu.menu_id =menu.object_id)

where frmmenu.menu_id is not null
order by o.object_name;


HTH

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.

10 thoughts on “SQL queries for finding folder location of Planning Webforms and more…”