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
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
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
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
Hi Celvin
You won't believe this I was looking for a solution to the same problem today in the morning… for the task list…
This worked like a charm…
Thanks So Much….
Cheers
Ankur
Hi, there is mistake in the first select: excess word "Path" in "o2.object_namePath".
It is not an excess word, that is the alias I think a space was "lost" somehow. Corrected the select
Hi Celvin,
Thanks for this post and it was much helpful.
By any chance, do you have SQL queries to find the access associated to the business rules. I'm trying to query the AAS database tables to get the list of business rules and who got access to it. It would be much helpful if you share any samples related to this request. Please advise.
Thanks,
AP
Hi Guru,
I was looking to identify a WEBFORM is readyonly or not using query? How to get that?
Thanks
Baskar
There is column called FORM_OPT, however it is hard to decode that.
Is there a way to obtain which members are "Write" vs. "Read" access point of view?
Excellent blog herе! Additionally yoᥙr website а ⅼot uр very
fast! What web host are you the use of? Can I аm getting yoᥙr affiliate hyperlink tօ
yoᥙr host? Ⅰ desir my site loaded ᥙp as fast aѕ youurs lol
is there a way to find out dimension members in webforms?
You can generate a report from Planning. I think it is in Reporting options.